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));