[c#] Entity framework left join

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
};

This question is related to c# linq entity-framework linq-to-entities left-join

The answer is


Please make your life easier (don't use join into group):

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

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 => person.Id,
    phoneNumber => phoneNumber.PersonId,
    (person, phoneNumber) => 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<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);
        }
    }
}

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<UserGroupPrices> 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.


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 => 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
            });

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 => 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 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]

For 2 and more left joins (left joining creatorUser and initiatorUser )

IQueryable<CreateRequestModel> 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
                                             };

Examples related to c#

How can I convert this one line of ActionScript to C#? Microsoft Advertising SDK doesn't deliverer ads How to use a global array in C#? How to correctly write async method? C# - insert values from file into two arrays Uploading into folder in FTP? Are these methods thread safe? dotnet ef not found in .NET Core 3 HTTP Error 500.30 - ANCM In-Process Start Failure Best way to "push" into C# array

Examples related to linq

Async await in linq select How to resolve Value cannot be null. Parameter name: source in linq? What does Include() do in LINQ? Selecting multiple columns with linq query and lambda expression System.Collections.Generic.List does not contain a definition for 'Select' lambda expression join multiple tables with select and where clause LINQ select one field from list of DTO objects to array The model backing the 'ApplicationDbContext' context has changed since the database was created Check if two lists are equal Why is this error, 'Sequence contains no elements', happening?

Examples related to entity-framework

Entity Framework Core: A second operation started on this context before a previous operation completed EF Core add-migration Build Failed Entity Framework Core add unique constraint code-first 'No database provider has been configured for this DbContext' on SignInManager.PasswordSignInAsync The instance of entity type cannot be tracked because another instance of this type with the same key is already being tracked Auto-increment on partial primary key with Entity Framework Core Working with SQL views in Entity Framework Core How can I make my string property nullable? Lazy Loading vs Eager Loading How to add/update child entities when updating a parent entity in EF

Examples related to linq-to-entities

How to add a default "Select" option to this ASP.NET DropDownList control? LEFT JOIN in LINQ to entities? How to get first record in each group using Linq Linq to Entities join vs groupjoin The specified type member 'Date' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties Linq where clause compare only date value without time value The specified type member is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported SQL to Entity Framework Count Group-By LINQ to Entities does not recognize the method The cast to value type 'Int32' failed because the materialized value is null

Examples related to left-join

MySQL select rows where left join is null How to return rows from left table not found in right table? How to specify names of columns for x and y when joining in dplyr? Select rows which are not present in other table SQL LEFT-JOIN on 2 fields for MySQL Combine two pandas Data Frames (join on a common column) SQL LEFT JOIN Subquery Alias How to use mysql JOIN without ON condition? LEFT JOIN only first row MySQL - sum column value(s) based on row from the same table