How to write LINQ left join

Question

 var query = from r in db.Resource
                    join c in db.ResourceProjectedCapacity on r.ID equals c.ResourceID into ps
                    from c in ps.DefaultIfEmpty(null)
                    join p in db.Project on c.ProjectID equals p.ID
                    select new
                    {
                        Capacity = c,
                        Resource = r,
                        Project = p
                    };

I have this linq query but it is only returning resources that have a matching row on ResourceProjectedCapacity table. How can I get all resources and in case they dont have a matching record the Capacity object to be null?

Does this answer help you? [LINQ - Left Join, Group By, and Count](http://stackoverflow.com/questions/695506/linq-left-join-group-by-and-count?rq=1) — Eric Finn, Jun 09 '14 at 11:25

score 1 · Answer 1 · answered Jun 09 '14 at 11:29

from i in db.Resource
let c = db.ResourceProjectedCapacity.Where(cc => i.id == cc.ResourceID).FirstOrDefault()
let p = db.Project.Where(pp => c.ProjectID == pp.ID).FirstOrDefault() 
select new
{
  Capacity = C,
 Resource = i,
 Project = p
 }

try above code

score 1 · Answer 2 · answered Jun 09 '14 at 11:30

I think the secondary inner join messes up the left outer join above it. I think a way to work around it is to break the join into a seperate query, and then left join on that, something like this:

var subquery = from c in db.ResourceProjectedCapacity
               join p in db.Project on c.ProjectID equals p.ID
               select new { c, p };


var query = from r in db.Resource
                    join c in subquery on r.ID equals c.c.ResourceID into ps
                    from c in ps.DefaultIfEmpty(null)
                    select new
                    {
                        Capacity = c.c,
                        Resource = r,
                        Project = c.p
                    };

NB don't worry, it won't do two db queries, it'll only execute against your db once you evaluate query with a .ToList() or something like that.

How to write LINQ left join

2 Answers2