EF Core GroupBy with Select Distinct Count

Question

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

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

In SQL this is simple:

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

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

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

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.

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?

Thanks!

The open GitHub issue is https://github.com/dotnet/efcore/issues/17376. As usual with EF Core, you are expected to wait (?!). — Ivan Stoev, May 18 '20 at 17:12
That looks to be the exact issue. Thanks for the link. I thumbed it, I guess I'm in the wait and see camp as well... — Josh G, May 18 '20 at 17:40

Arman Ebrahimpour · Answer 1 · 2020-06-03T09:17:53.910

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 Core GroupBy with Select Distinct Count

1 Answers1

Linked