Query root of type 'TemporalAsOfQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported.

[keyuxuan]

Ask a question

Recently we upgrade from EF6 to EF7, some unit tests start to fail. We are using SQLite as database provider for those unit tests. All those failed tests need to access one table called SignatureValidation in DB which could be a temporal table, but we have specified in DbContext.cs file that only to create that table as temporal if database is sql server, like this:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
 {
     if (this.Database.IsSqlServer())
     {
         modelBuilder.Entity<SignatureValidation>()
             .ToTable("SignatureValidation", b => b.IsTemporal());

     }
}

When tests are running, tests need to run/go through this code block, and I found out .TemporalAsOf(dateValue) statement is the one causing the problem. (if i remove .TemporalAsOf(dateValue), those tests will not fail)

  var validSignatureIds =
                    await dbContext.SignatureValidations
                                    .TemporalAsOf(dateValue)
                                    .Where(x => x.Result == Database.Enumerations.SignatureValidationResult.Valid)
                                    .Select(x => x.SignatureId)
                                    .ToListAsync();

Curious do you have any insights what is happening here and how to fix it so that tests wont fail? Those failing tests used to work fine before upgrading EF version.

Include stack traces

    ],
    ""extensions"": {
      ""message"": ""Query root of type 'TemporalAsOfQueryRootExpression' wasn't handled by provider code. This issue happens when using a provider specific method on a different provider where it is not supported."",
      ""stackTrace"": ""   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitExtension(Expression extensionExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitExtension(Expression extensionExpression)\r\n   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)\r\n   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)\r\n   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)\r\n   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)\r\n   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)\r\n   at Upkeep.GraphQL.Collections.MetricsResolvers.GetTotalSectionsWithCurrentOwnershipAsync(Nullable`1 date, UpkeepDbContext dbContext, Nullable`1 declaredBy, Nullable`1 withOwner, Nullable`1 withValidOwner, CancellationToken cancellationToken) in C:\\Users\\keyuxuan\\Music\\Upkeep\\Upkeep\\Shared\\Upkeep.GraphQL\\Upkeep.GraphQL\\Metrics\\MetricsResolvers.cs:line 214\r\n   at Upkeep.GraphQL.Collections.MetricsResolvers.GetTotalSectionsWithDeclarationsAsync(Report report, Nullable`1 date, FilterSectionsWithDeclarationsInput filter, UpkeepDbContext dbContext, CancellationToken cancellationToken) in C:\\Users\\keyuxuan\\Music\\Upkeep\\Upkeep\\Shared\\Upkeep.GraphQL\\Upkeep.GraphQL\\Metrics\\MetricsResolvers.cs:line 135\r\n   at HotChocolate.Resolvers.Expressions.ExpressionHelper.AwaitTaskHelper[T](Task`1 task)\r\n   at HotChocolate.Types.Helpers.FieldMiddlewareCompiler.<>c__DisplayClass9_0.<<CreateResolverMiddleware>b__0>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Types.EntityFrameworkObjectFieldDescriptorExtensions.<>c__DisplayClass2_1`1.<<UseDbContext>b__4>d.MoveNext()\r\n--- End of stack trace from previous location ---\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.ExecuteResolverPipelineAsync(CancellationToken cancellationToken)\r\n   at HotChocolate.Execution.Processing.Tasks.ResolverTask.TryExecuteAsync(CancellationToken cancellationToken)""
    }

Include provider and version information

EF Core version: EF7 Database provider: SQLite Target framework: NET 6.0 Operating system: Windows IDE: Visual Studio 2022 17.7.7

[roji]

It seems like you're trying to use the SQL Server temporal table support with the SQLite provider - that isn't supported.

[keyuxuan]

yeah we understand SQLite does not support temporal table, but before and after our EF version upgrade, we always use SQLite as our unit test database provider, and we always have to call this code block

 var validSignatureIds =
                    await dbContext.SignatureValidations
                                    .TemporalAsOf(dateValue)
                                    .Where(x => x.Result == Database.Enumerations.SignatureValidationResult.Valid)
                                    .Select(x => x.SignatureId)
                                    .ToListAsync();

why is this issue (test failing) only happening after upgrading to ef core 7?

[roji]

This really should not have worked with SQLite, at any point - please double-check that the test was actually running with SQLite, etc.