GroupBy(...).Select(x => x.First()).Select(...) could not be translated

[MiniaczQ]

Hello, I've ran into a problem while writing a query for my leaderboard database.

I'm using EFCore 6.0.5 and NpgSQL 6.0.5.

I want to get all the entries ordered by points (score), but also want to only show the best entry per player (UserId).
For that I use poor man's DistinctBy, the GroupBy(x => x.Key).Select(x => x.First()).
Furthermore I need to also attach rownumber after that, to have the ability to find position of any player on the board.
(I'll be asking about that in another issue)

The problem

When I tried to create the following query:

await _ctx.Entries.OrderBy(e => e.Points).GroupBy(e => e.UserId).Select(e => e.First()).Select(s => s.SomeField).ToListAsync();

I've got an error saying:

An unhandled exception has occurred while executing the request. {EventId={Id=1, Name="UnhandledException"}, SourceContext="Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware", RequestId="0HMJ5JRGT0IMH:00000002", RequestPath="/leaderboard", ConnectionId="0HMJ5JRGT0IMH"}
System.InvalidOperationException: The LINQ expression 'ProjectionBindingExpression: 0' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for
more information.

The Select(s => s.SomeField) does model mapping, but just accessing the fields seems to cause the error.
This is important, because I need to fill the Position field later down the line.

The same error also happens for FirstOrDefault.
Using methods like SelectMany with Take(1) failed in other ways.

Moving the OrderBy right before ToListAsync causes a different error (if that's of any help):

[2022-07-14 15:53:42.664 ERR] An unhandled exception has occurred while executing the request. {EventId={Id=1, Name="UnhandledException"}, SourceContext="Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware", RequestId="0HMJ5K10P88HJ:00000002", RequestPath="/leaderboard", ConnectionId="0HMJ5K10P88HJ"}
System.InvalidOperationException: The LINQ expression 'DbSet<Entry>()
    .GroupBy(s => s.UserId)
    .Select(g => g
        .AsQueryable()
        .First())
    .OrderByDescending(e0 => e0.Points)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

It's worth noting that

await _ctx.Entries.OrderBy(e => e.Points).GroupBy(e => e.UserId).Select(e => e.First()).ToListAsync();

which doesn't access the fields after grouping works fine.

I want to do it fully db-side, so AsEnumerable is not an option.

Is this intended behavior or am I doing something stupid?

[RichardD2]

Have you tried either:

_ctx.Entries.GroupBy(e => e.UserId).Select(g => g.OrderBy(e => e.Points).First())

or:

_ctx.Entries.GroupBy(e => e.UserId, (_, g) => g.OrderBy(e => e.Points).First())

[ajcvickers]

Duplicate of #26748