LEFT JOIN in LINQ to entities

Question

I m trying out LINQ to entities   I have a problem with the following  I want it to do this   SELECT       T Benutzer BE User      T Benutzer Benutzergruppen BEBG BE FROM T Benutzer  LEFT JOIN T Benutzer Benutzergruppen     ON T Benutzer Benutzergruppen BEBG BE   T Benutzer BE ID    the closest thing I ve come to is this           var lol                 from u in Repo T Benutzer                where u BE ID    1             from o in Repo T Benutzer Benutzergruppen DefaultIfEmpty                      on u BE ID equals o BEBG BE              where  u BE ID    o BEBG BE    o BEBG BE    null                 join bg in Repo T Benutzergruppen DefaultIfEmpty                     on o BEBG BG equals bg ID                where bg ID    899               orderby                 u BE Name ascending                     bg Name descending                select u              select new                                u BE User                  o BEBG BG                     bg Name                           ToList      But this generates the same results as an inner join  and not a left join  Moreover  it creates this completely  crazy SQL   SELECT        Extent1   BE ID  AS  BE ID        Extent1   BE User  AS  BE User        Join1   BEBG BG  AS  BEBG BG  FROM   dbo   T Benutzer  AS  Extent1   CROSS JOIN         SELECT            Extent2   BEBG BE  AS  BEBG BE            Extent2   BEBG BG  AS  BEBG BG      FROM   SELECT 1 AS X   AS  SingleRowTable1      LEFT OUTER JOIN  dbo   T Benutzer Benutzergruppen  AS  Extent2           ON 1   1    AS  Join1   WHERE  Extent1   BE ID     Join1   BEBG BE   OR  Join1   BEBG BE  IS NULL  ORDER BY  Extent1   BE Name  ASC   How can I do a left join in LINQ-2-entities in a way where another person can still understand what s being done in that code    and most-preferably where the generated SQL looks like   SELECT       T Benutzer BE User      T Benutzer Benutzergruppen BEBG BE FROM T Benutzer  LEFT JOIN T Benutzer Benutzergruppen     ON T Benutzer Benutzergruppen BEBG BE   T Benutzer BE ID

User · Answer

May be I come later to answer but right now I'm facing with this... if helps there are one more solution (the way i solved it).

    var query2 = (
    from users in Repo.T_Benutzer
    join mappings in Repo.T_Benutzer_Benutzergruppen on mappings.BEBG_BE equals users.BE_ID into tmpMapp
    join groups in Repo.T_Benutzergruppen on groups.ID equals mappings.BEBG_BG into tmpGroups
    from mappings in tmpMapp.DefaultIfEmpty()
    from groups in tmpGroups.DefaultIfEmpty()
    select new
    {
         UserId = users.BE_ID
        ,UserName = users.BE_User
        ,UserGroupId = mappings.BEBG_BG
        ,GroupName = groups.Name
    }

);

By the way, I tried using the Stefan Steiger code which also helps but it was slower as hell.

User · Answer

Ah  got it myselfs   The quirks and quarks of LINQ-2-entities  This looks most understandable   var query2         from users in Repo T Benutzer     from mappings in Repo T Benutzer Benutzergruppen          Where mapping   gt  mapping BEBG BE    users BE ID  DefaultIfEmpty       from groups in Repo T Benutzergruppen          Where gruppe   gt  gruppe ID    mappings BEBG BG  DefaultIfEmpty         where users BE Name Contains keyword              mappings BEBG BE Equals 666             mappings BEBG BE    666           groups Name Contains keyword       select new                UserId   users BE ID          UserName   users BE User          UserGroupId   mappings BEBG BG          GroupName   groups Name             var xy    query2  ToList      Remove the  DefaultIfEmpty    and you get an inner join  That was what I was looking for

User · Answer

Easy way is to use Let keyword  This works for me   from AItem in Db A Let BItem   Db B Where x   gt  x id    AItem id   FirstOrDefault    Where SomeCondition Select new YourViewModel       X1   AItem a      X2   AItem b      X3   BItem c     This is a simulation of Left Join  If each item in B table not match to A item   BItem return null

User · Answer

You can use this not only in entities but also store procedure or other data source   var customer    from cus in  billingCommonservice BillingUnit CustomerRepository GetAll                               join man in  billingCommonservice BillingUnit FunctionRepository ManagersCustomerValue                               on cus CustomerID equals man CustomerID                                start left join                             into a                             from b in a DefaultIfEmpty new DJBL uspGetAllManagerCustomer Result                                 select new   cus MobileNo1 b ActiveStatus

User · Answer

You can read an article i have written for joins in LINQ here  var query    from  u in Repo T Benutzer join bg in Repo T Benutzer Benutzergruppen     on u BE ID equals bg BEBG BE into temp from j in temp DefaultIfEmpty   select new       BE User   u BE User      BEBG BG    int  j BEBG BG      null   -1   j BEBG BG                 bg Name      The following is the equivalent using extension methods   var query    Repo T Benutzer  GroupJoin       Repo T Benutzer Benutzergruppen      x  gt x BE ID      x  gt x BEBG BE       o i   gt new  o i     SelectMany       x   gt  x i DefaultIfEmpty         o i    gt  new               BE User   o o BE User          BEBG BG    int  i BEBG BG

[c#] LEFT JOIN in LINQ to entities?

Examples related to c#

Examples related to linq

Examples related to entity-framework

Examples related to entity-framework-4

Examples related to linq-to-entities