InvalidOperationException: 'Set operation can't be applied on entity 'Customer' because temporal operations on both arguments don't match.'

[qsdfplkj]

I've a temporal table: Customers

I would like to get the delta between t1 and t2 using the TemporalAsOf . (I need new or modified items created since t1).

context.Customers.TemporalAsOf(t2).Except(context.Customers.TemporalAsOf(t1)).ToList();

Which I would expect to be translated to something like:

SELECT [c].[Id], [c].[Name], [c].[PeriodStart], [c].[PeriodEnd]
    FROM [Customers] FOR SYSTEM_TIME AS OF 't2' AS [c]
EXCEPT
SELECT [c].[Id], [c].[Name], [c].[PeriodStart], [c].[PeriodEnd]
    FROM [Customers] FOR SYSTEM_TIME AS OF 't1' AS [c]

But I get the following exception:

System.InvalidOperationException: 'Set operation can't be applied on entity 'Customer' because temporal operations on both arguments don't match.'

Stacktrace:

System.InvalidOperationException: 'Set operation can't be applied on entity 'Customer' because temporal operations on both arguments don't match.'
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerNavigationExpansionExtensibilityHelper.AreQueryRootsCompatible(QueryRootExpression first, QueryRootExpression second)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ValidateExpressionCompatibility(Expression outer, Expression inner)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ProcessSetOperation(NavigationExpansionExpression outerSource, MethodInfo genericMethod, NavigationExpansionExpression innerSource)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.Expand(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryTranslationPreprocessor.Process(Expression query)
   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.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Program.<>c.<<Main>$>b__0_11(DateTime fromUtc, DateTime toUtc, OrdersContext context) in C:\wvltemp\TemporalTables\TemporalTables\Program.cs:line 82
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.Invoke(HttpContext httpContext)

EF Core version: 7.0.0-preview.7.22376.2 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .net 7.0 Operating system: Windows 10 IDE: (Visual Studio 2022 Version 17.4.0 Preview 1.0

[ajcvickers]

Note from triage: this could potentially be done in cases where there are no Includes, or for no-tracking queries with independent Includes.

[qsdfplkj]

I was thinking is there any performance difference between

context.Customers.TemporalAsOf(utcPointInTime).ToList()

vs.

context.Customers.TemporalAll().Where(x => EF.Property<DateTime>(x, "PeriodStart") <= utcPointInTime && utcPointInTime < EF.Property<DateTime>(x, "PeriodEnd")).ToList();

as both should return the same records.

[ajcvickers]

@qsdfplkj Intuitively, I would expect the TemporalAsOf version to be be faster, but the only way to tell for sure is to measure it.

[qsdfplkj]

I did some testing. The query plans look the same. I did some timing with SET STATISTICS TIME ON but it's not really conclusive.

One thing I did notice is that only the history has an index for the periodstart and periodend which shows up as a index scan.

The measurement query:

`update statistics [Orders].[dbo].[Customers] update statistics [Orders].[dbo].[CustomersHistory] GO SET STATISTICS TIME ON GO

SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME ALL AS C WHERE C.PERIODSTART <= '2022-09-15 14:24:57.7212388' AND '2022-09-15 14:24:57.7212388' < C.PERIODEND

SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME AS OF '2022-09-15 14:24:57.7212388' AS C

SET STATISTICS TIME OFF GO`

The queryplan:

No index on customers:

For my original question (The delta between two temporalAsOf():

`SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME ALL AS C WHERE C.PERIODSTART <= '2022-09-15 14:24:57.7212388' AND '2022-09-15 14:24:57.7212388' < C.PERIODEND EXCEPT SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME ALL AS C WHERE C.PERIODSTART <= '2022-09-15 14:23:57.7212388' AND '2022-09-15 14:23:57.7212388' < C.PERIODEND

SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME AS OF '2022-09-15 14:24:57.7212388' AS C EXCEPT SELECT C.Id, C.Name, C.PeriodStart, C.PeriodEnd FROM [Orders].[dbo].[Customers] FOR SYSTEM_TIME AS OF '2022-09-15 14:23:57.7212388' AS C ` This gives the queryplan:

My conclussion for now is that I think if I just use TemporalAll() to calculate the delta I get the same query plan (and performance) but I'm still worried about the index scan. Should I add it myself or is this something that SQL server or EF can/should handle?