Using SQL Temporal Tables with Entity Framework Core

SQL Server Temporal Tables is a new feature introduced with SQL Server 2016 which brings a built-in support for handling historical data. Temporal Tables holds the information about each single row for any point in time. Whenever row was inserted, updated or deleted the track of this operation is persisted in history table and can be later on retreivied by providing a time span we are interested on. More on Temporal Tables can be found on this quite extensive documentation, which I recommend as a starting point in this topic. Temporal Tables are not yet natively supported by Entity Framework, however there is open request for this feature, which you can follow here. In this post I will present you some workaround based on raw sql queries and some migration modification which makes the temporal table support quite usefull.

When the table is made a temporal then the new history table is created and all historical rows are persisted in this history table together with two date time fields. Those fields says when the row started to be actual (current) and when it become outdated. But lets start from the begining.

Assume we have to simple POCO classes in our EF context. A Product class

public class Product
{
    [Key]
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    public virtual List<ProductDetail> Details { get; set; } = new List<ProductDetail>();
}

and a ProductDetail class

public class ProductDetail
{
    [Key]
    public int Id { get; set; }

    [Required]
    public virtual Product Product { get; set; }

    [Required]
    public string Name { get; set; }

    public string Value { get; set; }
}

Now assuming we have a default DbContext defined and default initial migration for the database, which creates an empty database schema, we want to make both tables Products and ProductDetails a temporal tables so all changes made to both tables will be tracked and we can later retreive the history of all changes. According to documentation we must alter each table and add required datetime2 fields. Basically we have to run following script

ALTER TABLE Products   
   ADD   
      SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN,   
      SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN,
      PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);   
GO   
ALTER TABLE Products SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.Products));

Additionally we want this scripts to be run in EF migration. I will use some extension method for the MigrationBuilder which makes it look better.

public static void AddTemporalTableSupport(this MigrationBuilder builder, 
string tableName, string historyTableSchema)
{
    builder.Sql($@"ALTER TABLE {tableName} ADD 
            SysStartTime datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
            SysEndTime datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
            PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);");
    builder.Sql($@"ALTER TABLE {tableName} 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {historyTableSchema}.{tableName} ));");
}

Now in our initial migration we need to add 3 lines at the bottom of Up method

protected override void Up(MigrationBuilder migrationBuilder)
{
    //default table creation code here...

    migrationBuilder.Sql("CREATE SCHEMA History");
    migrationBuilder.AddTemporalTableSupport("Products", "History");
    migrationBuilder.AddTemporalTableSupport("ProductDetails", "History");
}

As you can see I use a separate schema for the history tables. Now when we run our migration and check the table Products we can notice the extra date time columns added to both actual temporal table and corresponding history table.

Important note: Our model uses ON DELETE CASCADE which was not supported for Temporal Table feature  in Sql Server 2016 and introduced in SQL Server 2017 starting from CTP 2.0. For more information read this.

Now let’s add some data...

var p1 = new Product { Name = "Product 1" };
p1.Details = new List
{
    new ProductDetail{ Name = "Detail 1", Value = "Value 1" }
};
ctx.Products.Add(p1);
ctx.SaveChanges();

...and then modify it after a while.

var p1 = ctx.Products.Find(1);
p1.Name = "Product 1 modified";
var d1 = ctx.ProductDetails.Find(1);
d1.Value = "Value 1 modified";
ctx.SaveChanges();

When we check the history table for both temporal tables we should see the old rows from before the modification and the time on when those rows were valid. The current values has a “modified” word added. The one in history table don’t. So lets see how we can retreive the old entries with EF context.

According to the temporal table documentation we have to query database with a special AS OF clause. There is no native support for such clause in EF but we can make use of the FromSql method

var date = DateTime.Parse("2018-08-28 16:30:00");
var old = ctx.Products
    .AsNoTracking()
    .FromSql($"SELECT * FROM dbo.Products FOR SYSTEM_TIME AS OF {{0}}", date.ToUniversalTime())
    .ToList();

which is able to materialize our entities and moreover returns IQueryable and allows the result to be filtered by composing a more complex sql queries executed on the server. Note that here we are using AsNoTracking()  method to avoid the database context to keep the historical entities in memory as that would cause unpredicted results when we later on operate with current data on same context.

Ther is only one downside with this approach. As you may notice we are not getting the related ProductDetails. We would like to have get them from the history table as well so the state of the details is from the same point in time as the product we receive. Since this is not supported in EF we have to query all related entities separately from the history table of ProductDetails.

foreach (var p in old)
{
    p.Details = ctx.ProductDetails
        .FromSql($"SELECT * FROM dbo.ProductDetails FOR SYSTEM_TIME AS OF {{0}}", date.ToUniversalTime())
        .AsNoTracking()
        .Where(x => x.Product == p)
        .ToList();
}

Furthermore we could add some extension method so instead of calling this

.FromSql($"SELECT * FROM dbo.ProductDetails FOR SYSTEM_TIME AS OF {{0}}", date.ToUniversalTime())

we could use this

.ForSysTime(date)

but that is something you can find in the source code for this solution.