[聚合问答] LINQ - Left Join, Group By, and Count

c#,.net,linq,linq-to-sql 2017-11-30 9 阅读

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!

5个回答

177

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

2017-11-30
53

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

2017-11-30
24

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

2017-11-30
7

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

2017-11-30
5

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)

2017-11-30

注:本文内容来自互联网,旨在为开发者提供分享、交流的平台。如有涉及文章版本等事宜,请你联系站长进行处理。