Entity Framework Left Join

Entity framework left join

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

Entity Framework Left Join The error I got while using

A left outer join is a join in which each element of the first collection is returned, regardless of whether it has any correlated elements in the second collection. You can use LINQ to perform a left outer join by calling the DefaultIfEmpty method on the results of a group join.

There is no such syntax exactly the same as T-SQL to use Left or Right joins. However, there is a similar approach that you can leverage.

Assuming that you have two Entities. SalesOrderDetail and Product:

public partial class SalesOrderDetail
{
    public int SalesOrderID { get; set; }
    public short OrderQty { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public decimal LineTotal { get; set; }
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);

        sb.AppendLine($"Order ID: {SalesOrderID}");
        sb.Append($"   Product ID: {ProductID}");
        sb.AppendLine($"   Qty: {OrderQty}");
        sb.Append($"   Unit Price: {UnitPrice:c}");
        sb.AppendLine($"   Total: {LineTotal:c}");

        return sb.ToString();
    }
}

And Product as per below:

public partial class Product
{
    public int ProductID { get; set; }
    public string Name { get; set; }
    public string Color { get; set; }
    public decimal StandardCost { get; set; }
    public decimal ListPrice { get; set; }
    public string Size { get; set; }

    // Calculated Properties
    public int? NameLength { get; set; }
    public decimal? TotalSales { get; set; }
    public override string ToString()
    {
        StringBuilder sb = new StringBuilder(1024);

        sb.Append(Name);
        sb.AppendLine($"  ID: {ProductID}");
        sb.Append($"   Color: {Color}");
        sb.AppendLine($"   Size: {(Size ?? "n/a")}");
        sb.Append($"   Cost: {StandardCost:c}");
        sb.Append($"   Price: {ListPrice:c}");
        if (NameLength.HasValue)
        {
            sb.AppendLine($"   Name Length: {NameLength}");
        }
        if (TotalSales.HasValue)
        {
            sb.AppendLine($"   Total Sales: {TotalSales:c}");
        }
        return sb.ToString();
    }
}

Now Perform a left join between Products and Sales using DefaultIfEmpty() and SelectMany() as per below:

var query = (from prod in Products
         join sale in Sales
         on prod.ProductID equals sale.ProductID
           into sales
         from sale in sales.DefaultIfEmpty()
         select new
         {
             prod.ProductID,
             prod.Name,
             prod.Color,
             prod.StandardCost,
             prod.ListPrice,
             prod.Size,
             sale?.SalesOrderID,
             sale?.OrderQty,
             sale?.UnitPrice,
             sale?.LineTotal
         }).OrderBy(ps => ps.Name);

If you wish to use method syntax, you can achieve the same result as per below code:

var query = Products.SelectMany(
          sale =>
          Sales.Where(s => sale.ProductID == s.ProductID).DefaultIfEmpty(),
          (prod, sale) => new
          {
              prod.ProductID,
              prod.Name,
              prod.Color,
              prod.StandardCost,
              prod.ListPrice,
              prod.Size,
              sale?.SalesOrderID,
              sale?.OrderQty,
              sale?.UnitPrice,
              sale?.LineTotal
          }).OrderBy(ps => ps.Name);

Now you can use a simple foreach loop as per below:

foreach (var item in query)
{
    count++;
    sb.AppendLine($"Product Name: {item.Name} ({item.ProductID})");
    sb.AppendLine($"  Order ID: {item.SalesOrderID}");
    sb.AppendLine($"  Size: {item.Size}");
    sb.AppendLine($"  Order Qty: {item.OrderQty}");
    sb.AppendLine($"  Total: {item.LineTotal:c}");
}

For more information, you can visit https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins

Include() is not working as LEFT JOIN (Entity Framework 6)

EF generates LEFT OUTER JOIN for optional relationships and INNER JOIN for required relationships.

By using non nullable int type here

public int SourceBookingId { get; set; }

you are telling EF that the relationship is required, i.e. the column value cannot be NULL and there must be always a matching record in SourceBooking table. Hence it generates INNER JOIN.

if that's not the case, simply change the FK property type to nullable

public int? SourceBookingId { get; set; }

How to write this LEFT JOIN query with EF

Try this way:

from cbl in ClaimBatchLines 
join c in Claims on c.ID equals cbl.Claim_ID
join ic in InvoiceClaims on ic.ID equals c.ID into g
from e in g.DefaultIfEmpty()
where cbl.ClaimBatch_ID == 1
select new {cbl, c, e.Reference}

For more info about how to perform a left join in linq take a look this link

LINQ EF Core left join

This should translate to a LEFT JOIN in SQL - note that this will cause duplicate alumni rows if there are more than one matching AlumniSurvey rows.

alumni = alumni.GroupJoin(_context.AlumniSurvey, a => a.Uid, s => s.Uid, (a, sj) => new { a, sj })
               .SelectMany(asj => asj.sj.DefaultIfEmpty(), (asj, s) => new { asj.a, s })
               .Where(x => x.s.UniversityNumber != x.s.Uid)
               .Select(x => x.a);

You can create an extension method to simplify this:

private static Expression<Func<TOuter, TInner, TResult>> CastSMLambda<TOuter, TInner, TResult>(LambdaExpression ex, TOuter _1, TInner _2, TResult _3) => (Expression<Func<TOuter, TInner, TResult>>)ex;

public static IQueryable<TResult> LeftOuterJoin<TOuter, TInner, TKey, TResult>(
    this IQueryable<TOuter> outer,
    IQueryable<TInner> inner,
    Expression<Func<TOuter, TKey>> outerKeyExpr,
    Expression<Func<TInner, TKey>> innerKeyExpr,
    Expression<Func<TOuter, TInner, TResult>> resExpr) {

    var gjResTemplate = new { outer = default(TOuter), innerj = default(IEnumerable<TInner>) };
    // typeof(new { outer, innerj }) oij
    var oijParm = Expression.Parameter(gjResTemplate.GetType(), "oij");
    // TInner inner
    var iParm = Expression.Parameter(typeof(TInner), "inner");
    // oij.outer
    var oijOuter = Expression.PropertyOrField(oijParm, "outer");
    // (oij,inner) => resExpr(oij.outer, inner)
    var selectResExpr = CastSMLambda(Expression.Lambda(resExpr.Apply(oijOuter, iParm), oijParm, iParm), gjResTemplate, default(TInner), default(TResult));

    return outer.GroupJoin(inner, outerKeyExpr, innerKeyExpr, (outer, innerj) => new { outer, innerj })
                .SelectMany(r => r.innerj.DefaultIfEmpty(), selectResExpr);
}

// Apply: (x => f).Apply(args)
/// <summary>
/// Substitutes an array of Expression args for the parameters of a lambda, returning a new Expression
/// </summary>
/// <param name="e">The original LambdaExpression to "call".</param>
/// <param name="args">The Expression[] of values to substitute for the parameters of e.</param>
/// <returns>Expression representing e.Body with args substituted in</returns>
public static Expression Apply(this LambdaExpression e, params Expression[] args) {
    var b = e.Body;

    foreach (var pa in e.Parameters.Zip(args, (p, a) => (p, a)))
        b = b.Replace(pa.p, pa.a);

    return b.PropagateNull();
}

/// <summary>
/// Replaces an Expression (reference Equals) with another Expression
/// </summary>
/// <param name="orig">The original Expression.</param>
/// <param name="from">The from Expression.</param>
/// <param name="to">The to Expression.</param>
/// <returns>Expression with all occurrences of from replaced with to</returns>
public static T Replace<T>(this T orig, Expression from, Expression to) where T : Expression => (T)new ReplaceVisitor(from, to).Visit(orig);

/// <summary>
/// ExpressionVisitor to replace an Expression (that is Equals) with another Expression.
/// </summary>
public class ReplaceVisitor : ExpressionVisitor {
    readonly Expression from;
    readonly Expression to;

    public ReplaceVisitor(Expression from, Expression to) {
        this.from = from;
        this.to = to;
    }

    public override Expression Visit(Expression node) => node == from ? to : base.Visit(node);
}

public static T PropagateNull<T>(this T orig) where T : Expression => (T)new NullVisitor().Visit(orig);

/// <summary>
/// ExpressionVisitor to replace a null.member Expression with a null
/// </summary>
public class NullVisitor : System.Linq.Expressions.ExpressionVisitor {
    public override Expression Visit(Expression node) {
        if (node is MemberExpression nme && nme.Expression is ConstantExpression nce && nce.Value == null)
            return Expression.Constant(null, nce.Type.GetMember(nme.Member.Name).Single().GetMemberType());
        else
            return base.Visit(node);
    }
}

How to specify left join in EF?

Left Outer Join Example：

from c in table0
join o in table1 on c.sno equals o.sno into ps
from o in ps.DefaultIfEmpty()
select new { c.name, o.number}

It render SQL：

SELECT [t0].[name], [t1].[number] AS [number]
FROM [table0] AS [t0]
LEFT OUTER JOIN [table1] AS [t1] ON ([t0].[sno]) = [t1].[sno]    

Combining Left join, .includes() and Group By in Entity Framework

That is because of the query starts being built with a Keyless entity type and also
you Include some collection navigation properties on that Keyless entity.

In the first go you can check the generated equivalent query in SQl-Profiler or by Debug watcher to see what query generated.

but by executing that query the first sub-query will executed first so try to enforce firstQuery be run before anything by ToList() like:

var firstQuery =  _context.Records.GroupBy(r => r.RecordId)
   .Select(g => new 
       {
          Id = g.Key,
          Date = g.Max(x => x.Date)
   }).ToList();

if that doesn't work try this:

var veryFirstQuery =  _context.Records.GroupBy(r => r.RecordId)
   .Select(g => new 
       {
          Id = g.Key,
          Date = g.Max(x => x.Date)
   }).SelectMany(x=> x.Select(u=> u));
var firstQuery = veryFirstQuery.Select(x=> x.Id ).ToList();

and then(this is not correct syntax but just copy from your query and change what I think it needs):

var result = (from u in secondQuery 
                         where firstQuery.Contain(u.Id) 
                         select u)
                        .DefaultIfEmpty()
                        select new UserRecordsRequest{
                            Id = u.Id,
                            Email = u.Email,
                            Name = u.Name,
                            Date = (string?)veryFirstQuery.FirstOrDefault(x=> x.Id == u.Id).Date,
                            Phone = u.Phone,
                            Image = u.Image,
                            Employees = u.Employees,
                            Parents = u.Parents,
                        }.ToList()

or you can:

var result = (from u in secondQuery
                where firstQuery.Contain(u.Id)
                select u)
.DefaultIfEmpty().ToList()
.Select(u => new UserRecordsRequest
{
    Id = u.Id,
    Email = u.Email,
    Name = u.Name,
    Date = (string?)veryFirstQuery.FirstOrDefault(sb => sb.Id == u.Id).Date,
    Phone = u.Phone,
    Image = u.Image,
    Employees = u.Employees,
    Parents = u.Parents,
});

I am trying to say you can separate your main query to little queries to prevent that InvalidOperationException or review your models relationship in your Fluent-Api.

Entity framework produces left join when condition on foreign key

You will need to expose UserId property on Questionnaire manually:

public class Questionnaire
{
    public int Id { get; set; }

    [Required] 
    [MaxLength(500)] 
    public string Title { get; set; }

    public int UserId { get; set; }
    public User User { get; set; }
}

And use it in query instead of a.User.Id:

var questionnaires = this._dbContext.Questionnaires
    .Where(a => a.UserId == 1) // use UserId instead of User.Id
    .ToList(); 

---

Related Topics