GroupBy with resultSelector neutralizes Include
- Dominant language
- C#
- Stars
- 14.8k
- Forks
- 3.4k
- PR merge metrics
- PR metrics pending
Description
## The problem
`GroupBy` when using the overload which has the `resultSelector` parameter seems to *neutralize* `Include`, so the related entities **do not get included** (the generated SQL **does not `JOIN`** the table for the related entities). I believe that this is a **wrong** behavior.
Note that `GroupBy` combined with `Select` does not neutralize `Include`, so the the related entities get included (the generated SQL `JOIN`s the table for the related entities). This is the **good** behavior.
I think that "*`GroupBy` with `resultSelector`*" should behave like "*`GroupBy` then `Select`*", i.e. it should not neutralize `Include`.
## Code to reproduce the problem
``` csharp
void Main()
{
var contextOptions = new DbContextOptionsBuilder()
.UseSqlServer(@$"Server=(localdb)\mssqllocaldb;Database={nameof(IncludeGroupByDbContext)};Trusted_Connection=True")
.LogTo(Console.WriteLine)
.Options;
using (var dbContext = new IncludeGroupByDbContext(contextOptions))
{
dbContext.Database.EnsureDeleted();
dbContext.Database.EnsureCreated();
dbContext.People.AddRange(
new Person
{
Name = "Joe",
Age = 25,
Pets = new []
{
new Animal
{
Name = "Buddy"
}
}.ToList()
},
new Person
{
Name = "Joe",
Age = 41,
Pets = new[]
{
new Animal
{
Name = "Coco"
}
}.ToList()
},
new Person
{
Name = "Bill",
Age = 32,
Pets = new[]
{
new Animal
{
Name = "Daisy"
},
new Animal
{
Name = "Stella"
}
}.ToList()
}
);
dbContext.SaveChanges();
}
using (var dbContext = new IncludeGroupByDbContext(contextOptions))
{
// wrong behavior: Pets are *not* included, because GroupBy with resultSelector neutralizes Include
dbContext.People
.Include(person => person.Pets)
.GroupBy(person => person.Name, (name, sameNamePersonGroup) => sameNamePersonGroup.OrderBy(person => person.Age).First())
.Dump();
}
using (var dbContext = new IncludeGroupByDbContext(contextOptions))
{
// good behavior: Pets are included, because GroupBy then Select does not neutralize Include
dbContext.People
.Include(person => person.Pets)
.GroupBy(person => person.Name)
.Select(sameNamePersonGroup => sameNamePersonGroup.OrderBy(person => person.Age).First())
.Dump();
}
}
public class IncludeGroupByDbContext : DbContext
{
public DbSet People { get; set; }
public DbSet Animals { get; set; }
public IncludeGroupByDbContext(DbContextOptions options)
: base(options)
{
}
}
public class Person
{
public int PersonId { get; set; }
public required string Name { get; set; }
public int Age { get; set; }
public List Pets { get; set; }
}
public class Animal
{
public int AnimalId { get; set; }
public string? Name { get; set; }
public int? OwnerId { get; set; }
public Person? Owner { get; set; }
}
```
## EF log
Generated SQL for "*`GroupBy` with `resultSelector`*" (`Animal` table is **not** joined, despite the `Include`):
``` sql
SELECT [t0].[PersonId], [t0].[Age], [t0].[Name]
FROM (
SELECT [p].[Name]
FROM [People] AS [p]
GROUP BY [p].[Name]
) AS [t]
LEFT JOIN (
SELECT [t1].[PersonId], [t1].[Age], [t1].[Name]
FROM (
SELECT [p0].[PersonId], [p0].[Age], [p0].[Name], ROW_NUMBER() OVER(PARTITION BY [p0].[Name] ORDER BY [p0].[Age]) AS [row]
FROM [People] AS [p0]
) AS [t1]
WHERE [t1].[row] <= 1
) AS [t0] ON [t].[Name] = [t0].[Name]
```
Generated SQL for "*`GroupBy` then `Select`*" (`Animal` table is joined):
``` sql
SELECT [t0].[PersonId], [t0].[Age], [t0].[Name], [t].[Name], [a].[AnimalId], [a].[Name], [a].[OwnerId]
FROM (
SELECT [p].[Name]
FROM [People] AS [p]
GROUP BY [p].[Name]
) AS [t]
LEFT JOIN (
SELECT [t1].[PersonId], [t1].[Age], [t1].[Name]
FROM (
SELECT [p0].[PersonId], [p0].[Age], [p0].[Name], ROW_NUMBER() OVER(PARTITION BY [p0].[Name] ORDER BY [p0].[Age]) AS [row]
FROM [People] AS [p0]
) AS [t1]
WHERE [t1].[row] <= 1
) AS [t0] ON [t].[Name] = [t0].[Name]
LEFT JOIN [Animal] AS [a] ON [t0].[PersonId] = [a].[OwnerId]
ORDER BY [t].[Name], [t0].[PersonId]
```
## Provider and version information
EF Core version: 7.0.10
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 10
IDE: Visual Studio 2022 17.7.3, LINQPad
Contributor guide
Assessment
This issue has not been assessed yet.