Entity Framework Core 5 support for SqlServer Temporal Table

By Mirek on (tags: EF, Entity Framework, .Net 5, SQL Server, Temporal Tables, categories: architecture, code)

A time ago I wrote a post about enabling support for SqlServer Temporal tables in EF Core. Then it was EF Core 2.1. Let’s see what has changed during that time and what we can do today with EF Core 5 in terms of Temporal table support.

Assume we have a simple class Person 

public class Person
{
   public int Id { get; set; }
   public string Name { get; set; }
   public DateTime? ValidFromUTC { get; }
   public DateTime? ValidToUTC { get; }
   public override string ToString() => ...
}

As you can see at a first glance, we want to have history change time stamps available as read only properties right in the entity class. To accomplish it we need to instruct EF about couple of things about our Person class. 

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<Person>().HasKey(x => x.Id);

   modelBuilder.Entity<Person>().Property(x => x.ValidFromUTC)
                                .IsRequired()
                                .ValueGeneratedOnAddOrUpdate()
                                .HasDefaultValueSql("SYSUTCDATETIME()");

   modelBuilder.Entity<Person>().Property(x => x.ValidToUTC)
                                .IsRequired()
                                .ValueGeneratedOnAddOrUpdate()
                                .HasDefaultValueSql("CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999')");

   base.OnModelCreating(modelBuilder);
}

Here both ValidFromUTC and ValidToUTC fields are being set up to fulfill Temporal Table requirements

  1. IsRequired() assures the field is never null
  2. ValueGeneratedOnAddOrUpdate() informs Entity Framework to exclude this fields in insert or update operations, but to query them in select operations
  3. HasDefaultValueSql(…) sets the defaults according to what is required for Temporal table
  4. Default sql type for DateTime fields used by EF is datetime2 which is fine with the requirements.

The next step is to actually create the temporal table for our main table (containing Person entity). This is now slightly different to how it was before, because we now don’t need to alter table and create history time stamps column. Instead we just need to call this:

public static string GetEnableTemporalTableSql(string tableName) =>
  $@"ALTER TABLE [dbo].[{tableName}]
     ADD PERIOD FOR SYSTEM_TIME (ValidFromUTC, ValidToUTC)
   GO
     ALTER TABLE [dbo].[{tableName}]
     SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE =  [dbo].[{tableName}History], DATA_CONSISTENCY_CHECK = ON))
   GO";

Statement

ADD PERIOD FOR SYSTEM_TIME

changes the generation of the ValidFromUTC and ValidToUTC columns to AS_ROW_START and AS_ROW_ENDS. It will also perform a data consistency check on existing table.

Having that in place we can now easy query entity history with following query:

var history = context.People
   .FromSqlRaw($"SELECT * FROM dbo.[People] FOR SYSTEM_TIME ALL WHERE Id = {{0}}  ORDER BY ValidFromUTC", 1)
   .AsNoTracking()
   .AsQueryable();

Full sample solution can be found in attachement.

Cheers

Download attachement - 5 KB