Temporal Tables in EF Core: Querying Data Through Time

SQL Server temporal tables (also called system-versioned tables) automatically maintain a complete history of every row change. Every UPDATE and DELETE preserves the previous version in a history table. EF Core 6 added first-class support for configuring and querying temporal tables.

What Are Temporal Tables?

A temporal table has two hidden datetime2 columns — PeriodStart and PeriodEnd — managed automatically by SQL Server. When a row is updated, the database moves the old version to a paired history table with the time range it was valid. You never manually maintain this history; the database handles it transparently.

Configuring Temporal Tables

Enable temporal support in your model configuration:

Example.cs

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

For more control over column and history table names:

Example.cs

modelBuilder.Entity<Product>()
    .ToTable("Products", b => b.IsTemporal(temporal =>
    {
        temporal.HasPeriodStart("ValidFrom");
        temporal.HasPeriodEnd("ValidTo");
        temporal.UseHistoryTable("ProductsHistory");
    }));

When you create a migration, EF Core generates the SQL to create the temporal table:

Migrations/CreateTemporalProducts.sql

CREATE TABLE [Products] (
    [Id] int NOT NULL IDENTITY,
    [Name] nvarchar(max) NOT NULL,
    [Price] decimal(18,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START HIDDEN,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END HIDDEN,
    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo]),
    CONSTRAINT [PK_Products] PRIMARY KEY ([Id])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[ProductsHistory]));

Point-in-Time Queries

Query what the data looked like at a specific moment:

Example.cs

var lastMonth = DateTime.UtcNow.AddMonths(-1);

var productsAsOfLastMonth = await context.Products
    .TemporalAsOf(lastMonth)
    .ToListAsync();

This generates:

query.sql

SELECT * FROM [Products] FOR SYSTEM_TIME AS OF '2025-08-19T09:00:00'

You get back the exact state of every product as it existed at that timestamp. Products that didn't exist yet are excluded. Products that were deleted since then are included with their last known values.

Range Queries

Query all versions of rows that overlap with a time range:

Example.cs

var startDate = new DateTime(2025, 8, 1, 0, 0, 0, DateTimeKind.Utc);
var endDate = new DateTime(2025, 9, 1, 0, 0, 0, DateTimeKind.Utc);

// All versions that were active at any point during August
var augustHistory = await context.Products
    .TemporalBetween(startDate, endDate)
    .ToListAsync();

// All versions contained entirely within the range
var containedHistory = await context.Products
    .TemporalContainedIn(startDate, endDate)
    .ToListAsync();

// All versions — current and historical
var allVersions = await context.Products
    .TemporalAll()
    .Where(p => p.Id == 42)
    .ToListAsync();

Accessing Period Columns

To read the period start/end values, use EF.Property:

Example.cs

var productHistory = await context.Products
    .TemporalAll()
    .Where(p => p.Id == 42)
    .OrderBy(p => EF.Property<DateTime>(p, "ValidFrom"))
    .Select(p => new
    {
        p.Name,
        p.Price,
        ValidFrom = EF.Property<DateTime>(p, "ValidFrom"),
        ValidTo = EF.Property<DateTime>(p, "ValidTo")
    })
    .ToListAsync();

foreach (var version in productHistory)
{
    Console.WriteLine(
        $"{version.Name}: £{version.Price} " +
        $"({version.ValidFrom:g} to {version.ValidTo:g})");
}

Building an Audit Trail

Temporal tables provide a free audit trail. Combine TemporalAll() with comparisons to build a change log:

Example.cs

public async Task<List<ProductChange>> GetProductChangesAsync(int productId)
{
    var history = await context.Products
        .TemporalAll()
        .Where(p => p.Id == productId)
        .OrderBy(p => EF.Property<DateTime>(p, "ValidFrom"))
        .Select(p => new
        {
            p.Name,
            p.Price,
            ValidFrom = EF.Property<DateTime>(p, "ValidFrom")
        })
        .ToListAsync();

    return history.Zip(history.Skip(1), (old, current) => new ProductChange
    {
        ChangedAt = current.ValidFrom,
        OldPrice = old.Price,
        NewPrice = current.Price,
        OldName = old.Name,
        NewName = current.Name
    }).ToList();
}

Restoring Deleted Data

One of the most powerful uses — recovering accidentally deleted records:

Example.cs

var deletedProducts = await context.Products
    .TemporalAll()
    .Where(p => EF.Property<DateTime>(p, "ValidTo") < DateTime.UtcNow)
    .Where(p => !context.Products.Any(current => current.Id == p.Id))
    .ToListAsync();

// Re-insert a deleted product
foreach (var product in deletedProducts)
{
    context.Products.Add(new Product
    {
        Name = product.Name,
        Price = product.Price
    });
}
await context.SaveChangesAsync();

Limitations

SQL Server only — temporal tables are a SQL Server feature. PostgreSQL has similar capabilities but they're not mapped by EF Core's Npgsql provider in the same way.
Period columns are hidden by default — you access them via EF.Property, not directly on the entity.
History tables are append-only — you can't modify historical data through EF Core.
Schema changes affect history — adding or removing columns from the main table also modifies the history table.

When to Use Temporal Tables

Temporal tables are ideal for regulatory compliance (financial records, healthcare data), audit trails without custom infrastructure, and debugging production issues ("what did this record look like yesterday?"). They add minimal overhead to writes and provide extraordinary querying capabilities for free.