Unable to translate set operation when using concise C# records for projection

[poke]

Hey, when trying to concat two results that are projected using concise C# records, I am getting an invalid operation exception with the error message “Unable to translate set operation after client projection has been applied. Consider moving the set operation before the last 'Select' call.”

When not attempting to use a concatenation/union, the the positional record syntax does work, so it’s not that EF Core does not support the concise syntax here.

See the following example using the SQLite provider (but the same issue occurs with other database engines as well):

var options = new DbContextOptionsBuilder<TestContext>().UseSqlite("Data Source=testdb.db").Options;
using var db = new TestContext(options);
db.Database.EnsureCreated();

// this works without any issues
var resultWithProperties = db.Test1
    .Select(t => new ResultTypeProperties
    {
        Type = "Test 1",
        Value = t.Value
    })
    .Concat(db.Test2.Select(t => new ResultTypeProperties
    {
        Type = "Test 2",
        Value = t.Value
    }))
    .ToArray();

// this breaks with the exception “Unable to translate set operation after client projection has been applied.”
var resultWithRecords = db.Test1
    .Select(t => new ResultTypeRecord("Test 1", t.Value))
    .Concat(db.Test2
        .Select(t => new ResultTypeRecord("Test 2", t.Value))
    )
    .ToArray();

// Note that the positional record syntax _is_ supported usually which you
// can see if you comment out the `.Concat()` calls to skip the union.

The types for this are declared as following:

public class TestContext : DbContext
{
    public DbSet<Test1> Test1 { get; set; }
    public DbSet<Test2> Test2 { get; set; }
    public TestContext(DbContextOptions<TestContext> options) : base(options) { }
}

public class Test1
{
    public int Id { get; set; }
    public string Value { get; set; }
}
public class Test2
{
    public int Id { get; set; }
    public string Value { get; set; }
}

public record ResultTypeProperties
{
    public string Type { get; set; }
    public string Value { get; set; }
}
public record ResultTypeRecord(string Type, string Value);

Note the different syntax used to declare the properties on ResultTypeProperties and ResultTypeRecord.

This example currently uses Microsoft.EntityFrameworkCore.Sqlite version 6.0.6.

[ajcvickers]

Note from triage: looks like the expression tree generated here is different and hence the query pipeline needs to be updated to support this.

[Quintinon]

I ran into a different manifestation of what I believe is the same problem using concise-syntax records. Using EFCore 6 with a concise-syntax record in a simple projections (.Select) query, EFCore was able to compile the query correctly, but was retrieving every database column instead of only those used in the projection. This actually ended up being a problem for me because one of the columns I was excluding from the projection was a potentially large varbinary column.

I was able to work around this specific issue by issuing an anonymous object projection first, and then in the same query projecting to my concise-syntax record.

[SpaceOgre]

Not sure if I should post here or create a new issue, but I believe that it might have the same root cause.

1. If I use concise-syntax records like this, then it works:

   return await _dbContext.Set<User>()
   .OrderBy(x => x.SSN)
   .Select(x => new TestRecord(x.Id, x.SSN))
   .ToListAsync(ct);

2. But if I change the order of Select and OrderBy then it does not work:

   return await _dbContext.Set<User>()
   .Select(x => new TestRecord(x.Id, x.SSN))
   .OrderBy(x => x.SSN)
   .ToListAsync(ct);

   I get the following error:

   System.InvalidOperationException: The LINQ expression 'DbSet<User>()
   .OrderBy(u => new TestRecord(
       u.Id,
       u.SSN
   ).SSN)' could not be translated.

   Stacktrace

   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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)

3. If I use property initializers instead, then it also works with no problem:

   return await _dbContext.Set<User>()
   .Select(x => new TestRecord { Id = x.Id, SSN = x.SSN })
   .OrderBy(x => x.SSN)
   .ToListAsync(ct);

Version 1 and 2 generates the same SQL.

SELECT [s].[Id], [s].[SSN]
FROM [Users] AS [s]
ORDER BY [s].[SSN]