Problems with GroupBy

[ajcvickers]

I don't understand what I'm seeing, so recording here for @smitpatel

When run against EF6 (as opposed to EF Core 6.0), all these queries return the same results as L2O, although EF6 ignores the Include. None of them seem to work correctly with EF Core.

Model at end.

1

L2O

var grouping = context.People
    .Include(e => e.Shoes)
    .ToList()
    .GroupBy(e => e.FirstName)
    .ToList();

foreach (var group in grouping)
{
    foreach (var person in group)
    {
        Debug.WriteLine($"{person.FirstName} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
    }
}

Jim Bob has 2 pairs of shoes.
Jim Jon has 2 pairs of shoes.
Jim Don has 2 pairs of shoes.
Jim Zee has 2 pairs of shoes.
Tom Bob has 2 pairs of shoes.
Tom Jon has 2 pairs of shoes.
Tom Don has 2 pairs of shoes.
Tom Zee has 2 pairs of shoes.
Ben Bob has 2 pairs of shoes.
Ben Jon has 2 pairs of shoes.
Ben Don has 2 pairs of shoes.
Ben Zee has 2 pairs of shoes.

EF Core

var grouping = context.People
    .Include(e => e.Shoes)
    .GroupBy(e => e.FirstName)
    .ToList();

System.ArgumentException
must be reducible node
   at System.Linq.Expressions.Expression.ReduceAndCheck()
   at System.Linq.Expressions.Expression.ReduceExtensions()
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExtensionExpression(Expression expr, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpression(Expression node, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.RewriteExpressionFreeTemps(Expression expression, Stack stack)
   at System.Linq.Expressions.Compiler.StackSpiller.Rewrite[T](Expression`1 lambda)
   at System.Linq.Expressions.Expression`1.Accept(StackSpiller spiller)
   at System.Linq.Expressions.Compiler.StackSpiller.AnalyzeLambda(LambdaExpression lambda)
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at System.Linq.Expressions.Expression`1.Compile()
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query) in C:\dotnet\efcore\src\EFCore\Query\QueryCompilationContext.cs:line 206
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async) in C:\dotnet\efcore\src\EFCore\Storage\Database.cs:line 76
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async) in C:\dotnet\efcore\src\EFCore\Query\Internal\QueryCompiler.cs:line 111
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0() in C:\dotnet\efcore\src\EFCore\Query\Internal\QueryCompiler.cs:line 95
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler) in C:\dotnet\efcore\src\EFCore\Query\Internal\CompiledQueryCache.cs:line 74
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query) in C:\dotnet\efcore\src\EFCore\Query\Internal\QueryCompiler.cs:line 91
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression) in C:\dotnet\efcore\src\EFCore\Query\Internal\EntityQueryProvider.cs:line 78
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator() in C:\dotnet\efcore\src\EFCore\Query\Internal\EntityQueryable`.cs:line 90
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Temp.Program.Main() in C:\dotnet\efcore\test\EFCore.SqlServer.FunctionalTests\SqlServerEndToEndTest.cs:line 158

2

L2O

var grouping = context.People
    .Include(e => e.Shoes)
    .OrderBy(e => e.FirstName)
    .ThenBy(e => e.LastName)
    .ToList()
    .GroupBy(e => e.FirstName)
    .Select(g => new { Name = g.Key, People = g.ToList()})
    .ToList();
	
foreach (var group in grouping)
{
    foreach (var person in group.People)
    {
        Console.WriteLine($"{person.FirstName} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
    }
}

Ben Bob has 2 pairs of shoes.
Ben Don has 2 pairs of shoes.
Ben Jon has 2 pairs of shoes.
Ben Zee has 2 pairs of shoes.
Jim Bob has 2 pairs of shoes.
Jim Don has 2 pairs of shoes.
Jim Jon has 2 pairs of shoes.
Jim Zee has 2 pairs of shoes.
Tom Bob has 2 pairs of shoes.
Tom Don has 2 pairs of shoes.
Tom Jon has 2 pairs of shoes.
Tom Zee has 2 pairs of shoes.

EF Core

var grouping = context.People
    .Include(e => e.Shoes)
    .OrderBy(e => e.FirstName)
    .ThenBy(e => e.LastName)
    .GroupBy(e => e.FirstName)
    .Select(g => new { Name = g.Key, People = g.ToList()})
    .ToList();

The LINQ expression 'DbSet<Person>()
    .Include(e => e.Shoes)
    .OrderBy(e => e.FirstName)
    .ThenBy(e => e.LastName)
    .GroupBy(e => e.FirstName)
    .Select(g => new { 
        Name = g.Key, 
        People = g
            .ToList()
     })' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

3

L2O

var grouping = context.People
    .Include(e => e.Shoes)
    .OrderBy(e => e.FirstName)
    .ThenBy(e => e.LastName)
    .ToList()
    .GroupBy(e => e.FirstName)
    .Select(g => new { Name = g.Key, People = g.OrderBy(e => e.LastName).ToList()})
    .ToList();

foreach (var group in grouping)
{
    foreach (var person in group.People)
    {
        Console.WriteLine($"{person.FirstName} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
    }
}

Ben Bob has 2 pairs of shoes.
Ben Don has 2 pairs of shoes.
Ben Jon has 2 pairs of shoes.
Ben Zee has 2 pairs of shoes.
Jim Bob has 2 pairs of shoes.
Jim Don has 2 pairs of shoes.
Jim Jon has 2 pairs of shoes.
Jim Zee has 2 pairs of shoes.
Tom Bob has 2 pairs of shoes.
Tom Don has 2 pairs of shoes.
Tom Jon has 2 pairs of shoes.
Tom Zee has 2 pairs of shoes.

EF Core

var grouping = context.People
    .Include(e => e.Shoes)
    .OrderBy(e => e.FirstName)
    .ThenBy(e => e.LastName)
    .GroupBy(e => e.FirstName)
    .Select(g => new { Name = g.Key, People = g.OrderBy(e => e.LastName).ToList()})
    .ToList();

Ben Bob has 2 pairs of shoes.
Jim Bob has 2 pairs of shoes.
Tom Bob has 2 pairs of shoes.

Test code:

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public ICollection<Shoes> Shoes { get; } = new List<Shoes>();
}

public class Shoes
{
    public int Id { get; set; }
    public string Style { get; set; }
    public Person Person { get; set; }
}

public class SomeDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(Your.ConnectionString)
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    public DbSet<Person> People { get; set; }
}

public class Program
{
    [ConditionalFact]
    public void Main()
    {
        using (var context = new SomeDbContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();

            context.AddRange(
                new Person
                {
                    FirstName = "Jim",
                    LastName = "Bob",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Tom",
                    LastName = "Bob",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Ben",
                    LastName = "Bob",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Jim",
                    LastName = "Jon",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Tom",
                    LastName = "Jon",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Ben",
                    LastName = "Jon",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Jim",
                    LastName = "Don",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Tom",
                    LastName = "Don",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Ben",
                    LastName = "Don",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Jim",
                    LastName = "Zee",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Tom",
                    LastName = "Zee",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                },
                new Person
                {
                    FirstName = "Ben",
                    LastName = "Zee",
                    Shoes = { new() { Style = "Sneakers" }, new() { Style = "Dress" } }
                });

            context.SaveChanges();
        }

        using (var context = new SomeDbContext())
        {
            var grouping = context.People
                .Include(e => e.Shoes)
                .OrderBy(e => e.FirstName)
                .ThenBy(e => e.LastName)
                .GroupBy(e => e.FirstName)
                .Select(g => new { Name = g.Key, People = g.OrderBy(e => e.LastName).ToList()})
                .ToList();

            foreach (var group in grouping)
            {
                foreach (var person in group.People)
                {
                    Debug.WriteLine($"{person.FirstName} {person.LastName} has {person.Shoes.Count} pairs of shoes.");
                }
            }
        }

    }
}

[smitpatel]

1 wouldn't work since GroupBy is final result operator.
2 & 3 should be working. Investigating.

[ajcvickers]

1 works on EF6. Where are we tracking making it work in EF Core? Also, throwing must be reducible node is clearly a bug.

[smitpatel]

#19929
It used to throw better error message. Something regressed. Looking to improve exception message there too.

[smitpatel]

3rd was good catch. Identifiers for collection projection got mangled up.