Add support for temporal tables

[sjh37]

https://woodruff.dev/temporal-tables-in-ef-core-bringing-time-travel-to-your-data/

Given

public class Employee
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public decimal Salary { get; set; }
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Employee>()
        .ToTable("Employees", tb => tb.IsTemporal());
}

Running migration

dotnet ef migrations add AddTemporalTables
dotnet ef database update

EF Core will create

CREATE TABLE Employees (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    Salary DECIMAL(18,2),
    SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

SQL Server automatically:

• Adds SysStartTime and SysEndTime columns to track changes.
• Creates an EmployeesHistory table to store old versions of records.
• Enables SYSTEM_VERSIONING to automatically track changes.

No need to manually update history records—SQL Server handles it for you.

Querying Historical Data

Want to see all past versions of a record? Use .TemporalAll():

var allVersions = await context.Employees.TemporalAll()
    .Where(e => e.Id == 1)
    .ToListAsync();

This pulls data from both the main table AND the history table!

Querying Data from a Specific Time

Want to see what your database looked like last week? Use .TemporalAsOf(DateTime):

var lastWeekData = await context.Employees
    .TemporalAsOf(DateTime.UtcNow.AddDays(-7))
    .ToListAsync();

Time-traveling to last week’s database state!

Seeing Changes Over a Time Range

Need to track how an employee’s salary changed over time? Use .TemporalBetween(start, end):

var salaryChanges = await context.Employees
    .TemporalBetween(DateTime.UtcNow.AddMonths(-3), DateTime.UtcNow)
    .Where(e => e.Name == "Alice")
    .ToListAsync();

Perfect for analyzing trends, auditing, and debugging.

Manually created temporal tables may look like:

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

[sjh37]

https://dataedo.com/kb/query/sql-server/list-temporal-tables-in-database

List all temporal tables with

SELECT SCHEMA_NAME(t.schema_id) AS temporal_table_schema,
       t.name AS temporal_table_name,
       SCHEMA_NAME(h.schema_id) AS history_table_schema,
       h.name AS history_table_name,
       CASE WHEN t.history_retention_period = -1 THEN 'INFINITE'
           ELSE CAST(t.history_retention_period AS VARCHAR) + ' ' + t.history_retention_period_unit_desc + 'S'
       END AS retention_period
FROM sys.tables t
    LEFT OUTER JOIN sys.tables h
        ON t.history_table_id = h.object_id
WHERE t.temporal_type = 2 -- SYSTEM_VERSIONED_TEMPORAL_TABLE
ORDER BY temporal_table_schema, temporal_table_name;

Columns

• temporal_table_schema - temporal table schema name
• temporal_table_name - temporal table name
• history_table_schema - history table schema name
• history_table_name - history table name
• retention_period - retention period - how long history is preserved (defined by DBA). Example values: INFINITE, 6 MONTHS, 30 DAYS

Rows

• One row represents one temporal table
• Scope of rows: only temporal tables defined in a database
• Ordered by temporal table schema and table name

And this is how it looks in SSMS:

[sjh37]

https://learn.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver16

How do I query temporal data?

The SELECT ... FROM

statement has a new clause FOR SYSTEM_TIME, with five temporal-specific subclauses to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

When you query using the FOR SYSTEM_TIME clause using one of the five subclauses, historical data from the temporal table are included, as shown in the following image.

The following query searches for row versions for an employee with the filter condition WHERE EmployeeID = 1000 that were active at least for a portion of period between January 1, 2021 and January 1, 2022 (including the upper boundary):
SQL

CREATE TABLE dbo.Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2 GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

SELECT * FROM Employee
    FOR SYSTEM_TIME
        BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
            WHERE EmployeeID = 1000 ORDER BY ValidFrom;

FOR SYSTEM_TIME filters out rows with a validity period of zero duration (ValidFrom = ValidTo).

Those rows are generated if you perform multiple updates on the same primary key within the same transaction. In that case, temporal querying returns only row versions before the transactions and current rows after the transactions.

If you need to include those rows in the analysis, query the history table directly.