LINQ - Left Join Group By and Count

Question

Let s say I have this SQL   SELECT p ParentId  COUNT c ChildId  FROM ParentTable p   LEFT OUTER JOIN ChildTable c ON p ParentId   c ChildParentId GROUP BY p ParentId   How can I translate this into LINQ to SQL  I got stuck at the COUNT c ChildId   the generated SQL always seems to output COUNT     Here s what I got so far   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       Thank you

User · Answer

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

User · Answer

While the idea behind LINQ syntax is to emulate the SQL syntax  you shouldn t always think of directly translating your SQL code into LINQ  In this particular case  we don t need to do group into since join into is a group join itself    Here s my solution   from p in context ParentTable join c in context ChildTable on p ParentId equals c ChildParentId into joined select new   ParentId   p ParentId  Count   joined Count       Unlike the mostly voted solution here  we don t need j1  j2 and null checking in Count t    t ChildId    null

User · Answer

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  gt o ParentId       Select o  gt new   ParentId   o key  Count   o Sum p  gt p ChildId

User · Answer

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 lt child gt   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   gt  parent ParentId          innerKeySelector  child   gt  child ParentId            resultSelector   parent  children    gt  new   parent Id  Count   children Count

User · Answer

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  gt t ChildId    null

[c#] LINQ - Left Join, Group By, and Count

Examples related to c#

Examples related to .net

Examples related to linq

Examples related to linq-to-sql