efcore: "The LINQ expression could not be translated" with in-memory provider
The following query fails to translate using the in-memory provider
return await _dbContext.Affaires
.Include(a => a.Tiers).ThenInclude(t => t.Tiers)
.Include(a => a.Nature)
.SingleAsync(a => a.Id == affaire.Id, cancellationToken);
I get the following exception:
The LINQ expression 'DbSet<Affaire>()
.Where(a => EF.Property<TenantId>((object)a, "TenantId") == 3921751e-f1af-4fa3-b3cf-1605b898b928)
.Where(a => a.Id == __affaire_Id_0)
.Join(
inner: DbSet<NatureAffaire>(),
outerKeySelector: a => EF.Property<NatureAffaireId>(a, "NatureId"),
innerKeySelector: n => EF.Property<NatureAffaireId>(n, "Id"),
resultSelector: (o, i) => new TransparentIdentifier<Affaire, NatureAffaire>(
Outer = o,
Inner = i
))' 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.
Stack trace:
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.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.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 Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.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 Microsoft.EntityFrameworkCore.InMemory.Query.Internal.InMemoryQueryableMethodTranslatingExpressionVisitor.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__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.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, LambdaExpression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.SingleAsync[TSource](IQueryable`1 source, Expression`1 predicate, CancellationToken cancellationToken)
(redacted)
--- End of stack trace from previous location ---
at Xunit.Sdk.ExecutionTimer.AggregateAsync(Func`1 asyncAction) in C:\Dev\xunit\xunit\src\xunit.execution\Sdk\Frameworks\ExecutionTimer.cs:line 48
at Xunit.Sdk.ExceptionAggregator.RunAsync(Func`1 code) in C:\Dev\xunit\xunit\src\xunit.core\Sdk\ExceptionAggregator.cs:line 90
I’m not sure what it should be “translated” to (does the in-memory provider use its own query language?), but it’s a pretty basic query, and it’s failing miserably.
The same query works fine with the SQL Server provider.
The same query without the Includes works with the in-memory provider.
It’s worth noting that there’s something unusual in my DbContext: I use strongly-typed ids. In other words, for each entity Foo, there’s a FooId record that wraps a GUID. I suspect it’s what causing the issue. Unless it’s because the in-memory provider doesn’t support Includes, but I’d be very surprised if it didn’t.
EF Core version: 5.0.3 Database provider: in-memory Target framework: .NET 5.0 Operating system: Windows 10 IDE: JetBrains Rider
About this issue
- Original URL
- State: closed
- Created 6 years ago
- Comments: 15 (5 by maintainers)
Yeah. At this point, the only reliable approach is to test against the real DB engine. You can easily spin up a SQL Server container using TestContainers.
@NickStrupat we do recommend against using the in-memory provider. See the Choosing a testing strategy page, and specifically this section.