Using IQueryable with record type - InvalidOperationException

[LeighTheKiwi]

I am getting an InvalidOperationException when using a projection into a record type (.Net 7). The query works fine if the where clause is chained. It also works if where clause is not chained and the projection is not into a record (class, anonymous type both ok).

public record TestDto(
    int CellLocationId, 
    string AreaCode
);

This works - chained with record:

var query = _context.myTable.Select(s => new TestDto(s.CellLocationId, s.AreaCode)).Where(x => x.AreaCode == areaCode);

This works - unchained with anonymous:

var query = _context.myTable.Select(s => new {s.CellLocationId, s.AreaCode});
query = query.Where(x => x.AreaCode == areaCode);

This fails - unchained with record:

var query = _context.myTable.Select(s => new TestDto(s.CellLocationId, s.AreaCode));
query = query.Where(x => x.AreaCode == areaCode);

System.InvalidOperationException: The LINQ expression 'DbSet<myTable>()
    .Where(v => new TestDto(
        v.CellLocationId, 
        v.AreaCode
    ).AreaCode == __areaCode_0)' 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 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)
   at quacker.api.Controllers.CellLocationController.Get(String domainCode, String areaCode, String cellCode, String accessPointDescription, String cellCodes, Nullable`1 modificationState, Nullable`1 latitude, Nullable`1 longitude, String location, String city, Nullable`1 activeFrom, Nullable`1 activeTo, String networkCode, String technology) in C:\Projects\quacker.api\quacker.api\Controllers\CellLocationController.cs:line 233
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()

EF Core version: 7
LINQ
Database provider:Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 10 Enterprise
IDE: Visual Studio 2022 17.4.0

[roji]

It seems like you're trying to project to some custom type that's unknown to EF (TestDto), and then compose a filter over that; this isn't supported by EF, since it can't be translated to SQL for evaluation in the database (see Client vs. Server Evaluation).

Your first code sample (chained with record) shouldn't work either:

var query = _context.myTable.Select(s => new TestDto(s.CellLocationId, s.AreaCode)).Where(x => x.AreaCode == areaCode);

If you're seeing something different, please submit a full, runnable code sample so that we can see exactly what you're doing.

[LeighTheKiwi]

This approach is a Linq feature and is supported as per DTOs (includes a sample project).
Using a custom class DTO works fine - using a custom record DTO works if where clause is chained, does not work if where clause is added after initial IQueryable declaration.

[stevendarby]

Duplicate of #27281?

(EF definitely supports projecting to a class using member initialisation and filtering on that. With a constructor, maybe not.)

[roji]

@LeighTheKiwi can you please confirm whether your first code sample (chained with record) works when used with EF? You indicate that it does, although it shouldn't.

This approach is a Linq feature and is supported as per DTOs

That's a very general statement. EF generally supports projecting out to DTOs just fine; what it doesn't support is projecting out to unknown types and then attempting to compose additional LINQ operators over them (e.g. Where). The logic here is that EF must be able to translate your LINQ query to SQL, except for the last projection, which can be evaluated on the client (not translated to SQL) without affecting performance. If you compose a Where after something which EF cannot translate to EF (e.g. instantiation of an unknown type), you're forcing that Where to evaluate at the client, which brings all the data from the database and can be very bad for performance. Again, to understand all this read Client vs. Server Evaluation,

One exception to this is anonymous types (new { ... }), since there's no unknown code there. You can use these anywhere you want.

[roji]

As pointed out by @stevendarby, EF does support projecting to a class using member initialisation and filtering on that - but not with a constructor, which is what the OP does.

[LeighTheKiwi]

@roji - you are correct, I copied the wrong bit of test code - constructor implementation should have been:

var query = _context.myTable.Where(x => x.AreaCode == areaCode).Select(s => new TestDto(s.CellLocationId, s.AreaCode));

Closed as duplicate.

[roji]

Duplicate of #27281