dotnet / dotnet/efcore

`GroupBy` over complex key generates redundant subquery

Open
#30,113 3 comments 5 reactions 0 assignees View on GitHub
area-groupby area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

## Summary
In case anything different from property or navigation access is performed in `GroupBy` method, the generated query will add an additional subquery. Probably to avoid repeating SQL expression used in `group by`.

## Example
Having the following context:
```csharp
public class Entity
{
public int Id { get; private set; }
}

public class AppContext: DbContext
{
public DbSet Entities { get; private set; }

//...
}
```

and perfoming query:
```csharp
await context.Entities
.GroupBy(e => e.Id.ToString())
.Select(g => g.Key)
.ToListAsync()
```

version `7.0.0-preview.5.22302.2` (and later, up to `7.0.2` as of the time of writing) generates this SQL:
```sql
SELECT [t].[Key]
FROM (
SELECT CONVERT(varchar(11), [e].[Id]) AS [Key]
FROM [Entities] AS [e]
) AS [t]
GROUP BY [t].[Key]
```

while version `7.0.0-preview.4.22229.2` (and before) does querying via:
```sql
SELECT CONVERT(varchar(11), [e].[Id])
FROM [Entities] AS [e]
GROUP BY CONVERT(varchar(11), [e].[Id])
```

Changing the `GroupBy` expression to `e => e.Id` (or navigation property access ) gets rid of subquery in all cases, yet using something as simple as `e => 1` still generates one.

**MRE is attached:** [GroupBySubquery.zip](https://github.com/dotnet/efcore/files/10474571/GroupBySubquery.zip). Just replace the connection string with the one you use.

## Сonclusion
The first option seems to generally have a worse execution plan, especially as the main query grows more complex.

I'm not sure if this was an intentional change or an introduced bug, but in any case, user needs to have the option to revert to an old behaviour, as this can affect query performance quite noticeably.

In similar looking issues I found a suggested workaround of adding
```csharp
AppContext.SetSwitch("Microsoft.EntityFrameworkCore.Issue27102", true);
```
but this doesn't seem to affect this case.

## Environment

**EF Core version:** `7.0.2`, `7.0.0-preview.5.22302.2`
**Database provider:** Microsoft.EntityFrameworkCore.SqlServer
**Target framework:** .NET 7.0
**Operating system:** Windows

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.