← .NET Concepts

Temporal Tables and History Queries in EF Core

Published on 2026-11-12·v1.0

Objective

A SQL Server temporal table (also called a system-versioned table) keeps every version of every row. Each update or delete in the main table copies the old row into a parallel history table, stamped with the period during which that version was current. EF Core can create such tables, turn an existing table into one, query the history and restore data from a point in the past. For systems that must answer "what did this record look like on March 3rd?" (finance, health, anything audited) it is the database doing the work that an auditing interceptor would otherwise do in application code.

What was and was not run. Temporal tables are a SQL Server and Azure SQL feature. I had no SQL Server, so nothing in this lesson ran against a server. What I did run, on SDK 11.0.100 and Microsoft.EntityFrameworkCore.SqlServer 11.0.0, is the generation of SQL offline: the migration script from dotnet ef migrations script and the query text from ToQueryString(). The SQL below is what EF Core 11.0.0 generates; whether a given SQL Server version accepts it, and what the history table contains after a delete, comes from the EF Core documentation and the T-SQL behavior it describes, not from my execution. There is no lab for this lesson for that reason.

Use Cases

  • Answering "what was the value then" without writing audit tables, triggers or an interceptor.
  • Recovering from an accidental update or delete by reading the old row and inserting it again.
  • Listing every change to one record in order, with the time range of each version.
  • Turning an existing table into a system-versioned one with a migration.

Deep Dive

Configuring the table

The mapping is one call on the table builder:

csharp
modelBuilder.Entity<Employee>().ToTable("Employees", t => t.IsTemporal());

SQL Server adds two datetime2 period columns and the history table. By default EF Core maps the period columns to shadow properties, named PeriodStart and PeriodEnd, and the history table is EmployeesHistory (the table name plus History). The times are always UTC, generated by the server. You can rename all of it:

csharp
t.IsTemporal(h =>
{
    h.HasPeriodStart("ValidFrom");
    h.HasPeriodEnd("ValidTo");
    h.UseHistoryTable("EmployeesHistory", "audit");   // table, schema
});

The DDL EF Core generates (offline, not executed)

For a model with a temporal Departments table (history table in schema audit) and a plain Employees table, dotnet ef migrations script 0 Initial produced, trimmed:

plaintext
IF SCHEMA_ID(N'audit') IS NULL EXEC(N'CREATE SCHEMA [audit];');

CREATE TABLE [Departments] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    CONSTRAINT [PK_Departments] PRIMARY KEY ([Id]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [audit].[DepartmentsHistory]));

The period columns are HIDDEN, so SELECT * does not return them (T-SQL behavior, not run here); EF Core names them explicitly when it needs them.

Making an existing table temporal is the second migration, the interesting one. I generated it by adding IsTemporal() to Employees after the first migration and running dotnet ef migrations script Initial MakeEmployeesTemporal:

plaintext
ALTER TABLE [Employees] ADD [PeriodEnd] datetime2 NOT NULL DEFAULT '9999-12-31T23:59:59.9999999';

ALTER TABLE [Employees] ADD [PeriodStart] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.0000000';

ALTER TABLE [Employees] ADD PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])

ALTER TABLE [Employees] ALTER COLUMN [PeriodStart] ADD HIDDEN

ALTER TABLE [Employees] ALTER COLUMN [PeriodEnd] ADD HIDDEN

DECLARE @historyTableSchema nvarchar(max) = QUOTENAME(SCHEMA_NAME())
EXEC(N'ALTER TABLE [Employees] SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ' + @historyTableSchema + '.[EmployeesHistory]))')

Existing rows get PeriodStart of year 0001 and PeriodEnd of the maximum date, so from the database's point of view they existed forever. That is correct to read but means you cannot recover what they looked like before the migration. The documentation lists conversion of existing tables as supported through migrations; I did not execute this script against a table with data.

Ordinary reads and writes

Application code does not change. Inserts, updates and deletes go through SaveChanges as usual, and SQL Server maintains the history. A plain query returns only current rows; the period columns are in its SELECT list (shadow properties are selected like any other), and after a tracking query you can read them through context.Entry(e).Property<DateTime>("PeriodStart").CurrentValue. For current rows the end of the period is the maximum datetime2 value.

The query operators and their SQL

The operators are extension methods on the DbSet. These are the texts ToQueryString() returned (trimmed to the FROM; the select list is the entity columns):

csharp
db.Employees.TemporalAsOf(t0).Where(e => e.Id == 1)
plaintext
FROM [Employees] FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00.0000000Z' AS [e]
WHERE [e].[Id] = 1

TemporalAsOf returns the version that was current at one instant: at most one row per key.

csharp
db.Employees.TemporalAll().Where(e => e.Id == 1)
    .OrderBy(e => EF.Property<DateTime>(e, "PeriodStart"))
    .Select(e => new { e.Name, e.Salary,
                       From = EF.Property<DateTime>(e, "PeriodStart"),
                       To = EF.Property<DateTime>(e, "PeriodEnd") })
plaintext
SELECT [e].[Name], [e].[Salary], [e].[PeriodStart] AS [From], [e].[PeriodEnd] AS [To]
FROM [Employees] FOR SYSTEM_TIME ALL AS [e]
WHERE [e].[Id] = 1
ORDER BY [e].[PeriodStart]

TemporalAll returns every version, current and historical: that is the audit trail of a record. The other range operators translate to:

plaintext
FROM [Employees] FOR SYSTEM_TIME FROM '2026-01-01T00:00:00.0000000Z' TO '2026-06-01T00:00:00.0000000Z' AS [e]      -- TemporalFromTo
FROM [Employees] FOR SYSTEM_TIME BETWEEN '2026-01-01T00:00:00.0000000Z' AND '2026-06-01T00:00:00.0000000Z' AS [e]  -- TemporalBetween
FROM [Employees] FOR SYSTEM_TIME CONTAINED IN ('2026-01-01T00:00:00.0000000Z', '2026-06-01T00:00:00.0000000Z') AS [e] -- TemporalContainedIn

The documentation describes them as: TemporalFromTo returns versions active at some point between the two times; TemporalBetween is the same but also includes rows that became active exactly at the upper boundary; TemporalContainedIn returns only versions that both started and ended inside the range. The exact boundaries are defined by the SQL Server FOR SYSTEM_TIME clause, which I did not execute, so check its documentation if boundary rows matter to you.

A temporal source can sit inside a join, which compares the present with the past:

csharp
db.Employees.Where(e => e.Id == 1)
    .Join(db.Employees.TemporalAsOf(t0), c => c.Id, h => h.Id, (c, h) => new { c.Salary, Before = h.Salary })
plaintext
FROM [Employees] AS [e]
INNER JOIN [Employees] FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00.0000000Z' AS [e0] ON [e].[Id] = [e0].[Id]

Queries that use a temporal operator are no-tracking by default, according to the documentation. Passing DateTime values: all times are UTC, so build them with DateTimeKind.Utc or DateTimeOffset.UtcDateTime.

What EF Core 11 adds: period columns as CLR properties

Before EF Core 11 the period columns had to be shadow properties. Giving the entity a DateTime property for the period and pointing the configuration at it by name failed at model validation. I ran that case against Microsoft.EntityFrameworkCore.SqlServer 10.0.14 and got InvalidOperationException: Period property 'Department.ValidFrom' must be a shadow property. In EF Core 11 the lambda overloads exist and the property is a normal member:

csharp
t.IsTemporal(h =>
{
    h.HasPeriodStart(d => d.ValidFrom);   // does not compile in EF Core 10
    h.HasPeriodEnd(d => d.ValidTo);
});

db.Departments.TemporalAll().OrderBy(d => d.ValidFrom).Select(d => new { d.Name, d.ValidFrom, d.ValidTo })
plaintext
SELECT [d].[Name], [d].[ValidFrom], [d].[ValidTo]
FROM [Departments] FOR SYSTEM_TIME ALL AS [d]
ORDER BY [d].[ValidFrom]

No more EF.Property<DateTime>(e, "ValidFrom") strings. Per the documentation the period properties are generated by the server (ValueGenerated.OnAddOrUpdate), so do not set them when inserting or updating. The documentation labels this feature as new in EF Core 11. The queries and DDL for the default shadow-property mapping were identical in the two versions in my offline comparison.

Restoring a deleted row

The history table keeps the last version of a deleted row, with PeriodEnd set to the moment of the delete. To find it, order the history by period end:

csharp
db.Employees.TemporalAll().Where(e => e.Id == 1)
    .OrderByDescending(e => EF.Property<DateTime>(e, "PeriodEnd")).Take(1)
plaintext
SELECT TOP(@p) [e].[Id], [e].[DepartmentId], [e].[Name], [e].[PeriodEnd], [e].[PeriodStart], [e].[Salary]
FROM [Employees] FOR SYSTEM_TIME ALL AS [e]
WHERE [e].[Id] = 1
ORDER BY [e].[PeriodEnd] DESC

The documentation's way to restore is to read the row TemporalAsOf a time before the delete and mark it as added:

csharp
var employee = await context.Employees.TemporalAsOf(timeStamp).SingleAsync(e => e.Name == "Rainbow Dash");
context.Add(employee);
await context.SaveChangesAsync();

The entity is not tracked (temporal queries are no-tracking), so Add makes it a new insert. This creates a new current row, and the history then shows a new current version after the deleted one; it does not rewrite the past. Note that the documentation's example uses a Guid key. With an IDENTITY key, inserting the old value explicitly is rejected by SQL Server unless IDENTITY_INSERT is on (T-SQL behavior, which I did not run); either accept a new key or use a client-generated key for tables where you need this. I did not run the restore, so the insert SQL is not shown.

The alternative: an interceptor

The auditing interceptor lesson writes audit rows in application code, in the same transaction. A temporal table does it in the database: it also records changes made by scripts, other applications and ExecuteUpdate/ExecuteDelete, which bypass SaveChanges interceptors. The interceptor can record who made the change (the application user), which a temporal table does not: the period columns say when, not who. Many systems use both. PostgreSQL 18 added temporal constraints (the Npgsql 11 release notes mention them), a different feature that I did not verify.

Trade-offs

  • SQL Server only. The mapping, the operators and the DDL are provider features. Code that calls TemporalAsOf does not run on SQLite or PostgreSQL, so tests with those providers cannot cover it.
  • History grows without a limit by default. Every update stores the old row. Plan retention and index the history table, or storage and TemporalAll queries will slow down over time (SQL Server retention settings are T-SQL and not covered here).
  • When, not who. Period columns carry the time of the change. If the audit needs the user, you also need an application-level record.
  • No history before the conversion. Making an existing table temporal starts its history at that moment, with existing rows treated as always valid.
  • Restoring rewrites the present, not the past. A restore inserts a new current row. With identity keys you cannot reuse the old key without extra steps.
  • Not run against a server here. The SQL in this lesson was generated offline. Test migrations and queries on a real SQL Server before relying on them.

Documentation Links

Related concepts