dotnet / dotnet/efcore

GroupBy with resultSelector neutralizes Include

Open
#31,622 1 comment 0 reactions 0 assignees View on GitHub
area-query customer-reported
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

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.