Linq分组依据和计数

DCO 发表于 Dev

dèo

我正在尝试了解linq的分组和计数方式，但是我不知道该怎么做。

我有这张桌子：

ASSET:
Id, Code, Name, ParentId

如果ParentId是根，则为null；如果资产链接到另一资产，则包含父ID。

我想为每个亲生父母提供ID和孩子的数量

这是我使用的查询：

select father.Id, father.Code, COUNT(children.Id) As NumberOfChildren 
from Asset father 
left join Asset children on(father.Id = children.ParentId) 
where father.ParentId IS NULL 
group by father.Id, father.Code

这是我做的linq查询

var query = from father in this.assetService.GetAll() 
                        join children in this.assetService.GetAll() 
                        on father.Id equals children.ParentId into Children 
                        from children in Children.DefaultIfEmpty() 
                        where father.ParentId.Value == null 
                        group father by new { id = father.Id, code = father.Code } into gf 
                        select new { id = gf.Key.id, count = gf.Count() };

但实体会生成该查询：

SELECT 
    1 AS [C1], 
    [GroupBy1].[K1] AS [Id], 
    [GroupBy1].[A1] AS [C2] 
    FROM ( SELECT 
        [Extent1].[Id] AS [K1], 
        [Extent1].[Code] AS [K2], 
        COUNT(1) AS [A1] 
        FROM  [dbo].[Asset] AS [Extent1] 
        LEFT OUTER JOIN [dbo].[Asset] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ParentId] 
        WHERE [Extent1].[ParentId] IS NULL 
        GROUP BY [Extent1].[Id], [Extent1].[Code] 
    )  AS [GroupBy1]

问题来自COUNT(1)，我怎么能知道那应该是COUNT(children.Id)

熵

由于您要处理中的NULL值children.Id，因此需要一种在选择最终对象时对它们进行计数的方法。为此，您将分组到一个新的对象中，可以对其进行查询以获取正确的计数。这是您要查找的修改后的查询对象：

var query = from father in this.assetService.GetAll() 
                        join children in this.assetService.GetAll() 
                        on father.Id equals children.ParentId into Children 
                        from children in Children.DefaultIfEmpty() 
                        where father.ParentId.Value == null 
                        group new { father = father, childExists = (children != null) } by new { id = father.Id, code = father.Code } into gf 
                        select new { id = gf.Key.id, count = gf.Count(o => o.childExists) };

我使用以下C＃小提琴对其进行测试，如果父级没有子级，它将正确返回0条记录。

https://dotnetfiddle.net/gyqpef

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。