`GroupBy` over complex key generates redundant subquery
- 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
Assessment
This issue has not been assessed yet.