C# LINQ - 左连接、分组依据和计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/695506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
LINQ - Left Join, Group By, and Count
提问by pbz
Let's say I have this SQL:
假设我有这个 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:
如何将其转换为 LINQ to SQL？我被 COUNT(c.ChildId) 卡住了，生成的 SQL 似乎总是输出 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() }
Thank you!
谢谢！
采纳答案by Mehrdad Afshari
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) }
回答by Mehrdad Afshari
(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) })
回答by Amy B
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 } ;
回答by Eren Ers?nmez
LATE ANSWER:
迟到的回答：
You shouldn't need the left joinat all if all you're doing is Count(). Note that join...intois actually translated to GroupJoinwhich returns groupings like new{parent,IEnumerable<child>}so you just need to call Count()on the group:
你不应该需要的左连接在所有如果你正在做的是伯爵（）。请注意，join...into实际上转换为GroupJoinwhich 返回分组，new{parent,IEnumerable<child>}因此您只需要调用Count()该组：
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 intois equivalent to GroupJoin(while a joinwithout an intois Join):
在扩展方法语法中， ajoin into等效于GroupJoin（而 ajoin没有intois Join）：
context.ParentTable
.GroupJoin(
inner: context.ChildTable
outerKeySelector: parent => parent.ParentId,
innerKeySelector: child => child.ParentId,
resultSelector: (parent, children) => new { parent.Id, Count = children.Count() }
);
回答by Mosh
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 intosince join intois a group join itself.
虽然 LINQ 语法背后的想法是模拟 SQL 语法，但您不应总是考虑将 SQL 代码直接转换为 LINQ。在这种特殊情况下，我们不需要执行group into因为join into本身就是一个 group join。
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, j2and null checking in Count(t => t.ChildId != null)
与这里大多数投票的解决方案不同，我们不需要j1、j2和Count(t => t.ChildId != null) 中的空检查
