GroupBy().Select().Where() works for simple POCOs but not when using a class or record with a constructor for initialization

[bradtglass]

Description

There some sample code below which explain the issue better than me but basically if you do a GroupBy() then a Select() then a Where() then there are some cases where this order works and some where it doesn't. Usually I would just put the select after the Where() to fix this but we have some examples where we're selecting aggregate values which are needed in the Where().

Basically this works when the Select() is creating a new object by property initialisation syntax but not when using constructor initialisation. I can understand why logically this is easier, we're setting a property and then using it's value so we can track which value that was.

If constructor initialisation was supported in this scenario I would expect it to use property conventions similar to those that EF core uses for entity classes (if the name and type of the constructor parameter is the same but lower case of a property then match them up). This is a scenario that comes up a lot where we use the Select() to project our query into a record type which will usually only have a constructor.

Example

This example is the simplest I could make to replicate the Exception. It shows the same query projecting into a simple POCO class and a record, the first query executes fine, the second throws an Exception (details at the bottom).

I tested this sample using EF Core 7 and the preview of EF Core 8 and got the same result in both. Also worth pointing out that this is with SQL Server, I assume this will be different in other providers.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Design;

namespace ConsoleApp1;

internal static class Program
{
    public static async Task Main(string[] args)
    {
        var dbContext = new DesignTimeFactory().CreateDbContext(args);

        // This will execute because we are setting the Total property directly
        var good = await dbContext.Foos.GroupBy(f => f.Name)
                                  .Select(x => new Projection
                                   {
                                       Name = x.Key,
                                       Total = x.SelectMany(y => y.Bars).Sum(y => y.Number)
                                   })
                                  .Where(x => x.Total > 5)
                                  .ToListAsync();

        // This will throw because we are setting Total via the constructor
        var bad = await dbContext.Foos.GroupBy(f => f.Name)
                                 .Select(x => new RecordProjection(x.Key, x.SelectMany(y => y.Bars).Sum(y => y.Number)))
                                 .Where(x => x.Total > 5)
                                 .ToListAsync();
    }
}

public class DesignTimeFactory : IDesignTimeDbContextFactory<Context>
{
    public Context CreateDbContext(string[] args) =>
        new(new DbContextOptionsBuilder<Context>()
           .UseSqlServer("YOUR_CONNECTION_STRING")
           .Options);
}

public class Context : DbContext
{
    public Context(DbContextOptions options) : base(options) { }

    public DbSet<Foo> Foos => Set<Foo>();
    public DbSet<Bar> Bar => Set<Bar>();
}

public class Foo
{
    public Guid Id { get; set; }

    public List<Bar> Bars { get; } = new();

    public required string Name { get; set; }
}

public class Bar
{
    public Guid Id { get; set; }

    public int Number { get; set; }
}

public class Projection
{
    public required string Name { get; init; }
    public int Total { get; init; }
}

public record RecordProjection(string Name, int Total);

The second query produces the following exception details:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Foo>()
    .GroupBy(f => f.Name)
    .Select(g => new RecordProjection(
        g.Key, 
        g
            .AsQueryable()
            .SelectMany(
                collectionSelector: e => DbSet<Bar>()
                    .Where(b => EF.Property<Guid?>(e, "Id") != null && object.Equals(
                        objA: (object)EF.Property<Guid?>(e, "Id"), 
                        objB: (object)EF.Property<Guid?>(b, "FooId"))), 
                resultSelector: (e, c) => new TransparentIdentifier<Foo, Bar>(
                    Outer = e, 
                    Inner = c
                ))
            .Sum(ti => ti.Inner.Number)
    ))
    .Where(e0 => e0.Total > 5)' could not be translated. 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|16_0(ShapedQueryExpression translated, <>c__DisplayClass16_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.Translate(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at ConsoleApp1.Program.Main(String[] args) in C:\Users\BradGlass\dev\Temp\ConsoleApp1\ConsoleApp1\Program.cs:line 23
   at ConsoleApp1.Program.<Main>(String[] args)

[roji]

This is by-design. Regardless of the GroupBy or anything else going on in the query, EF does not translate constructor calls inside queries. This is because constructors are functions which could contain arbitrary logic which EF cannot know about; EF doesn't translate arbitrary function calls either for the same reason.

EF does translate instantiations which use initializers (e.g. new Blog { Name = x }), since you're clearly specifying which properties should get assigned which values. That's a compromise to allow people to project out their own types.

Note that I came across a bit of oddness in EF's policy here, opened #31315 to track.

[bradtglass]

Thanks @roji that makes sense, it feels like a limitation but I can understand why we wouldn't want to run the risk of side effects from the constructor call