Translate GroupBy followed by FirstOrDefault (and similar) using ROW_NUMBER()

[chenghehe]

What problem are you trying to solve?

var result = await dbContext.YourTable
.GroupBy(e => e.GroupColumn)
.Select(g => g.OrderBy(e => e.SortColumn).FirstOrDefault())
.ToListAsync();

This way of writing will prompt an exception and cannot run

Describe the solution you'd like

No response

[chenghehe]

abnormal information：The given key 'EmptyProjectionMember' was not present in the dictionary

[roji]

@chenghehe the LINQ query as your wrote it is currently not translatable to SQL, since SQL GROUP BY requires an aggregate function (MAX, SUM...) to reduce the group back to a table; see this doc page for more information on this. Such queries typically require the use of SQL window functions, but these aren't yet supported by EF (see #12747).

In the meantime, if you're OK with bringing the rows back from the database and performing the OrderBy and FirstOrDefault on the client, add AsEnumerable() for client evaluation after the GroupBy (but for large numbers of rows this could be problematic for performance).

Am putting this issue in the backlog to track translating the above query pattern via window functions:

var result = await dbContext.YourTable
    .GroupBy(e => e.GroupColumn)
    .Select(g => g.OrderBy(e => e.SortColumn).FirstOrDefault())
    .ToListAsync();

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY [GroupColumn] ORDER BY [SortColumn]) AS rn
    FROM [YourTable]
) ranked
WHERE rn = 1;

The same can be done for ElementAt, Last, etc.

[m-gasser]

I wonder whether the way to go for generating GroupBy should be using JSON_ARRAYAGG in newer SQL Server versions and json_agg for Postgres.
No idea how well it performs, but it would help for all "the world is flat" issues inherited by relational algebra. Another place where this could be useful, is to implement .Include(). It might avoid performance issues (due to duplication) caused by using joins, without causing tearing issues, as .AsSplitQuery() does.

[bachratyg]

@m-gasser That should be #30958 (comment). Perf could also be poor, see #22435 (comment). Better to solve the cartesian problem first.

[roji]

@m-gasser I don't exactly see the relevance of JSON for this issue specifically, since the query (GroupBy followed by FirstOrDefault) projects a completely flat, classical relational resultset. Where do you JSON fitting in here?