Left Join in Linq to Entities

LEFT JOIN in LINQ to entities?

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 => mapping.BEBG_BE == users.BE_ID).DefaultIfEmpty()
    from groups in Repo.T_Benutzergruppen
        .Where(gruppe => 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.

How to do LEFT JOIN in LINQ to Entities?

DefaultIfEmpty is used for left joins for EntityFramework 4+

var query = from u in context.Users
            from a in context.Addresses
                             .Where(x => u.Primary2Address == x.AddressiD)
                             .DefaultIfEmpty()
            from s in context.States
                             .Where(x => a.Address2State == x.StateID)
                             .DefaultIfEmpty()
            from c in context.Countries
                             .Where(x => a.CountryID == x.CountryID)
                             .DefaultIfEmpty()
            select u.UserName;

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 left join with filtering in joined

Two Way solve Solution

Remove Where Case

Add where Case In Join table

Show for Where Case Blow Linq Query

first Query

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE.where(c=>c.HOTID==hotelId)   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.DefaultIfEmpty()   
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();

Second is:

IEnumerable<DTOHotMealsPrice> lst = (from m in this.dbEntity.HOT_MEALS
join ml in this.dbEntity.HOT_MEALS_PRICE   on m.MEALSID equals ml.MEALSID into mls
from mls1 in mls.where(c=>c.HOTID==hotelId).DefaultIfEmpty()
select new DTOHotMealsPrice
{
    MEALSID = m.MEALSID,
    MEALSNAME = m.MEALSNAME,
    CHPRICE = mls1.CHPRICE,
    PRICE = mls1.PRICE,
    HOTID = mls1.HOTID
}).Distinct().ToList();

Left outer join in C# LINQ

Based upon comment by @GertArnold, RC-F (tables) and RC-E (tables) are in N-1 association, where RC should hold a valid id key reference for F, but in RC table, some F ids are zeros, which is skipping data when I do inner join.

This is how I ended up querying the results.

            List<MyType> myResults;
            using (Entities context = new Entities())
            {
                var results = (from rc in context.RC
                    join r in context.R on rc.RId equals r.Id
                    join c in context.C on rc.CId equals c.Id
                    select rc).ToList();

                myResults = results.Select(rc => new MyType
                {
                    Id = rc.Id,
                    Rule = new IdName
                    {
                        Id = rc.R.Id,
                        Name = rc.R.Name
                    },
                    Conjuction = new IdName
                    {
                        Id = rc.C.Id,
                        Name = rc.C.Conjuncation
                    },
                    Field = new IdName
                    {
                        Id = rc.F!= null ? rc.F.Id : 0,
                        Name = rc.F!= null ? rc.F.Name : null
                    },
                    Expression = new IdName
                    {
                        Id = rc.E!= null ? rc.E.Id : 0,
                        Name = rc.E!= null ? rc.E.Expression : null
                    },
                    DisplayOrder = rc.Order,
                    Value1 = rc.Value,
                    Value2 = rc.Value2
                }).ToList();
            }

Left Join on Linq to Entity issue

Below linq code has issues including the and clause {and [Extent2].[CHEM_ID] IS NULL}.

You forgot to include null check in the LINQ query where clause. The only specific here is that you can't check temp.CHEM_ID property because from C# compiler perspective it's non nullable, so you have to apply the check on temp object instead (EF is smart enough to translate it to correct SQL).

Here is the working query:

var query =
    from objOrder in context.ORDERS
    join objOrderAssigned in context.ORDERS_ASSIGNED
    on new { key1 = objOrder.CHEM_ID, key2 = objOrder.ORDER_NBR }
    equals new { key1 = objOrderAssigned.CHEM_ID, key2 = objOrderAssigned.ORDER_NO }
    into assignedOrders
    from objOrderAssigned in assignedOrders.DefaultIfEmpty()
    where objOrder.ORDER_STATUS == "NEW" && objOrderAssigned == null
    select new Order
    {
        compoundid = objOrder.CHEM_ID,
        orderno = objOrder.ORDER_NBR,
        route = objOrder.ROUTE_CD,
    };

LINQ to Entities - Left join not pulling data

Have you tried a plain old SQL query?
You are asking to trust all your joins and without seeing the data.
I'd suggest running a query directly on the DB.

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

---

Related Topics