pbz
pbz

Reputation: 9085

LINQ - Left Join, Group By, and Count

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!

Upvotes: 176

Views: 214549

Answers (5)

Amy B
Amy B

Reputation: 110071

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 { p.ParentId, /* p.Foo, p.Bar, p.Baz, */ 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 } ;

Upvotes: 64

Eren Ersönmez
Eren Ersönmez

Reputation: 39085

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

Upvotes: 39

Mosh
Mosh

Reputation: 6044

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)

Upvotes: 8

雪域飞貂
雪域飞貂

Reputation:

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

Upvotes: 8

Mehrdad Afshari
Mehrdad Afshari

Reputation: 421968

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

Upvotes: 199

Related Questions