LINQ Left Join with Where on Both Tables

I struggled with this one for a bit, but here’s how you do a LINQ “LEFT JOIN” equivalent while applying WHERE to both tables and maintaining an “all” relationship on the first table:

var TeamAllocations = from team in db.Teams
where team.CustomerId == PageContext.CustomerId
join a in
(from x in db.TeamAssetAllocations where x.AssetId == _AssetId select x) on team.Id equals a.TeamId into ta
from a in ta.DefaultIfEmpty()
select new { team.Id, team.Name, a.QuantityAllowed };

Personally, I don’t really get it, especially the “from a in ta.DefaultIfEmpty()” part, but whatever.

One thought on “LINQ Left Join with Where on Both Tables

  1. Richard,
    Thank you for this very useful post. You provide a solution to a common problem that may not be addressed anywhere else. It worked for me but I needed to add a check to avoid an error caused by null values.
    Steve

    var TeamAllocations = from team in db.Teams
    where team.CustomerId == PageContext.CustomerId
    join a in
    (from x in db.TeamAssetAllocations where x.AssetId == _AssetId select x) on team.Id equals a.TeamId into ta
    from a in ta.DefaultIfEmpty()
    select new { team.Id, team.Name, QuantityAllowed = a.QuantityAllowed == null ? 0 : a.QuantityAllowed };

Leave a Reply

Your email address will not be published. Required fields are marked *