dotnet / dotnet/efcore

GroupBy over entity type: stop including all columns

Open
#34,921 2 comments 1 reaction 0 assignees View on GitHub
area-groupby consider-for-next-release customer-reported needs-design
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

Our current implementation of GroupBy over entity type populates all of the entity type's columns into the GROUP BY clause:

```c#
_ = await context.Posts
.GroupBy(p => p.Blog)
.Select(g => new { g.Key.Id, Count = g.Count() })
.ToListAsync();
```

SQL:

```sql
SELECT [b].[Id], COUNT(*) AS [Count]
FROM [Posts] AS [p]
INNER JOIN [Blogs] AS [b] ON [p].[BlogId] = [b].[Id]
GROUP BY [b].[Id], [b].[Name]
```

Note that the LINQ query doesn't project the Blog's Name out, and there's no reason for us to group by Name (grouping by the primary key should be enough - just like in the final GroupBy case). But our SQL tells the database to do the (considerable!) extra work of grouping by Name.

This issue also affects final GroupBy, where the grouping key is converted to orderings, so the final query has ORDER BY for all of the entity type's columns, instead of just for the primary key's columns. Aside from being problematic perf-wise, this is also the cause of https://github.com/npgsql/efcore.pg/issues/3202, where the entity type has a non-sortable column type (`xid`) which causes the query to fail.

Note previous issue #34895 for final GroupBy, and unmerged PR #34896 which specifically removed the unneeded columns post-hoc for final GroupBy; but the more correct fix would be to not have them in the groupby clause in the first place.

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.