LINQ - Left Join, Group By, and Count
from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, Count = grouped.Count(t=>t.ChildId != null) }
LATE ANSWER:
You shouldn't need the left join at all if all you're doing is Count(). Note that join...into
is actually translated to GroupJoin
which returns groupings like new{parent,IEnumerable<child>}
so you just need to call Count()
on the group:
from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into g
select new { ParentId = p.Id, Count = g.Count() }
In Extension Method syntax a join into
is equivalent to GroupJoin
(while a join
without an into
is Join
):
context.ParentTable
.GroupJoin(
inner: context.ChildTable
outerKeySelector: parent => parent.ParentId,
innerKeySelector: child => child.ParentId,
resultSelector: (parent, children) => new { parent.Id, Count = children.Count() }
);
(from p in context.ParentTable
join c in context.ChildTable
on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
select new {
ParentId = p.ParentId,
ChildId = j2==null? 0 : 1
})
.GroupBy(o=>o.ParentId)
.Select(o=>new { ParentId = o.key, Count = o.Sum(p=>p.ChildId) })
Consider using a subquery:
from p in context.ParentTable
let cCount =
(
from c in context.ChildTable
where p.ParentId == c.ChildParentId
select c
).Count()
select new { ParentId = p.Key, Count = cCount } ;
If the query types are connected by an association, this simplifies to:
from p in context.ParentTable
let cCount = p.Children.Count()
select new { ParentId = p.Key, Count = cCount } ;