Entity framework left join

Question

How do I change this query so it returns all u usergroups   from u in usergroups from p in u UsergroupPrices select new UsergroupPricesList   UsergroupID   u UsergroupID  UsergroupName   u UsergroupName  Price   p Price

User · Answer

If you prefer method call notation  you can force a left join using SelectMany combined with DefaultIfEmpty  At least on Entity Framework 6 hitting SQL Server  For example   using var ctx   new MyDatabaseContext          var data   ctx      MyTable1      SelectMany a   gt  ctx MyTable2        Where b   gt  b Id2    a Id1         DefaultIfEmpty          Select b   gt  new                 a Id1          a Col1          Col2   b    null    int   null   b Col2                  Note that MyTable2 Col2 is a column of type int    The generated SQL will look like this   SELECT       Extent1   Id1  AS  Id1         Extent1   Col1  AS  Col1        CASE WHEN   Extent2   Col2  IS NULL  THEN CAST NULL AS int  ELSE  CAST   Extent2   Col2  AS int  END AS  Col2      FROM   dbo   MyTable1  AS  Extent1      LEFT OUTER JOIN  dbo   MyTable2  AS  Extent2  ON  Extent2   Id2     Extent1   Id1

User · Answer

I was able to do this by calling the DefaultIfEmpty   on the main model  This allowed me to left join on lazy loaded entities  seems more readable to me           var complaints   db Complaints DefaultIfEmpty                Where x   gt  x DateStage1Complete    null    x DateStage2Complete    null               OrderBy x   gt  x DateEntered               Select x   gt  new                               ComplaintID   x ComplaintID                  CustomerName   x Customer Name                  CustomerAddress   x Customer Address                  MemberName   x Member    null   x Member Name  string Empty                  AllocationName   x Allocation    null   x Allocation Name  string Empty                  CategoryName   x Category    null   x Category Ssl Name   string Empty                  Stage1Start   x Stage1StartDate                  Stage1Expiry   x Stage1 ExpiryDate                  Stage2Start   x Stage2StartDate                  Stage2Expiry   x Stage2 ExpiryDate

User · Answer

For 2 and more left joins  left joining creatorUser and initiatorUser    IQueryable lt CreateRequestModel gt  queryResult   from r in authContext Requests                                              join candidateUser in authContext AuthUsers                                              on r CandidateId equals candidateUser Id                                              join creatorUser in authContext AuthUsers                                              on r CreatorId equals creatorUser Id into gj                                              from x in gj DefaultIfEmpty                                                join initiatorUser in authContext AuthUsers                                              on r InitiatorId equals initiatorUser Id into init                                              from x1 in init DefaultIfEmpty                                                 where candidateUser UserName Equals candidateUsername                                               select new CreateRequestModel                                                                                                 UserName   candidateUser UserName                                                   CreatorId    x    null   String Empty   x UserName                                                    InitiatorId    x1    null   String Empty   x1 UserName                                                    CandidateId   candidateUser UserName

User · Answer

It might be a bit of an overkill  but I wrote an extension method  so you can do a LeftJoin using the Join syntax  at least in method call notation    persons LeftJoin      phoneNumbers      person   gt  person Id      phoneNumber   gt  phoneNumber PersonId       person  phoneNumber    gt  new                       Person   person              PhoneNumber   phoneNumber  Number                My code does nothing more than adding a GroupJoin and a SelectMany call to the current expression tree  Nevertheless  it looks pretty complicated because I have to build the expressions myself and modify the expression tree specified by the user in the resultSelector parameter to keep the whole tree translatable by LINQ-to-Entities   public static class LeftJoinExtension       public static IQueryable lt TResult gt  LeftJoin lt TOuter  TInner  TKey  TResult gt           this IQueryable lt TOuter gt  outer          IQueryable lt TInner gt  inner          Expression lt Func lt TOuter  TKey gt  gt  outerKeySelector          Expression lt Func lt TInner  TKey gt  gt  innerKeySelector          Expression lt Func lt TOuter  TInner  TResult gt  gt  resultSelector                MethodInfo groupJoin   typeof  Queryable  GetMethods                                                     Single m   gt  m ToString       System Linq IQueryable 1 TResult  GroupJoin TOuter TInner TKey TResult  System Linq IQueryable 1 TOuter   System Collections Generic IEnumerable 1 TInner   System Linq Expressions Expression 1 System Func 2 TOuter TKey    System Linq Expressions Expression 1 System Func 2 TInner TKey    System Linq Expressions Expression 1 System Func 3 TOuter System Collections Generic IEnumerable 1 TInner  TResult                                                        MakeGenericMethod typeof  TOuter   typeof  TInner   typeof  TKey   typeof  LeftJoinIntermediate lt TOuter  TInner gt             MethodInfo selectMany   typeof  Queryable  GetMethods                                                      Single m   gt  m ToString       System Linq IQueryable 1 TResult  SelectMany TSource TCollection TResult  System Linq IQueryable 1 TSource   System Linq Expressions Expression 1 System Func 2 TSource System Collections Generic IEnumerable 1 TCollection     System Linq Expressions Expression 1 System Func 3 TSource TCollection TResult                                                         MakeGenericMethod typeof  LeftJoinIntermediate lt TOuter  TInner gt    typeof  TInner   typeof  TResult             var groupJoinResultSelector    Expression lt Func lt TOuter  IEnumerable lt TInner gt   LeftJoinIntermediate lt TOuter  TInner gt  gt  gt                                           oneOuter  manyInners    gt  new LeftJoinIntermediate lt TOuter  TInner gt   OneOuter   oneOuter  ManyInners   manyInners             MethodCallExpression exprGroupJoin   Expression Call groupJoin  outer Expression  inner Expression  outerKeySelector  innerKeySelector  groupJoinResultSelector            var selectManyCollectionSelector    Expression lt Func lt LeftJoinIntermediate lt TOuter  TInner gt   IEnumerable lt TInner gt  gt  gt                                               t   gt  t ManyInners DefaultIfEmpty              ParameterExpression paramUser   resultSelector Parameters First             ParameterExpression paramNew   Expression Parameter typeof  LeftJoinIntermediate lt TOuter  TInner gt     t            MemberExpression propExpr   Expression Property paramNew   OneOuter             LambdaExpression selectManyResultSelector   Expression Lambda new Replacer paramUser  propExpr  Visit resultSelector Body   paramNew  resultSelector Parameters Skip 1  First              MethodCallExpression exprSelectMany   Expression Call selectMany  exprGroupJoin  selectManyCollectionSelector  selectManyResultSelector            return outer Provider CreateQuery lt TResult gt  exprSelectMany              private class LeftJoinIntermediate lt TOuter  TInner gt                public TOuter OneOuter   get  set            public IEnumerable lt TInner gt  ManyInners   get  set               private class Replacer   ExpressionVisitor               private readonly ParameterExpression  oldParam          private readonly Expression  replacement           public Replacer ParameterExpression oldParam  Expression replacement                         oldParam   oldParam               replacement   replacement                     public override Expression Visit Expression exp                        if  exp     oldParam                                return  replacement                             return base Visit exp

User · Answer

Please make your life easier  don t use join into group    var query   from ug in UserGroups             from ugp in UserGroupPrices Where x   gt  x UserGroupId    ug Id  DefaultIfEmpty               select new                                 UserGroupID   ug UserGroupID                  UserGroupName   ug UserGroupName                  Price   ugp    null   ugp Price   0   this is to handle nulls as even when Price is non-nullable prop it may come as null from SQL  result of Left Outer Join

User · Answer

adapted from MSDN  how to left join using EF 4  var query   from u in usergroups             join p in UsergroupPrices on u UsergroupID equals p UsergroupID into gj             from x in gj DefaultIfEmpty               select new                    UsergroupID   u UsergroupID                  UsergroupName   u UsergroupName                  Price    x    null   String Empty   x Price

User · Answer

If UserGroups has a one to many relationship with UserGroupPrices table  then in EF  once the relationship is defined in code like     In UserGroups Model public List lt UserGroupPrices gt  UserGrpPriceList  get set      In UserGroupPrices model public UserGroups UserGrps  get set     You can pull the left joined result set by simply this   var list   db UserGroupDbSet ToList      assuming your DbSet for the left table is UserGroupDbSet  which will include the UserGrpPriceList  which is a list of all associated records from the right table

[c#] Entity framework left join

Examples related to c#

Examples related to linq

Examples related to entity-framework

Examples related to linq-to-entities

Examples related to left-join