EFCore fails to filter when projecting to a C# record type

[alaatm]

Given the following:

public class Blog
{
    public int Id { get; set; }
    public int Name { get; set; }
    public ICollection<Post> Posts { get; set; }
}

public class Post
{
    public int Id { get; set; }
}

Filtering on a projection to a defined type (class) or an anonymous type works:

var q1 = db
    .Blogs
    .Select(p => new C
    {
        Name = p.Name,
        PostCount = p.Posts.Count
    });

q1 = q1.Where(p => p.PostCount > 5);
Console.WriteLine(q1.ToQueryString());

public class C
{
    public string Name { get; set; }
    public int PostCount { get; set; }
}

var q2 = db
    .Blogs
    .Select(p => new
    {
        p.Name,
        PostCount = p.Posts.Count
    });

q2 = q2.Where(p => p.PostCount > 5);
Console.WriteLine(q1.ToQueryString());

Both work and produce:

SELECT [b].[Name], (
    SELECT COUNT(*)
    FROM [Post] AS [p0]
    WHERE [b].[Id] = [p0].[BlogId]) AS [PostCount]
FROM [Blogs] AS [b]
WHERE (
    SELECT COUNT(*)
    FROM [Post] AS [p]
    WHERE [b].[Id] = [p].[BlogId]) > 5

However, filtering on a projection to a C# record fails:

var q3 = db
    .Blogs
    .Select(p => new R(p.Name, p.Posts.Count));

q3 = q3.Where(p => p.PostCount > 5);
Console.WriteLine(q3.ToQueryString());

public record R(string Name, int PostCount);

This throws the following:

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Blog>()
    .Where(b => new R(
        b.Name,
        DbSet<Post>()
            .Where(p => EF.Property<int?>(b, "Id") != null && object.Equals(
                objA: (object)EF.Property<int?>(b, "Id"),
                objB: (object)EF.Property<int?>(p, "BlogId")))
            .Count()
    ).PostCount > 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|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   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__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToQueryString(IQueryable source)
   at Program.<Main>$(String[] args) in C:\personal-projs\EFFilterAfterProjection\Program.cs:line 40

Workaround is to project to an anonymous type, perform any additional filtering/sorting then project to record type.

Might be related in some form to #11457

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer

[roji]

Records can have arbitrary constructors, just like normal C# classes/structs; that means EF cannot translate record instantiation to SQL, so composing LINQ operators (such as filtering) after projecting to a record cannot be translated either.

Basically there's no difference here between records and non-record types, which EF doesn't support projecting to (before composing additional operators).

[stevendarby]

Perhaps worth clarifying that EF does support projecting to both class and record types but only using initialisation syntax and not constructor syntax. For example, both queries below work, but not if you added constructors to the class and record types and used those instead.

using Microsoft.EntityFrameworkCore;

{
    using (MyDbContext context = new())
    {
        context.Database.EnsureDeleted();
        context.Database.EnsureCreated();

        context.Add(new Person { Name = "Bob" });
        context.SaveChanges();
    }

    using (MyDbContext context = new())
    {
        var personClassDtos = context.People
            .Select(x => new PersonClassDto { Name = x.Name })
            .Where(x => x.Name == "Bob")
            .ToList();

        var personRecordDtos = context.People
            .Select(x => new PersonRecordDto { Name = x.Name })
            .Where(x => x.Name == "Bob")
            .ToList();
    }
}

public class MyDbContext : DbContext
{
    public DbSet<Person> People { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information)
            .UseSqlServer("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Projection;Integrated Security=SSPI");    
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
}

public class PersonClassDto
{
    public string Name { get; set; } = null!;
}

public record PersonRecordDto
{
    public string Name { get; set; } = null!;
}

[roji]

Thanks @stevendarby, I indeed missed that distinction in my comment.

[aradalvand]

Records can have arbitrary constructors, just like normal C# classes/structs; that means EF cannot translate record instantiation to SQL

I'm almost certainly missing something but I'm curious why that is. Isn't the constructor initialization just yet another expression EF could just inspect? What's preventing this from being possible?

[stevendarby]

The code inside the constructor is opaque to EF - it doesn’t know what properties are set by the arguments etc.

[aradalvand]

Yes, but it could retrieve the data and then create the object/record instances on the client side by actually calling the constructor and passing it the retrieved values.

What makes this approach unfeasible?

[stevendarby]

If you want to do it in the client you can call ToList/AsEnumerable etc. We were talking about projecting and doing further filtering etc. on the server.

[aradalvand]

Ah yeah, now I get what I was missing. This wouldn't allow further filtering and so on, right.
Thanks.