Entity framework left join

Question

How do I modify this query to return all u.usergroups?

from u in usergroups from p in u.UsergroupPrices select new UsergroupPricesList { UsergroupID = u.UsergroupID, UsergroupName = u.UsergroupName, Price = p.Price }; 

Answer 1

adapted from MSDN, how to leave a connection 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) }; 

Answer 2

This may be a little redundant, but I wrote an extension method, so you can make LeftJoin using Join syntax (at least in the method call notation):

 persons.LeftJoin( phoneNumbers, person => person.Id, phoneNumber => phoneNumber.PersonId, (person, phoneNumber) => new { Person = person, PhoneNumber = phoneNumber?.Number } ); 

My code does nothing but add GroupJoin and call SelectMany on the current expression tree. However, it looks rather complicated, because I have to build the expressions myself and change the expression tree specified by the user in the resultSelector parameter resultSelector that the whole tree can be translated using LINQ-to-Entities.

 public static class LeftJoinExtension { public static IQueryable<TResult> LeftJoin<TOuter, TInner, TKey, TResult>( this IQueryable<TOuter> outer, IQueryable<TInner> inner, Expression<Func<TOuter, TKey>> outerKeySelector, Expression<Func<TInner, TKey>> innerKeySelector, Expression<Func<TOuter, TInner, TResult>> resultSelector) { MethodInfo groupJoin = typeof (Queryable).GetMethods() .Single(m => 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<TOuter, TInner>)); MethodInfo selectMany = typeof (Queryable).GetMethods() .Single(m => 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<TOuter, TInner>), typeof (TInner), typeof (TResult)); var groupJoinResultSelector = (Expression<Func<TOuter, IEnumerable<TInner>, LeftJoinIntermediate<TOuter, TInner>>>) ((oneOuter, manyInners) => new LeftJoinIntermediate<TOuter, TInner> {OneOuter = oneOuter, ManyInners = manyInners}); MethodCallExpression exprGroupJoin = Expression.Call(groupJoin, outer.Expression, inner.Expression, outerKeySelector, innerKeySelector, groupJoinResultSelector); var selectManyCollectionSelector = (Expression<Func<LeftJoinIntermediate<TOuter, TInner>, IEnumerable<TInner>>>) (t => t.ManyInners.DefaultIfEmpty()); ParameterExpression paramUser = resultSelector.Parameters.First(); ParameterExpression paramNew = Expression.Parameter(typeof (LeftJoinIntermediate<TOuter, TInner>), "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<TResult>(exprSelectMany); } private class LeftJoinIntermediate<TOuter, TInner> { public TOuter OneOuter { get; set; } public IEnumerable<TInner> 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); } } } 

Answer 3

Please make your life easier (do not use grouping):

 var query = from ug in UserGroups from ugp in UserGroupPrices.Where(x => 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) }; 

Answer 4

If UserGroups is one to many related to the UserGroupPrices table, then in EF, as soon as the relationship is defined in the code, for example:

 //In UserGroups Model public List<UserGroupPrices> UserGrpPriceList {get;set;} //In UserGroupPrices model public UserGroups UserGrps {get;set;} 

You can get the combined result set on the left just like this:

 var list = db.UserGroupDbSet.ToList(); 

it is assumed that your DbSet for the left table is UserGroupDbSet, which will include UserGrpPriceList, which is a list of all related records from the right table.

Answer 5

I was able to do this by calling DefaultIfEmpty () in the main model. This allowed me to join the lazy loaded objects, it seems to me more readable:

  var complaints = db.Complaints.DefaultIfEmpty() .Where(x => x.DateStage1Complete == null || x.DateStage2Complete == null) .OrderBy(x => x.DateEntered) .Select(x => 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 }); 

Answer 6

You can force the left join using SelectMany combined with DefaultIfEmpty . At least Entity Framework 6 uses SQL Server. For example:

 using(var ctx = new MyDatabaseContext()) { var data = ctx .MyTable1 .SelectMany(a => ctx.MyTable2 .Where(b => b.Id2 == a.Id1) .DefaultIfEmpty() .Select(b => new { a.Id1, a.Col1, Col2 = b == null ? (int?) null : b.Col2, })); } 

(Note that MyTable2.Col2 is an int column). 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]