小编典典

LINQ - 左连接、分组依据和计数

all

假设我有这个 SQL:

SELECT p.ParentId, COUNT(c.ChildId)
FROM ParentTable p
  LEFT OUTER JOIN ChildTable c ON p.ParentId = c.ChildParentId
GROUP BY p.ParentId

如何将其转换为 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() }

谢谢!


阅读 109

收藏
2022-08-24

共1个答案

小编典典

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) }
2022-08-24