CODEWITHSUNDEEP
linqentity-frameworkc#-4.0
velteyn
velteyn

Reputation: 313

Left Join Entity Framework

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'

Upvotes: 1

Views: 6343

Answers (4)

Prateek Gupta
Prateek Gupta

Reputation: 908

I have this working code:-

  var res = from et in db.entity_test join en in db.entity_new on et.id equals en.id into ps from en in ps.DefaultIfEmpty() select new {Name = et.name,Email=en.email };

    GridView1.DataSource = res;
    GridView1.DataBind();

Upvotes: 0

Zhe
Zhe

Reputation: 29

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

Upvotes: 1

Gert Arnold
Gert Arnold

Reputation: 109079

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

Upvotes: 2

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

from art in db.Artworks
join endValidities in db.EndValidities
on art.ID_Dtsl equals endValidities .IDdtsl into ev
where art.ID_Cdgs == IDFondazione

from endVal in ev.DefaultIfEmpty()
select new SchedaOpera()
     {
          Cdscheda = art.Cdscheda,
          Confirmed = art.FGconfermata,
          DSimmagine = art.DSimmgine,
          Dtsf = art.Dtsf,
          Dtsi = art.Dtsi,
          Dtsl = endVal.Dtsl ?? string.Empty,
          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
     };

Upvotes: 0

Related Questions