Left Join Entity Framework

c#-4.0 entity-framework linq

Question

I am going to trash Entity Framework 5! I have spent two days to find solution from web but found nothing!

I tried a simple left join from table Artworks and EndValidities with the input IDFondazione as 2620

var query = from art in db.Artworks
  join endVal in db.EndValidities.DefaultIfEmpty() 
  on art.ID_Dtsl equals endVal.IDdtsl 
  where art.ID_Cdgs == IDFondazione
  select new SchedaOpera()
  {
    Cdscheda = art.Cdscheda,
    Confirmed = art.FGconfermata,
    DSimmagine = art.DSimmgine,
    Dtsf = art.Dtsf,
    Dtsi = art.Dtsi,
    Dtsl = endVal != null ? endVal.Dtsl : "",
    Iscr = art.Iscr,
    Misa = art.Misa,
    Ldcn = art.Ldcn,
    Ldcs = art.Ldcs,
    Misd = art.Misd,
    Misl = art.Misl,
    Misn = art.Misn,
    Misp = art.Misp,
    Miss = art.Miss,
    Pvcc = art.Pvcc,
    Pvcp = art.Pvcp,
    Sgtt = art.Sgtt
  };

The marvellous entity framework continues to produce

SELECT [Extent1].[ID_Dtsl] AS [ID_Dtsl], [Extent1].[CDscheda] AS [CDscheda], 
[Extent1].[FGconfermata] AS [FGconfermata], 
[Extent1].[DSimmagine] AS [DSimmagine], 
[Extent1].[DS_DTSF] AS [DS_DTSF], 
[Extent1].[DS_DTSI] AS [DS_DTSI], 
CASE WHEN (cast(1 as bit) <> cast(0 as bit)) THEN [Extent2].[DSdtsl] ELSE N'_' END AS [C1], 
[Extent1].[DS_ISCR] AS [DS_ISCR], 
[Extent1].[NR_MISA] AS [NR_MISA], 
[Extent1].[DS_LDCN] AS [DS_LDCN], 
[Extent1].[DS_LDCS] AS [DS_LDCS], 
[Extent1].[NR_MISD] AS [NR_MISD], 
[Extent1].[NR_MISL] AS [NR_MISL], 
[Extent1].[NR_MISN] AS [NR_MISN], 
[Extent1].[NR_MISP] AS [NR_MISP], 
[Extent1].[NR_MISS] AS [NR_MISS], 
[Extent1].[FGpubblicata] AS [FGpubblicata], 
[Extent1].[DS_PVCC] AS [DS_PVCC], 
[Extent1].[DS_PVCP] AS [DS_PVCP], 
[Extent1].[DS_SGTT] AS [DS_SGTT]
FROM  [dbo].[TPD_OPERE] AS [Extent1]
INNER JOIN [dbo].[TSA_DTSL] AS [Extent2] ON [Extent1].[ID_Dtsl] = [Extent2].[IDdtsl]
WHERE [Extent1].[CD_CDGS] = '2620'
1
1
9/19/2012 1:49:49 PM

Accepted Answer

DefaultIfEmpty in Entity Framework only works on navigation properties. Otherwise you should use GroupJoin, which in comprehensive syntax is achieved by join ... into. See this excellent post for more details.

So your query could be (GroupJoin):

from art in db.Artworks
join endVal in db.EndValidities on art.ID_Dtsl equals endVal.IDdtsl
into g // can be any name
where art.ID_Cdgs == IDFondazione
...

The difference is the statement selects art objects and a collection of endVal objects per art object. So the Dtsl property should be a Select on endVal.Dtsl, producing a collection of Dtsl values. The collection can be empty (zero elements), which represents the outer join.

Or, if there is navigation property Artwork.EndValidities:

from art in db.Artworks
from endVal in art.EndValidities.DefaultIfEmpty() 
...
2
9/19/2012 10:29:06 PM

Popular Answer

My Demo, tested in linqpad, you can left outer join two tables as below:

var resultAcctInfo = (
                from p in Focus_Person
                where String.Compare(p.Login, "yourname", true) == 0 && (p.AcctStatusID == 1 || p.AcctStatusID == 2)
                join an in Focus_SecurityAnswer on p.PersonID equals an.PersonID into G
                //from g in G //You can uncommented this line to check the difference.
                join q in Focus_SecurityQuestion on G.FirstOrDefault().QuestionID equals q.QuestionID into Q  //left join question table
                //from q in Q                   
                select new
                {
                    Person = p,
                    Answer = G.FirstOrDefault(),                        
                    //Question = q,
                });
resultAcctInfo.Dump();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow