EFCore.TimeTraveler 0.26.1

Background

EF Core does not natively support querying from the history of temporal tables. You may query a single temporal table using .FromSqlRaw(...) or .FromSqlInterpolated(...). Multiple temporal tables can be queried using the same raw SQL functionality with LINQ Join. Additionally, the EfCoreTemporalTable library provides a nice syntax for this functionality.

However, any related data from Include(...) or navigation properties is not able to be queried from temporal history with EF Core. The problem I am trying to solve is that I have a good amount of LINQ queries and other code written and tested, but now a requirement has come in requiring loading the same set of data at a prior System Time. The Point-in-time analysis (time travel) usage scenario for SQL Server Temporal Tables pretty well described what I am wanting to do.

Prerequisites

• EF Core 3.1 (Supports .NETStandard 2.0)
• SQL Server 2016 or higher or Azure SQL (For Temporal Table Support)

Assumptions

• EF ALWAYS generates SQL with table names surrounded by square brackets.
• EF ALWAYS uses a table alias in generated SQL, so that the table name does not get repeated in the SQL unless joining to the same table.

Restrictions

Since history is immutable, all EF queries within a TemporalQuery.AsOf(targetTime) block must use .AsNoTracking(). This avoids the DBContext getting confused and caching prior state data as the current state of the data. In a future release, the disabling of change tracking for temporal queries may be automatic if I ever figure out the best way to do this. PR's are welcome.

Example Usage

EF Core Mapping

    var appleEntity = modelBuilder.Entity<Apple>()
        .EnableTemporalQuery();

    appleEntity.HasKey(apple => apple.Id);

    var wormEntity = modelBuilder.Entity<Worm>()
        .EnableTemporalQuery();

    wormEntity.HasOne(worm => worm.Apple)
        .WithMany(apple => apple.Worms)
        .HasForeignKey(worm => worm.AppleId);        

EF Core LINQ Query

    var appleCurrentState = await context.Apples
        .Include(apple => apple.Worms)
        .Where(a => a.Id == appleId)
        .AsNoTracking()
        .SingleAsync();   

    appleCurrentState.Worms.Count().Should().Be(3);

    using (TemporalQuery.AsOf(ripeAppleTime))
    {
        var applePriorState = await context.Apples
            .Include(apple => apple.Worms)
            .Where(a => a.Id == appleId)
            .AsNoTracking()
            .SingleAsync();                     
    
        applePriorState.Worms.Count().Should().Be(0);
    }

More Complicated Example

See /tests/EFCore.TimeTravelerTests/EndToEndTest.cs