Track every EF Core record change with temporal tables

Tracking record changes in Entity Framework Core is harder than it should be. Temporal tables in SQL Server make it simple. Here's how they work.

What is a temporal table?

A temporal table is a regular table that SQL Server pairs with a mirrored history table. Every time a row changes, SQL Server automatically copies the previous version of that row into the history table, along with PeriodStart and PeriodEnd columns. These indicate when the version of that record was active.

In this tutorial, we'll use a Products table as an example.

CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[LastUpdated] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
))
GO

SQL Server creates a matching ProductsHistory table to log every change to the products, with PeriodStart and PeriodEnd columns indicating when each history record was active.

The good news is that if you're using Entity Framework Core, this is fully supported out of the box. There are no additional NuGet packages needed.

Seeing temporal tables in action

Let's walk through what happens to the data when we insert, update, or delete a product.

We'll start by inserting a product with the name Watch and a price of 60. When we run this, we get a record in Products, but nothing appears in ProductsHistory. That's expected, because the product has just been created, so we wouldn't expect any history records to be created at this stage.

dbo.Products

dbo.ProductsHistory

Next, let's update that price to 56. If we refresh the queries, Products now shows the price as 56, but we also have a new record in the history table. SQL Server has set the PeriodStart and PeriodEnd on that history row to indicate exactly when the previous version was active.

dbo.Products

dbo.ProductsHistory

Now let's delete that record. At this point, the product with an ID of 1 is no longer in the Products table. But this is the useful part about temporal tables. Even though we've deleted the product, all its history records still appear in ProductsHistory. We can keep a full audit of everything that's happened to that record, even after it's gone.

dbo.Products

dbo.ProductsHistory

Configuring a temporal table in Entity Framework Core

To configure Products as a temporal table, we call ToTable on the builder and specify the table name, then pass a configuration action that calls IsTemporal() on the options:

// ProductConfiguration.cs
public class ProductConfiguration : IEntityTypeConfiguration
{
	public void Configure(EntityTypeBuilder builder)
	{
		...
		builder.ToTable("Products", x => x.IsTemporal());
	}
}

Once we're happy with the configuration, we can add a migration and update the database.

Creating a temporal table without migrations

If you're not using migrations, here's an example of how to create the same thing directly in SQL.

CREATE TABLE [dbo].[Products](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[Price] [decimal](18, 2) NOT NULL,
	[LastUpdated] [datetime2](7) NOT NULL,
	[PeriodEnd] [datetime2](7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
	[PeriodStart] [datetime2](7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
	PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ProductsHistory])
)
GO

There are a couple of things to note. When specifying the columns for the periods, we need to use ROW START and ROW END. We also need to add a PERIOD FOR SYSTEM_TIME clause that references those columns. We also need to enable system versioning and specify the history table.

Querying the history with temporal queries

Now that we have a temporal table set up, let's go through the different ways we can query the history records in Entity Framework Core.

TemporalAsOf

The first query type is TemporalAsOf. This gives us the record as it existed at a particular point in time, in UTC. This is the one we'll probably use the most. Think of it as asking, "what did this record look like on Tuesday at 2pm?"

public async Task GetAsOfAsync(int id, DateTime utcDate)
{
	return await _context.Products
		.TemporalAsOf(utcDate)
		.Where(x => x.Id == id)
		.OrderBy(e => EF.Property(e, "PeriodStart"))
		.Select(x => new GetProductDto(
				x.Id,
				x.Name,
				x.Price,
				x.LastUpdated,
				EF.Property(x, "PeriodStart"),
				EF.Property(x, "PeriodEnd")
		))
		.FirstOrDefaultAsync();
}

TemporalAll

TemporalAll returns not only the history records but also the current record. We'd use this if we want a complete audit history for a record:

public async Task> GetAllAsync(int id)
{
	return await _context.Products
		.TemporalAll()
		.Where(x => x.Id == id)
		.OrderBy(e => EF.Property(e, "PeriodStart"))
		.Select(x => new GetProductDto(
				x.Id,
				x.Name,
				x.Price,
				x.LastUpdated,
				EF.Property(x, "PeriodStart"),
				EF.Property(x, "PeriodEnd")
		))
		.ToListAsync();
}

In this example, we pass in the ID as a parameter, and we get back all the history records as well as the current one where the ID matches. Where the product ID is 1, we'd expect this to return all the records from ProductsHistory where the ID is 1, as well as the current record from Products.

TemporalFromTo

With TemporalFromTo, we specify a date range, and it returns all the records whose active period falls within that range. However, these dates are exclusive, so if a record's period ends at the same time as the date range ends, it won't be included in the results:

public async Task> GetFromToAsync
	(int id, 
	DateTime fromUtcDate, 
	DateTime toUtcDate)
{
	return await _context.Products
		.TemporalFromTo(fromUtcDate, toUtcDate)
		.Where(x => x.Id == id)
		.OrderBy(e => EF.Property(e, "PeriodStart"))
		.Select(x => new GetProductDto(
				x.Id,
				x.Name,
				x.Price,
				x.LastUpdated,
				EF.Property(x, "PeriodStart"),
				EF.Property(x, "PeriodEnd")
		))
		.ToListAsync();
}

TemporalBetween

TemporalBetween is almost the same as TemporalFromTo, but with one subtle difference. If a record's period starts at the same time as the date range, it will be included in the results. This is the opposite of how TemporalFromTo behaves, and it's a difference that can trip up some developers:

public async Task> GetBetweenAsync
   (int id,
   DateTime fromUtcDate,
   DateTime toUtcDate)
{
	return await _context.Products
		.TemporalBetween(fromUtcDate, toUtcDate)
		.Where(x => x.Id == id)
		.OrderBy(e => EF.Property(e, "PeriodStart"))
		.Select(x => new GetProductDto(
				x.Id,
				x.Name,
				x.Price,
				x.LastUpdated,
				EF.Property(x, "PeriodStart"),
				EF.Property(x, "PeriodEnd")
		))
		.ToListAsync();
}

TemporalContainedIn

TemporalContainedIn is the most restrictive of all of them. The only records returned are the ones that both started and ended within the specified date range. If a record started before the range or finished after it, it won't be included in the results, even if it was active for part of that range:

public async Task> GetContainedInAsync
   (int id,
   DateTime fromUtcDate,
   DateTime toUtcDate)
{
	return await _context.Products
		.TemporalContainedIn(fromUtcDate, toUtcDate)
		.Where(x => x.Id == id)
		.OrderBy(e => EF.Property(e, "PeriodStart"))
		.Select(x => new GetProductDto(
				x.Id,
				x.Name,
				x.Price,
				x.LastUpdated,
				EF.Property(x, "PeriodStart"),
				EF.Property(x, "PeriodEnd")
		))
		.ToListAsync();
}

Changing the default column and table names

PeriodStart and PeriodEnd are the default column names for the date ranges in temporal tables, but Entity Framework Core lets us change these.

When configuring a temporal table, we can call HasPeriodStart and HasPeriodEnd to specify new column names, and UseHistoryTable to give the history table its own name. Here, we're renaming the columns to ValidFrom and ValidUntil, and the history table to CategoriesArchive:

// CategoryConfiguration.cs
public class CategoryConfiguration : IEntityTypeConfiguration
{
	public void Configure(EntityTypeBuilder builder)
	{
		...
		builder.ToTable("Categories", x => x.IsTemporal(
			b =>
			{
				b.HasPeriodStart("ValidFrom");
				b.HasPeriodEnd("ValidUntil");
				b.UseHistoryTable("CategoriesArchive");
			}
		));
	}
}

Watch the video

Watch the video below where you'll see all of this in action, including temporal queries running against real data: