Entity-framework – Left Join Entity Framework

c#-4.0entity-frameworklinq

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'

Best Solution

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() 
...