Description
I would have not raised this bug, but according to this exchange between @ajcvickers and @iSatishYadav this the possibility of using Include() and ThenInclude() with non-temporal child entities when using the TemporalAsOf query, should have been supported.
More details about the bug and how to reproduce it are found below.
@iSatishYadav The problem with the temporal operators that return multiple instances of the same entity is that the referential constraints of the model are broken. There can be many instances of one entity referring to many instances of another entity. (Note this is different from many instances which are different entities.) This is essentially why SQL Server also removes all constraints from the history tables. So we have no plan to enable Include or other navigation expansions for the temporal operators that return many instances.
Contrast this withTemporalAsOf, which uses a specific point in time. In this case, the model constraints are preserved and all entity associations are in a valid state. This is why Include and other navigation expansions are supported with AsOf.
Originally posted by @ajcvickers in #26704 (comment)
I have managed to reproduce this bug in this forked repository here.
This was the repository @ajcvickers used to showcase the features in efcore 6.
You can see the added code and schema update in this commit here
The query that raises the error is the following:
private static void QueryEverythingTemporalAsOf()
{
using var context = new OrdersContext(log: true);
var query = context.Products.TemporalAsOf(DateTime.UtcNow)
.Include(p => p.Orders).ThenInclude(o => o.Customer)
.Include(p => p.ProductType).ThenInclude(pt => pt.ProductClass);
var list = query.ToList();
}Two new tables have been added: ProductType and ProductClass.
These are basically child tables to the Products table.
These are obviously non-temporal.
This, in theory should never or seldom change. But these are needed in retrieving the entities from the DB.
The error is the following:
System.InvalidOperationException
HResult=0x80131509
Message=Temporal query is trying to use navigation to an entity 'ProductType' which itself doesn't map to temporal table. Either map the entity to temporal table or use join manually to access it.
It says I have 2 options: Either map the entity to temporal table or use join manually to access it. The first is not feasible and the second raises some questions:
- where am I supposed to place the join for this to work ? Will the join automatically populate the resulting entity with
ProductTypeandProductClass? - what if I need a left join ?
- what do I do in case of a really large query that has multiple include statements (something to the tune of 20 Includes) ?
The stack trace is the following:
System.InvalidOperationException
HResult=0x80131509
Message=Temporal query is trying to use navigation to an entity 'ProductType' which itself doesn't map to temporal table. Either map the entity to temporal table or use join manually to access it.
Source=Microsoft.EntityFrameworkCore.SqlServer
StackTrace:
at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerNavigationExpansionExtensibilityHelper.CreateQueryRoot(IEntityType entityType, QueryRootExpression source)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandingExpressionVisitor.ExpandForeignKey(Expression root, EntityReference entityReference, IForeignKey foreignKey, Boolean onDependent, Boolean derivedTypeConversion)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandingExpressionVisitor.ExpandNavigation(Expression root, EntityReference entityReference, INavigation navigation, Boolean derivedTypeConversion)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.ExpandIncludesHelper(Expression root, EntityReference entityReference, INavigationBase previousNavigation)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.ExpandInclude(Expression root, EntityReference entityReference)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.IncludeExpandingExpressionVisitor.VisitExtension(Expression extensionExpression)
at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.ExpandingExpressionVisitor.Expand(Expression expression, Boolean applyIncludes)
at Microsoft.EntityFrameworkCore.Query.Internal.NavigationExpandingExpressionVisitor.PendingSelectorExpandingExpressionVisitor.Visit(Expression expression)
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 Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.IncludableQueryable`2.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at Program.QueryEverythingTemporalAsOf() in D:\Repos\GitHub\TemporalTables\Program.cs:line 182
at Program.Main() in D:\Repos\GitHub\TemporalTables\Program.cs:line 27
After looking at the stack trace, I took a look at the source code of efcore here and searched for where the error message seemed to have originated from.
I can see that it is looking only for temporal entities.
public override QueryRootExpression CreateQueryRoot(IEntityType entityType, QueryRootExpression? source)
{
if (source is TemporalQueryRootExpression)
{
if (!entityType.GetRootType().IsTemporal())
{
throw new InvalidOperationException(
SqlServerStrings.TemporalNavigationExpansionBetweenTemporalAndNonTemporal(entityType.DisplayName()));
}
if (source is TemporalAsOfQueryRootExpression asOf)
{
return source.QueryProvider != null
? new TemporalAsOfQueryRootExpression(source.QueryProvider, entityType, asOf.PointInTime)
: new TemporalAsOfQueryRootExpression(entityType, asOf.PointInTime);
}
throw new InvalidOperationException(SqlServerStrings.TemporalNavigationExpansionOnlySupportedForAsOf("AsOf"));
}
return base.CreateQueryRoot(entityType, source);
}