Efcore: GroupBy ForeignKey not supported in EF CORE 3.1?

[PeterOeClausen]

I just upgraded to EF CORE 3.1 with a Postgres Db.

After upgrading, I noticed that almost all my GroupBy() statements throws an exception.

I made a simple example. With two entities: A and B.
A is the principal entity and contains a list of B.
B is the descendant and has a ForeignKey to A.

A simple GroupBy like this:

Context.Bs
   .GroupBy(b => b.AId, (key, values) => new { key = key, values = values })
   .CountAsync();

This example throws the exception:

Message: 
    System.InvalidOperationException : Processing of the LINQ expression '(GroupByShaperExpression:
    KeySelector: (b.AId), 
    ElementSelector:(EntityShaperExpression: 
        EntityType: B
        ValueBufferExpression: 
            (ProjectionBindingExpression: EmptyProjectionMember)
        IsNullable: False
    )
    )' by 'RelationalProjectionBindingExpressionVisitor' 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.

Why is this?

I'm not accessing a navigational property like b.A.Id, I'm accessing a ForeignKey that I know exists in the Bs table in my database.

I know this might be due to this query translated badly, and EF throws an exception, cause I'm not supposed to evaluate on client. But this query seems to be able to be translated to SQL.

I also know that this query can be changed to a simpler query using .Where() and .Count(), but it's not my point.

My question is: Why can't GroupBy queries using an existing ForeignKey in descendant be evaluated server-side?

Edit: Found the culprit! (I'm not sure GroupBy can return groups with lists)

Notice that I'm saying values = values in my previous example. This is what I think is creating the error, because I'm returning a List/Enumerable in GroupBy, and that does not make sense in SQL.

Instead, GroupBy can only (to my observation) return flat objects, so this would work:

var query = from p in context.Set<Post>()
   group p by p.AuthorId into g
   select new
      {
         g.Key,
         Count = g.Count()
      };

However the example I originally state would work in EF Core 2.2 that I came from (though, probably due to client-side evaluation which has become an error in 3.1 which is a good thing).

I'm investigating GroupJoin() as it might be the method I'm looking for: https://docs.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupjoin

As it seems I can't join in a GroupBy().

But nonetheless. It could have been nice with a better Exception message to understand that I was using GroupBy() wrong - That I cannot join in them, and that they need to be flat.

Further technical details

EF Core version:
Database provider: (Npgsql.EntityFrameworkCore.PostgreSQL)
Target framework: (.NET Core 3.1)
Operating system: Windows 10
Database version: postgres (PostgreSQL) 12.2 (Debian 12.2-2.pgdg100+1) running in Docker Container.
IDE: (Visual Studio 2019 16.4.5)

[jaumemilian]

I'm struggling witht the same issue today !
If there is a solution to perform the query properly in server side, please, let us know.
Meanwhile I just did 2 separated queries :(

[PeterOeClausen]

@jaumemilian see my edit :)

[ErikEJ]

@PeterOeClausen

However the example I originally state would work in EF Core 2.2 that I came from.

Probably because you had not disabled client evaluation, so it was done client side!

[PeterOeClausen]

@ErikEJ That is true. Thank you for pointing that out.