EF核心小组按选择的独立计数 [英] EF Core GroupBy with Select Distinct Count

问题描述

我有一张表Items，它与两个不同的父母有一对多的关系.

I have a table, Items, which has a many to one relationship with two distinct parents.

我想为每个ParentB选择ParentA的计数.

I want to select the counts of ParentA for each ParentB.

在SQL中，这很简单:

In SQL this is simple:

SELECT "ParentBId", count(distinct "ParentAId") 
FROM "Items"
GROUP BY "ParentBId"

在Linq中，我有以下声明:

In Linq I have this statement:

var itemCounts = await _context.Items
    .GroupBy(item => item.ParentBId,
        (parentBId, items) => new
        {
            ParentBId = parentBId,
            Count = items.Select(item => item.ParentAId).Distinct().Count(),
        }).ToDictionaryAsync(group => group.ParentBId, group => group.Count);

运行此查询时，EF出现以下错误:

When running this query, EF is blowing up with this error:

System.InvalidOperationException: Processing of the LINQ expression 'AsQueryable<string>(Select<Item, string>(
    source: NavigationTreeExpression
        Value: default(IGrouping<string, Item>)
        Expression: (Unhandled parameter: e), 
    selector: (item) => item.ParentAId))' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
...

Items表的确使用每个层次的表和一个区分符列来确定项目类型.我不知道这是否是一个因素.

The Items table does use Table per hierarchy with a discriminator column to determine what the item type is. I do not know if this is a factor.

我已经看到很多人推荐items.Select(i => i.Field).Distinct().Count()选项，但这似乎在这里不起作用.还有其他建议吗?

I have seen lots of people recommend the items.Select(i => i.Field).Distinct().Count() option, but this doesn't seem to be working here. Any other suggestions?

谢谢！

推荐答案

当前，组内有任何区别(例如GroupBy的ElementSelector内的Distinct或GroupBy的ElementSelector内的另一个GroupBy )不受EF Core支持.如果在这种情况下坚持使用EF，则必须获取内存中的一些数据:

Currently any kind of distinction inside groups (like Distinct inside ElementSelector of GroupBy or another GroupBy inside ElementSelector of GroupBy) isn't supported by EF Core. If you insist on using EF in this case, you have to fetch some data in memory:

var result = (await _context.Items
              .Select(p => new { p.ParentAId, p.ParentBId })
              .Distinct()
              .ToListAsync())  // When EF supports mentioned cases above, you can remove this line!
              .GroupBy(i => i.ParentBId, i => i.ParentAId)
              .ToDictionary(g => g.Key, g => g.Distinct().Count());

这篇关于EF核心小组按选择的独立计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！