TemporalAsOf with NoTrackingWithIdentityResolution

[dragos-durlut]

Solution can be found lower down the page.

Problem

In efcore 6.0.1, the TemporalAsOf extension is set to only create queries marked AsNotTracking.

Even the description of the method says it pretty clearly:

Temporal queries are always set as 'NoTracking'.

The problem appears when we want to do TemporalAsOf queries which have more complexity to them such as this:

Context.Set<EntityZero>.TemporalAsOf(DateTime.UtcNow)
    .Include(x => x.EntityOne)
        .ThenInclude(x => x.EntitySeven)
    .Include(x => x.EntityOne)
        .ThenInclude(x => x.EntityEight)
        .ThenInclude(x => x.EntityFive)
    .Include(x => x.EntityNine)
        .ThenInclude(x => x.EntityTen)
    .Include(x => x.EntityFourteen)
        .ThenInclude(x => x.EntitySix)
    .Include(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityEleven)
        .ThenInclude(x => x.EntityTwelve)
    .Include(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntryType)
    .Include(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntityThirteen)
        .ThenInclude(x => x.EntityFifteen)
    .Include(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityOne)
        .ThenInclude(x => x.EntitySeven)
    .Include(x => x.EntitySixteen)
        .ThenInclude(x => x.EntitySeventeen)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwenty)
        .ThenInclude(x => x.EntitySix)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwenty)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntityThirteen)
        .ThenInclude(x => x.EntityFifteen)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwenty)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntryType)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwenty)
        .ThenInclude(x => x.EntityEightteen)
        .ThenInclude(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntityThirteen)
        .ThenInclude(x => x.EntityFifteen)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwentyOne)
        .ThenInclude(x => x.EntitySix)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwentyOne)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntityThirteen)
        .ThenInclude(x => x.EntityFifteen)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwentyOne)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntryType)
    .Include(x => x.EntityTwo)
        .ThenInclude(x => x.EntityTwentyOne)
        .ThenInclude(x => x.EntityEightteen)
        .ThenInclude(x => x.EntityFourteen)
        .ThenInclude(x => x.EntityFour)
        .ThenInclude(x => x.EntityThirteen)
        .ThenInclude(x => x.EntityFifteen)
    .Include(x => x.EntityNineteen);

A mammoth query such as this can easily generate an error such as this:

Error "Cycles are not allowed in no-tracking queries; either use a tracking query or remove the cycle"

First clue how to fix this is here

If you want to do cycles in include then either do a tracking query or from 5.0 onwards you can also use AsNoTrackingWithIdentityResolution to go back to 2.2 behavior which will always work.

Second clue how to fix this is here

In Entity Framework Core 5.0 you can use AsNoTrackingWithIdentityResolution() to not track entities but still perform identity resolution so you won’t have duplicate instances.

Third clue how to fix this is here

When you configure the query to use identity resolution with no tracking, we use a stand-alone change tracker in the background when generating query results so each instance is materialized only once. Since this change tracker is different from the one in the context, the results are not tracked by the context. After the query is enumerated fully, the change tracker goes out of scope and garbage collected as required.

In short use a no-tracking query with identity resolution

Solution

I understand that using a NoTrackingWithIdentityResolution or TrackAll when executing a TemporalAll query can cause some clashes.
I think we can quote @ajcvickers here to give a better explanation:

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 with TemporalAsOf, 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)

Extrapolating from this, I can say that it should be pretty safe that we can use NoTrackingWithIdentityResolution on a TemporalAsOf query. (Not sure what to say about TrackAll though)

In fact I have been using NoTrackingWithIdentityResolution on a TemporalAsOf query for quite some time.

I have created an extension method such as below:

public static class SqlServerDbSetExtensions
{
	using Microsoft.EntityFrameworkCore;
	using Microsoft.EntityFrameworkCore.Query;
	using Microsoft.EntityFrameworkCore.SqlServer.Query.Internal;
	using System;
	using System.Linq;
    /// <summary>
    ///     Applies temporal 'AsOf' operation on the given DbSet, which only returns elements that were present in the database at a given
    ///     point in time.
    /// </summary>
    /// <remarks>
    ///     <para>
    ///         Temporal information is stored in UTC format on the database, so any <see cref="DateTime" /> arguments in local time may lead to
    ///         unexpected results.
    ///     </para>
    ///     <para>
    ///         The default tracking behavior for queries can be controlled by <see cref="Microsoft.EntityFrameworkCore.ChangeTracking.ChangeTracker.QueryTrackingBehavior" />.
    ///     </para>
    ///     <para>
    ///         See <see href="https://aka.ms/efcore-docs-temporal">Using SQL Server temporal tables with EF Core</see>
    ///         for more information and examples.
    ///     </para>
    /// </remarks>
    /// <param name="source">Source DbSet on which the temporal operation is applied.</param>
    /// <param name="utcPointInTime"><see cref="DateTime" /> representing a point in time for which the results should be returned.</param>
    /// <returns>An <see cref="IQueryable" /> representing the entities at a given point in time.</returns>
    public static IQueryable<TEntity> TemporalAsOf<TEntity>(
        this DbSet<TEntity> source,
        DateTime utcPointInTime,
        QueryTrackingBehavior queryTrackingBehavior
        )
        where TEntity : class
    {
#pragma warning disable EF1001 // Internal EF Core API usage.
        var queryableSource = (IQueryable)source;
        var queryRootExpression = (QueryRootExpression)queryableSource.Expression;
        var entityType = queryRootExpression.EntityType;

        var query = queryableSource.Provider.CreateQuery<TEntity>(
            new TemporalAsOfQueryRootExpression(
                queryRootExpression.QueryProvider!,
                entityType,
                utcPointInTime)).AsTracking(queryTrackingBehavior);
        return query;
#pragma warning restore EF1001 // Internal EF Core API usage.
    }
}

A working example of this can be found in this repo here.
The changes that contain the code and usages can be found here

This can be used like this:

var order = context.Orders
	.TemporalAsOf(on, QueryTrackingBehavior.NoTrackingWithIdentityResolution)
	.Include(e => e.Product)
	.Include(e => e.Customer)
	.Single(order =>
		order.Customer.Name == customerName
		&& order.OrderDate > on.Date
		&& order.OrderDate < on.Date.AddDays(1));

The AsTracking(...) method code can be found here. Issue would be of course with the TrackAll option.

I have been using this extension for quite some time now without any side effects.
The TrackAll option would have to be removed but this is still an easy fix.

[dragos-durlut]

I've added the update for Ef Core 8.0.1

    public static IQueryable<TEntity> TemporalAsOf<TEntity>(
        this DbSet<TEntity> source,
        DateTime utcPointInTime,
        QueryTrackingBehavior queryTrackingBehavior
        )
        where TEntity : class
    {
#pragma warning disable EF1001 // Internal EF Core API usage.
        var queryableSource = (IQueryable)source;
        var entityQueryRootExpression = (EntityQueryRootExpression)queryableSource.Expression;
        var entityType = entityQueryRootExpression.EntityType;

        var query = queryableSource.Provider.CreateQuery<TEntity>(
            new TemporalAsOfQueryRootExpression(
                entityQueryRootExpression.QueryProvider!,
                entityType,
                utcPointInTime)).AsTracking(queryTrackingBehavior);
        return query;
#pragma warning restore EF1001 // Internal EF Core API usage.
    }

Implementation can be found here