pinnacletechnology / efcore.timetraveler

Allow full-featured Entity Framework Core queries against SQL Server Temporal Tables

EFCore.TimeTraveler

Update 3/5/2020

Checkout the awesome Adam-Langley/efcore-temporal-query project that generates time traveling queries without the use of interception!

Build
NuGet

Allow full-featured Entity Framework Core queries against SQL Server Temporal Tables.

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

Complicated LINQ Query

    return await context.Apples
        .Include(apple => apple.Worms)
        .ThenInclude(worm => worm.Weapons)
        .Include(apple => apple.Worms)
        .ThenInclude(worm => worm.FriendshipsA)
        .ThenInclude(friendship => friendship.WormB)
        .ThenInclude(worm => worm.Weapons)
        .Include(apple => apple.Worms)
        .ThenInclude(worm => worm.FriendshipsB)
        .ThenInclude(friendship => friendship.WormA)
        .ThenInclude(worm => worm.Weapons)
        .Where(a => a.Id == appleId)
        .AsNoTracking()
        .SingleAsync();

SQL Produced By EF Core 3.1

      SELECT [t].[Id], [t].[FruitStatus], [t2].[Id], [t2].[AppleId], [t2].[Name], [t2].[Id0], [t2].[Name0], [t2].[WormId], [t2].[Id1], [t2].[WormAId], [t2].[WormBId], [t2].[Id00], [t2].[AppleId0], [t2].[Name1], [t2].[Id10], [t2].[Name00], [t2].[WormId0], [t2].[Id2], [t2].[WormAId0], [t2].[WormBId0], [t2].[Id01], [t2].[AppleId1], [t2].[Name2], [t2].[Id11], [t2].[Name01], [t2].[WormId1]
      FROM (
          SELECT TOP(2) [a].[Id], [a].[FruitStatus]
          FROM [Apple] AS [a]
          WHERE [a].[Id] = @__appleId_0
      ) AS [t]
      LEFT JOIN (
          SELECT [w].[Id], [w].[AppleId], [w].[Name], [w0].[Id] AS [Id0], [w0].[Name] AS [Name0], [w0].[WormId], [t0].[Id] AS [Id1], [t0].[WormAId], [t0].[WormBId], [t0].[Id0] AS [Id00], [t0].[AppleId] AS [AppleId0], [t0].[Name] AS [Name1], [t0].[Id1] AS [Id10], [t0].[Name0] AS [Name00], [t0].[WormId] AS [WormId0], [t1].[Id] AS [Id2], [t1].[WormAId] AS [WormAId0], [t1].[WormBId] AS [WormBId0], [t1].[Id0] AS [Id01], [t1].[AppleId] AS [AppleId1], [t1].[Name] AS [Name2], [t1].[Id1] AS [Id11], [t1].[Name0] AS [Name01], [t1].[WormId] AS [WormId1]
          FROM [Worm] AS [w]
          LEFT JOIN [WormWeapon] AS [w0] ON [w].[Id] = [w0].[WormId]
          LEFT JOIN (
              SELECT [w1].[Id], [w1].[WormAId], [w1].[WormBId], [w2].[Id] AS [Id0], [w2].[AppleId], [w2].[Name], [w3].[Id] AS [Id1], [w3].[Name] AS [Name0], [w3].[WormId]
              FROM [WormFriendship] AS [w1]
              INNER JOIN [Worm] AS [w2] ON [w1].[WormBId] = [w2].[Id]
              LEFT JOIN [WormWeapon] AS [w3] ON [w2].[Id] = [w3].[WormId]
          ) AS [t0] ON [w].[Id] = [t0].[WormAId]
          LEFT JOIN (
              SELECT [w4].[Id], [w4].[WormAId], [w4].[WormBId], [w5].[Id] AS [Id0], [w5].[AppleId], [w5].[Name], [w6].[Id] AS [Id1], [w6].[Name] AS [Name0], [w6].[WormId]
              FROM [WormFriendship] AS [w4]
              INNER JOIN [Worm] AS [w5] ON [w4].[WormAId] = [w5].[Id]
              LEFT JOIN [WormWeapon] AS [w6] ON [w5].[Id] = [w6].[WormId]
          ) AS [t1] ON [w].[Id] = [t1].[WormBId]
      ) AS [t2] ON [t].[Id] = [t2].[AppleId]
      ORDER BY [t].[Id], [t2].[Id], [t2].[Id0], [t2].[Id1], [t2].[Id00], [t2].[Id10], [t2].[Id2], [t2].[Id01], [t2].[Id11]

SQL Produced By EF Core 3.1 With EFCore.TimeTraveler

      SELECT [t].[Id], [t].[FruitStatus], [t2].[Id], [t2].[AppleId], [t2].[Name], [t2].[Id0], [t2].[Name0], [t2].[WormId], [t2].[Id1], [t2].[WormAId], [t2].[WormBId], [t2].[Id00], [t2].[AppleId0], [t2].[Name1], [t2].[Id10], [t2].[Name00], [t2].[WormId0], [t2].[Id2], [t2].[WormAId0], [t2].[WormBId0], [t2].[Id01], [t2].[AppleId1], [t2].[Name2], [t2].[Id11], [t2].[Name01], [t2].[WormId1]
      FROM (
          SELECT TOP(2) [a].[Id], [a].[FruitStatus]
          FROM [Apple] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [a]
          WHERE [a].[Id] = @__appleId_0
      ) AS [t]
      LEFT JOIN (
          SELECT [w].[Id], [w].[AppleId], [w].[Name], [w0].[Id] AS [Id0], [w0].[Name] AS [Name0], [w0].[WormId], [t0].[Id] AS [Id1], [t0].[WormAId], [t0].[WormBId], [t0].[Id0] AS [Id00], [t0].[AppleId] AS [AppleId0], [t0].[Name] AS [Name1], [t0].[Id1] AS [Id10], [t0].[Name0] AS [Name00], [t0].[WormId] AS [WormId0], [t1].[Id] AS [Id2], [t1].[WormAId] AS [WormAId0], [t1].[WormBId] AS [WormBId0], [t1].[Id0] AS [Id01], [t1].[AppleId] AS [AppleId1], [t1].[Name] AS [Name2], [t1].[Id1] AS [Id11], [t1].[Name0] AS [Name01], [t1].[WormId] AS [WormId1]
          FROM [Worm] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w]
          LEFT JOIN [WormWeapon] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w0] ON [w].[Id] = [w0].[WormId]
          LEFT JOIN (
              SELECT [w1].[Id], [w1].[WormAId], [w1].[WormBId], [w2].[Id] AS [Id0], [w2].[AppleId], [w2].[Name], [w3].[Id] AS [Id1], [w3].[Name] AS [Name0], [w3].[WormId]
              FROM [WormFriendship] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w1]
              INNER JOIN [Worm] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w2] ON [w1].[WormBId] = [w2].[Id]
              LEFT JOIN [WormWeapon] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w3] ON [w2].[Id] = [w3].[WormId]
          ) AS [t0] ON [w].[Id] = [t0].[WormAId]
          LEFT JOIN (
              SELECT [w4].[Id], [w4].[WormAId], [w4].[WormBId], [w5].[Id] AS [Id0], [w5].[AppleId], [w5].[Name], [w6].[Id] AS [Id1], [w6].[Name] AS [Name0], [w6].[WormId]
              FROM [WormFriendship] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w4]
              INNER JOIN [Worm] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w5] ON [w4].[WormAId] = [w5].[Id]
              LEFT JOIN [WormWeapon] FOR SYSTEM_TIME AS OF @TimeTravelDate AS [w6] ON [w5].[Id] = [w6].[WormId]
          ) AS [t1] ON [w].[Id] = [t1].[WormBId]
      ) AS [t2] ON [t].[Id] = [t2].[AppleId]
      ORDER BY [t].[Id], [t2].[Id], [t2].[Id0], [t2].[Id1], [t2].[Id00], [t2].[Id10], [t2].[Id2], [t2].[Id01], [t2].[Id11]

efcore.timetraveler's Issues

Error on generate Migration

Hi
Firstly Thank you for this Project

I receive this error on creating migration, have you any Idea ?
The current CSharpHelper cannot scaffold literals of type 'EFCore.TimeTraveler.TemporalTables'. Configure your services to use one that can.

Thank you
Best regards

Migration error: The current CSharpHelper cannot scaffold literals of type 'EFCore.TimeTraveler.TemporalTables'.

As per instructions in the main README.md, I tried to enable temporal querying on one entity:

modelBuilder.Entity<InterpolationVariable>().EnableTemporalQuery();

I moved it to the end of OnModelCreating() as per comments in EFCore.TimeTraveler/Extensions.cs

Note that all entities have temporal tables enabled by a later migration. The problem occurs in the initial migration. The method used is shown in https://www.eidias.com/blog/2018/8/29/using-sql-temporal-tables-with-entity-framework-core , which probably does not matter since it has not yet been invoked by the migration runner.

Here is the migration command and its stack dump (Entity Framework Core .NET Command-line Tools 3.1.2):

% dotnet ef migrations add initial -o ./Migrations
Build started...
Build succeeded.
System.InvalidOperationException: The current CSharpHelper cannot scaffold literals of type 'EFCore.TimeTraveler.TemporalTables'. Configure your services to use one that can.
   at Microsoft.EntityFrameworkCore.Design.Internal.CSharpHelper.UnknownLiteral(Object value)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpSnapshotGenerator.GenerateAnnotation(IAnnotation annotation, IndentedStringBuilder stringBuilder)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpSnapshotGenerator.GenerateAnnotations(IReadOnlyList`1 annotations, IndentedStringBuilder stringBuilder)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpSnapshotGenerator.Generate(String builderName, IModel model, IndentedStringBuilder stringBuilder)
   at Microsoft.EntityFrameworkCore.Migrations.Design.CSharpMigrationsGenerator.GenerateMetadata(String migrationNamespace, Type contextType, String migrationName, String migrationId, IModel targetModel)
   at Microsoft.EntityFrameworkCore.Migrations.Design.MigrationsScaffolder.ScaffoldMigration(String migrationName, String rootNamespace, String subNamespace, String language)
   at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.AddMigration(String name, String outputDir, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigrationImpl(String name, String outputDir, String contextType)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.AddMigration.<>c__DisplayClass0_0.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
The current CSharpHelper cannot scaffold literals of type 'EFCore.TimeTraveler.TemporalTables'. Configure your services to use one that can.

Allow Usage of Change Tracking To Enable Caching and Attaching of Related Entities From Multiuple Queries

Let me know if you think something like this would be useful.

Maybe something like this? Would likely blow up memory usage when many time values used?

public class TimeTravelCacheKeyFactory : IModelCacheKeyFactory
{
    public object Create(DbContext context)
    {
        if (TemporalQuery.TargetDateTime != null)
        {
            return (context.GetType(), TemporalQuery.TargetDateTime);
        }
        return context.GetType();
    }
}

https://docs.microsoft.com/en-us/ef/core/modeling/dynamic-model#imodelcachekeyfactory

Automatically Apply AsNoTracking() When Running Within a TemporalQuery.AsOf()

SQL Server Temporal History is immutable, so there is no need for change tracking. Because we are intercepting to switch between historical and current, if we leave off the AsNoTracking(), then the change tracker can get confused and cache historical data as current state data.

Unable to scaffold

I'm linked against .net Core and Ef Core 3.1.1, and I get this when adding a migration that includes temporal query:

The current CSharpHelper cannot scaffold literals of type 'EFCore.TimeTraveler.TemporalTables'. Configure your services to use one that can.

I dont see any code in Github which would generate the ALTER TABLE statements, so I'm curious - where is all the code that performs the scaffolding?

Determine If String Replacements Can Be Cached

If we have hundreds of temporal tables, that means we are calling command.CommandText = command.CommandText.Replace(table, $"{table} FOR SYSTEM_TIME AS OF @TimeTravelDate"); hundreds of times per SQL executed. This is a lot of allocations and will degrade as the number of temporal tables increases.

In the [life of a query](The life of a query) in EF Core,

The result is cached so that this processing does not need to be done every time the query is executed.

We need to figure out some way to get our changes for temporal tables inside that pipeline so that the FOR SYSTEM_TIME AS OF can be included in the cached SQL. Of course, this means there has to be 2 different queries cached, temporal and non-temporal. There does not seem to be interception that occurs before the SQL hits the cache, probably for good reason. Looking for options...