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!

score 199 · Accepted Answer · answered Mar 29 '09 at 22:28

199

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

answered Mar 29 '09 at 22:28

Mehrdad Afshari

414,610
91
852
789

OK, that works, but why? How do you think through it? How does not counting null values give us the same as COUNT(c.ChildId)? Thanks. – pbz Mar 29 '09 at 22:36
4

This is how SQL works. COUNT(fieldname) will count the rows in that field that are not null. Maybe I don't get your question, please clarify if that's the case. – Mehrdad Afshari Mar 29 '09 at 22:38
I guess I always thought about it in terms of counting rows, but you are correct, only the non-null values are counted. Thanks. – pbz Mar 29 '09 at 22:46
1

.Count() will generate COUNT(*) which will count all the rows in that group, by the way. – Mehrdad Afshari Mar 29 '09 at 22:47
I had the exact same problem however comparing t=>t.ChildID != null didn't work for me. The result was always a null object and Resharper complained that the expression was always true. So I used (t => t != null) and that worked for me. – Joe Dec 02 '10 at 01:35
You don't need "group by" here since "join into" in LINQ is essentially group join. Also, "j1", "j2" and null checking are not required either. They are extra noise. See my answer below for a cleaner solution. – Mosh Oct 01 '15 at 04:40

score 63 · Answer 2 · edited Mar 17 '12 at 04:26

63

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

edited Mar 17 '12 at 04:26

Brian Webster

30,033
48
152
225

answered Jul 09 '10 at 00:28

Amy B

108,202
21
135
185

If I remember correctly (it's been a while), that query was a simplified version of a large one. If all I needed was the key and count your solution would've been cleaner / better. – pbz Jul 09 '10 at 18:53
1

Your comment doesn't make sense in context with original question and upvoted answers. Additionally - if you want more than the key, you have the whole parent row to draw from. – Amy B Jul 09 '10 at 21:57
The solution with `let` keyword will generate a subquery as same as @Mosh group joined solution. – Mohsen Afshin May 11 '16 at 08:55
@MohsenAfshin yes, it generates a subquery the same as the query with a subquery in my answer directly above it. – Amy B May 11 '16 at 10:46

score 39 · Answer 3 · edited Apr 02 '17 at 10:00

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

score 8 · Answer 4 · 2009-08-14T08:49:12.510

8

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

edited Aug 14 '09 at 08:49

answered Aug 14 '09 at 08:42

score 8 · Answer 5 · answered Oct 01 '15 at 04:44

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)

LINQ - Left Join, Group By, and Count

5 Answers5

Linked

Related