GroupBy() in EF Core 3.1 seems to be broken

Question

Trying to migrate from EF6 to EFCore 3.1 is a very frustrating process due to a number of breaking changes they introduced. However, GroupBy() is promised to be supported in 3.1, right?

IQueryable<Condition> myquery = dbctx.Conditions.Where(e => e.Rule.Namespace == "test");

var d = await myquery.GroupBy(e => e.RuleId).SelectMany(e => e).ToArrayAsync();

fails with

System.InvalidOperationException : Processing of the LINQ expression 'x => x' 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.

Seeing that even simple queries EF6 was translating without issues do not work in EF Core is just unbelievable :( Is there a workaround for this sort of queries?

Answer 1

Nothing to surprise, EF Core 3.1 is less feature supported version of EF Core and has a lot of limitations especially for grouping. These limitations are closely coupled with SQL limitations.

For example, your query

var d = await myquery.GroupBy(e => e.RuleId)
   .SelectMany(e => e)
   .ToArrayAsync();

Means that you want to group records and return grouping details. This query has no analogues in SQL world.

Solution is simple - do grouping on the client side:

var d = (await myquery.ToListAsync())
   .GroupBy(e => e.RuleId)
   .SelectMany(e => e)
   .ToArray();

For query

var d = await myquery.GroupBy(e => e.RuleId).CountAsync();

Looks like again about EF Core 3.1 limitation (EF Core 6+ should handle this) As workaround you can replace with the following:

var d = await myquery
   .Select(e => e.RuleId)
   .Distinct()
   .CountAsync();