dotnet / dotnet/efcore

Translate GroupBy with elements projection via window functions instead of self-join

Open
#36,509 1 comment 1 reaction 0 assignees View on GitHub
area-perf area-query
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

EF supports GroupBy, followed by a Select which projects out the elements themselves:

```c#
public virtual Task GroupBy_Select_grouping_ToList_and_aggregate_function(bool async)
=> AssertQuery(
async,
ss => ss.Set()
.GroupBy(o => o.CustomerID)
.Select(g => new
{
Orders = g.ToList(),
Average = g.Average(o => o.Price)
}));
```

This, of course, isn't supported as-is by SQL GROUP BY, which collapses rows together and can only return the result of aggregate functions (as well as grouping keys).

This sort of query can be naturally and efficiently handled by window functions (#12747):

```sql
SELECT [o].[Id], [o].[Foo], ..., AVERAGE([o].[Price]) OVER (PARTITION BY [o].[CustomerID])
FROM [Orders] AS [o]
```

However, EF currently translates this via an inefficient self-join instead:

```sql
SELECT [o1].[CustomerID], [o0].[OrderID], [o0].[CustomerID], [o0].[EmployeeID], [o0].[OrderDate], [o1].[c]
FROM (
SELECT AVG([o].[Price]) AS [c], [o].[CustomerID]
FROM [Orders] AS [o]
GROUP BY [o].[CustomerID]
) AS [o1]
LEFT JOIN [Orders] AS [o0] ON [o1].[CustomerID] = [o0].[CustomerID]
ORDER BY [o1].[CustomerID]
```

Change the translation to use window functions.

Note: the window function translation works only when the grouped elements are projected as is (`g.ToList()`), without any addition LINQ operators composed over the group. Some very specific operators may be supported on the group (e.g. Where can be translated to FILTER in some/most databases - but not all); but in the general case, the existence of operators would cause us to fall back to the current JOIN-based translation.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.