Prime your flux capacitor: SQL Server temporal tables in EF Core 6.0

EF Core 6.0 Release Candidate 1 was released to NuGet a few weeks ago. This release is the first of two “go live” release candidates that are supported in production. EF Core 6.0 RC1 runs on Release Candidate 1 for .NET 6.0, which also has a “go live” license.

SQL Server Temporal Tables

The most requested feature for EF Core 6.0 was support for SQL Server temporal tables. As of RC1, temporal table support is now here!

SQL Server temporal tables automatically keep track of all the data ever stored in a table, even after that data has been updated or deleted. This is achieved by creating a parallel “history table” into which timestamped historical data is stored whenever a change is made to the main table. This allows historical data to be queried, such as for auditing, or restored, such as for recovery after accidental mutation or deletion.

EF Core 6.0 supports:

• The creation of temporal tables using EF Core migrations
• Transformation of existing tables into temporal tables, again using migrations
• Querying historical data
• Restoring data from some point in the past

Watch the EF Core Community Standup for Temporal Tables

Sample application

The sample application used in this post can be downloaded from GitHub. It uses a simple EF Core model for customers, products, and orders:

public class Customer
{
    public Guid Id { get; set; }
    public string Name  { get; set; }

    public List<Order> Orders { get; set; }
}

public class Order
{
    public Guid Id { get; set; }
    public DateTime OrderDate { get; set; }

    public Product Product { get; set; }
    public Customer Customer { get; set; }
}

public class Product
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Configuring a temporal table

Entity types are mapped to temporal tables in OnModelCreating using IsTemporal. For example:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder
        .Entity<Customer>()
        .ToTable("Customers", b => b.IsTemporal());

    modelBuilder
        .Entity<Product>()
        .ToTable("Products", b => b.IsTemporal());

    modelBuilder
        .Entity<Order>()
        .ToTable("Orders", b => b.IsTemporal());
}

EF Core migrations will then either create these tables as temporal or, if the tables already exist, they will be converted to temporal tables. For example, migrations will execute the following SQL to convert the existing Products table to a temporal table:

ALTER TABLE [Products] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';
ALTER TABLE [Products] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';
ALTER TABLE [Products] ADD PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
ALTER TABLE [Products] ALTER COLUMN [PeriodStart] ADD HIDDEN
ALTER TABLE [Products] ALTER COLUMN [PeriodEnd] ADD HIDDEN
DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'ALTER TABLE [Products] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + '].[ProductHistory]))')

Notice that the table now contains two hidden datetime2 columns called PeriodStart and PeriodEnd. These “period columns” represent the time range during which the data in the row existed. These columns are mapped to shadow properties in the EF Core model, allowing them to be used in queries as shown later.

⚠️NOTE: The times in these columns are always UTC time generated by SQL Server. UTC times are used for all operations involving temporal tables, such as in the queries shown below.

The final command sets SYSTEM_VERSIONING = ON, which enables temporal storage on this table. This causes SQL Server to create an associated history table called ProductHistory. The names of the period columns and history table can be configured in the EF model, if desired.

Querying historical data

Most of the time, temporal tables are used just like any other table. That is, the period columns and historical data are handled transparently by SQL Server such that the application can ignore them. Entities are added, queried, updated, and deleted in the normal way.

The sample application has been seeded with products and orders that have changed over time. With this sample data, the code to lookup the current price of a product is just the same as it would be without temporal tables. For example:

var product = context.Products.Single(product => product.Name == productName);

Notice that Single can be used here because there is only one product with a given name in the table. However, behind the scenes SQL Server has been keeping track of each change to the price of this product. The LINQ extension method TemporalFromTo can be used to query for this historical data between two dates. For example, the sample application contains a query that retrieves prices of a product between two dates:

var productSnapshots = context.Products
    .TemporalBetween(from, to)
    .OrderBy(product => EF.Property<DateTime>(product, "PeriodStart"))
    .Where(product => product.Name == productName)
    .Select(product =>
        new
        {
            Product = product,
            PeriodStart = EF.Property<DateTime>(product, "PeriodStart"),
            PeriodEnd = EF.Property<DateTime>(product, "PeriodEnd")
        })
    .ToList();

The query projects out the PeriodStart and PeriodEnd values into an anonymous type, along with a snapshot of the entity instance at that time. The EF.Property method is used to get the period values since they are mapped to shadow properties in the entity.

Executing this query on the sample data for the “DeLorean” product returns the following results:

The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d is currently $150000.00.
  Historical prices from 10/5/2021 12:09:11 PM to 10/7/2021 12:09:37 PM:
    The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $2000000.00 from 10/5/2021 12:09:11 PM until 10/5/2021 12:09:16 PM.
    The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $2500000.00 from 10/5/2021 12:09:16 PM until 10/5/2021 12:09:27 PM.
    The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $75000.00 from 10/5/2021 12:09:27 PM until 10/5/2021 12:09:32 PM.
    The 'DeLorean' with PK 49b66f2a-12dc-466a-d703-08d987f8ee3d was $150000.00 from 10/5/2021 12:09:32 PM until 12/31/9999 11:59:59 PM.

Notice that the query returns multiple instances of Product with the same primary key. This is because the database is telling us what this single entity looked like a various times in the past.

The last snapshot return as an end time of “12/31/9999 11:59:59 PM”. This indicates that this row still exists in the database. This is the row that is returned when querying the table in the normal way.

EF Core supports several temporal table query operators:

• TemporalAsOf: Returns rows that were active (current) at the given UTC time. This is a single row from the history table for a given primary key.
• TemporalAll: Returns all rows in the historical data. This is typically many rows from the history table for a given primary key.
• TemporalFromTo: Returns all rows that were active between two given UTC times. This may be many rows from the history table for a given primary key.
• TemporalBetween: The same as TemporalFromTo, except that rows are included that became active on the upper boundary.
• TemporalContainedIn: : Returns all rows that started being active and ended being active between two given UTC times. This may be many rows from the history table for a given primary key.

💡NOTE: See the SQL Server temporal tables documentation for more information on exactly which rows are included for each of these operators.

Finding a specific historical record

Let’s assume for the sample that a customer ordered a DeLorean at some point in the past. However, they have now decided that brushed steel isn’t modern enough and so decides to return it. Customer Service are happy to oblige and run a query to pull up the order:

var order = context.Orders
    .Include(e => e.Product)
    .Include(e => e.Customer)
    .Single(order =>
        order.Customer.Name == customerName
        && order.OrderDate > on.Date
        && order.OrderDate < on.Date.AddDays(1));

This results in the following information:

Arthur ordered a DeLorean for $150000.00 on 10/5/2021 12:16:07 PM

Customer Service return $150,000 to Arthur. Arthur is not amused since he bought the DeLorean for $2.5 million!

Customer Service contact Engineering and insist that in the future orders should keep track of the price that the product was actually sold for. Engineering agree, but in the meantime they are able to use the historical data to retrieve the price of the DeLorean at the time it was sold. This is achieved by adding TemporalAsOf to the query used above:

var order = context.Orders
    .TemporalAsOf(on)
    .Include(e => e.Product)
    .Include(e => e.Customer)
    .Single(order =>
        order.Customer.Name == customerName
        && order.OrderDate > on.Date
        && order.OrderDate < on.Date.AddDays(1));

This query generates the following SQL:

SELECT TOP(2) [o].[Id], [o].[CustomerId], [o].[OrderDate], [o].[PeriodEnd], [o].[PeriodStart], [o].[ProductId], [p].[Id], [p].[Name], [p].[PeriodEnd], [p].[PeriodStart], [p].[Price], [c].[Id], [c].[Name], [c].[PeriodEnd], [c].[PeriodStart]
FROM [Orders] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [o]
LEFT JOIN [Customers] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [c] ON [o].[CustomerId] = [c].[Id]
LEFT JOIN [Products] FOR SYSTEM_TIME AS OF '2021-10-05T12:18:54.3934318Z' AS [p] ON [o].[ProductId] = [p].[Id]
WHERE (([c].[Name] = @__customerName_0) AND ([o].[OrderDate] > @__on_Date_1)) AND ([o].[OrderDate] < @__AddDays_2)

Notice the use of FOR SYSTEM_TIME AS OF. This tells SQL Server to look in the history table and return the record as it existed at that time. This results in the following information:

Arthur ordered a DeLorean for $2500000.00 on 10/5/2021 12:18:49 PM

Arthur gets his $2.5 million, then promptly deletes his account because of the experience.

Restoring deleted data

Some time later, Arthur decides he really needs a flux capacitor. Unfortunately, it’s only available at the same place that sells DeLoreans. He requests that his account be restored, including all previous orders. This can be done in two stages. First, the timestamp when the customer was deleted from the database can be found:

var customerDeletedOn = context.Customers
    .TemporalAll()
    .Where(customer => customer.Name == customerName)
    .OrderBy(customer => EF.Property<DateTime>(customer, "PeriodEnd"))
    .Select(customer => EF.Property<DateTime>(customer, "PeriodEnd"))
    .Last();

This timestamp is then used to craft a query that returns the customer and all the customer’s orders that existed at a specific point in time just before it was deleted:

var customerAndOrders = context.Customers
    .TemporalAsOf(customerDeletedOn.AddMilliseconds(-1))
    .Include(e => e.Orders)
    .Single();

Asking for records at a specific point in time ensures that orders that were already deleted before the customer account was deleted are not now erroneously restored.

Once the historical records have been retrieved, they can be re-inserted into the database using by adding them to the context and calling SaveChanges:

context.Add(customerAndOrders);
context.SaveChanges();

Arthur now has is account restored and can order a new flux capacitor, presumably allowing him to return to the future.

Summary

SQL Server temporal tables automatically keep track of the historical state of tables. EF Core 6.0 supports creating and modifying temporal tables mapped to entity types. This data can then be queried using new LINQ operators, and deleted data can be restored. This allows for easy auditing of data, as well as forensics on the deleted data and recovery from data corruption errors.

Special thanks to Arthur Vickers who contributed to this blog post.