EF Core 6 Temporal Table

I’ve wrote already a couple of posts regarding SqlServer temporal tables, so this is not an introduction to that feature. Instead I recommend reading Microsoft documentation and EF Core 6 introduction into temporal tables support. You may also want to read my previous posts regarding temporal tables here and here.

Ok, without any further ado, let’s jump in the code.

Assume we have two entity classes: Product and Order like so:

public class Product
{
     public Guid Id { get; set; }
     public string? Name { get; set; }
     public decimal Price { get; set; }
}
 
public class Order
{
     public Guid Id { get; set; }
     public string? Customer { get; set; }
     public Product? Product { get; set; }
     public int Quantity { get; set; }
}

and we want to track both of them in SqlServer temporal tables.

With EF Core 6 its as easy as adding one extra line for each entity configuration. This is done in database context class, which in this case looks like follows:

public class ShopContext : DbContext
{
     public DbSet<Order>? Products { get; set; }
     public DbSet<Order>? Orders { get; set; }
     public ShopContext() { }
     protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
     => optionsBuilder.UseSqlServer("Data Source=(local);Initial Catalog=test;Integrated Security=True");
     
     protected override void OnModelCreating(ModelBuilder modelBuilder)
     {
         modelBuilder.Entity<Order>(b =>
         {
             b.ToTable(ot => ot.IsTemporal());
         });
         modelBuilder.Entity<Product>(b =>
         {
             b.ToTable(ot => ot.IsTemporal());
         });
     }
}

Having that in place lets seed some data…

var db = new ShopContext();
await db.Database.EnsureDeletedAsync();
await db.Database.EnsureCreatedAsync();
var prod = new Product { Name = "WallBox", Price = 999.9m };
var order = new Order { Customer = "Thomass", Product = prod, Quantity = 10 };
db.Add(order);
await db.SaveChangesAsync();
var save1 = DateTime.UtcNow;
...

then let’s wait a while and modify that data…

Thread.Sleep(100);
prod.Price -= 100;
order.Quantity = 11;
order.Customer = "Tchomas";
await db.SaveChangesAsync();
var save2 = DateTime.UtcNow;
...

As you can see we grab the time stamp after each save. It will help us to query historical data.

Now assuming we have the save1 timestamp we want to see how was the state of the order at that time.

var orderHistory = db.Orders!
    .TemporalAsOf(save1)
    .Include(x => x.Product)
    .Where(x => x.Id == order!.Id)
    .Select(x => new
    {
        Order = x,
        PeriodStart = EF.Property<DateTime>(x, "PeriodStart"),
        PeriodEnd = EF.Property<DateTime>(x, "PeriodEnd"),
    }).Single();

TemporalAsOf() method accepts an UTC time stamp and translates to SQL

FOR SYSTEM_TIME AS OF

causing the query retreives data from historical table it was valid at the given point in time. PeriodStart and PeriodEnd are Shadow properties added automatically by EF Core. They are UTC time stamps that construct PERIOD FOR SYSTEM_TIME in temporal table. Here we are using EF helper class to retrive those properties from EF model.

The result of the query above is following:

Order valid from: 2021-11-18T10:08:50.2718848
                          to: 2021-11-18T10:08:50.5096725
            Customer: Thomass
              Quantity: 10     
    Product Name: WallBox
      Product Price: 999,90 zł

As you can see the query returned values that were valid at the moment we saved the save1 time. Moreover, EF is smart enough to detect that Product entity is also mapped to a temporal table. In above query the time stamp filter was applied not only to Orders table but also to Products table, causing we get the version of the Product (it’s Price value specifically), that was valid at given point in time.

It can be clearly seen in the SQL query log:

SELECT TOP(2) [o].[Id], [o].[ChangedOn], [o].[Customer], 
              [o].[PeriodEnd], [o].[PeriodStart], 
              [o].[ProductId], [o].[Quantity], 
              [p].[Id], [p].[Name], [p].[PeriodEnd], 
              [p].[PeriodStart], [p].[Price]
      FROM [Order] FOR SYSTEM_TIME AS OF '2021-11-18T10:08:50.6894182Z' AS [o]  
      LEFT JOIN [Product] 
                FOR SYSTEM_TIME AS OF '2021-11-18T10:08:50.6894182Z' 
                AS [p] ON [o].[ProductId] = [p].[Id]  
      WHERE [o].[Id] = @__order_Id_0

There are many ways we can query temporal data. You can find more details on that on the EF documentation I mentioned above.

Now, lets focus on some specific requirement. Let’s say we need an information on when the entity was recently changed. Obviously, according to temporal table architecture, that information is stored in PeriodStart column. We can, of course, use EF helper class to fetch that column value from the undergoing EF model, but that’s valid only in query context or when we can access the database context ChangeTracker. Unfortunatelly EF Core 6 has some limitations on period columns.

PeriodStart and PeriodEnd fields are forced to be shadow properties, so we cannot have them directly on an entity CLR class. As a side note: their are also defined as hidden columns in Sql Server table. The only thing we can configure in current version is the name of period properties and the name of history table.

Fortunatelly, there is a workaround for that and its called Computed columns. Computed column is basically a sql column that has corresponding property reflected in entity class and it’s value is calculated based on some other columns or SQL expression. Computed column can be virtual or stored. Virtual means the value is computed when fetching data, while stored means the value is evaluated and persisted on every row update.

Let’s define ChangedOn property on Order entity:

public class Order
{
     public Guid Id { get; set; }
     public string? Customer { get; set; }
     public Product? Product { get; set; }
     public int Quantity { get; set; }
     public DateTime ChangedOn { get; }
}

and configure it as computed column to fetch data straight from the PeriodStart column

modelBuilder.Entity<Order>(b =>
{
  b.ToTable(ot => ot.IsTemporal());
  b.Property(x => x.ChangedOn).HasComputedColumnSql("[PeriodStart]");
});

That’s it. Basically it doesn’t matter if we define it as virtual or persisted, as the PeriodStart column is updated on each row update anyway. The only difference is that extra storage space occupied by a copy of datetime2 value.

So we’ve mapped one of the period time stamps to actual entity class propety ChangedOn.

One important note here. Querying by ChangeOn woun’t return historical data, so we cannot do a query like this:

var order = db.Orders!
     .Include(x => x.Product)
     .Where(x => x.Id == order!.Id)
     /*that will not work! */
     .Where(x => x.ChangedOn <= save1)
     .SingleOrDefault();

as it does not fetch data from historical table obviously. Maybe that’s the reason EF does not expose period columns as regular properties

Cheers

Full source code for this post is available below.