Temporal Tables in EF Core for Data Auditing

Temporal tables are a SQL Server feature that automatically keeps the full history of every row, and EF Core 6+ can configure and query them. On each update or delete, SQL Server copies the previous version into a history table, so you can read a row as it existed at any point in time. That covers recovery and investigation without audit code, provided retention and SQL Server lock-in are acceptable.

An audit record written by application code can miss changes made outside the application. Temporal tables move row-history capture into the database instead.

What Are Temporal Tables?

Temporal tables are a SQL Server feature that automatically maintains the full history of data changes. Every time a row is inserted, updated, or deleted, SQL Server copies the previous version to a history table with timestamps.

You don't need to write any audit logging code. The database handles it transparently. EF Core 6+ added first-class support for configuring and querying temporal tables.

Configuring Temporal Tables

Enable temporal tables in your entity configuration:

public class OrderConfiguration : IEntityTypeConfiguration<Order>
{
    public void Configure(EntityTypeBuilder<Order> builder)
    {
        builder.ToTable("Orders", b => b.IsTemporal());
    }
}

That's it. When you create a migration, EF Core generates:

CREATE TABLE [Orders] (
    [Id] uniqueidentifier NOT NULL,
    [Status] nvarchar(50) NOT NULL,
    [TotalAmount] decimal(18,2) NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Orders] PRIMARY KEY ([Id]),
    PERIOD FOR SYSTEM_TIME ([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[OrdersHistory]));

SQL Server adds two hidden columns (PeriodStart and PeriodEnd) and creates a history table automatically.

Customizing Period Columns

You can customize the column names and history table name:

builder.ToTable("Orders", b => b.IsTemporal(t =>
{
    t.HasPeriodStart("ValidFrom");
    t.HasPeriodEnd("ValidTo");
    t.UseHistoryTable("OrderAuditHistory");
}));

The period columns are shadow properties. They don't exist on your entity class, but you can still read them in queries with EF.Property:

var orders = await context.Orders
    .Select(o => new
    {
        o.Id,
        o.Status,
        PeriodStart = EF.Property<DateTime>(o, "PeriodStart"),
        PeriodEnd = EF.Property<DateTime>(o, "PeriodEnd")
    })
    .ToListAsync();

Mapping the period columns to regular CLR properties on the entity isn't supported until EF Core 11 (in preview at the time of writing). On earlier versions, EF.Property is the only way to get at them.

How It Works

When you update an entity through EF Core:

var order = await context.Orders.FindAsync(orderId);
order.Status = OrderStatus.Shipped;
await context.SaveChangesAsync();

SQL Server automatically:

1. Copies the current row (with the old values) to the history table
2. Updates the current row with the new values
3. Sets the PeriodStart of the updated row to the transaction start time (UTC)
4. Sets the PeriodEnd of the history row to that same timestamp

You don't need to intercept SaveChangesAsync or use the change tracker for audit tracking. The database does everything.

Querying Current Data

Regular queries work exactly as before:

var orders = await context.Orders
    .Where(o => o.Status == OrderStatus.Shipped)
    .ToListAsync();

This returns only current data. The history table is invisible to normal queries.

TemporalAsOf

Query how the data looked at a specific point in time:

var yesterday = DateTime.UtcNow.AddDays(-1);

var ordersAsOfYesterday = await context.Orders
    .TemporalAsOf(yesterday)
    .Where(o => o.Id == orderId)
    .ToListAsync();

This returns the row as it existed at that exact timestamp. If an order was Confirmed yesterday but Shipped today, TemporalAsOf returns the Confirmed version.

TemporalBetween

Query all versions of a row within a time range:

var startDate = DateTime.UtcNow.AddDays(-7);
var endDate = DateTime.UtcNow;

var orderHistory = await context.Orders
    .TemporalBetween(startDate, endDate)
    .Where(o => o.Id == orderId)
    .OrderBy(o => EF.Property<DateTime>(o, "PeriodStart"))
    .ToListAsync();

This returns every version of the order from the last seven days. You get one row for each change.

TemporalAll

Get the complete history of a row from creation to now:

var fullHistory = await context.Orders
    .TemporalAll()
    .Where(o => o.Id == orderId)
    .OrderBy(o => EF.Property<DateTime>(o, "PeriodStart"))
    .Select(o => new
    {
        o.Id,
        o.Status,
        o.TotalAmount,
        ValidFrom = EF.Property<DateTime>(o, "PeriodStart"),
        ValidTo = EF.Property<DateTime>(o, "PeriodEnd")
    })
    .ToListAsync();

This includes the current row and all historical versions. It's useful for building audit trails and change history views.

TemporalContainedIn and TemporalFromTo

Two more temporal operators for specific range semantics:

// Rows whose validity period started AND ended within the range
var contained = await context.Orders
    .TemporalContainedIn(startDate, endDate)
    .Where(o => o.Id == orderId)
    .ToListAsync();

// Rows that were active at any point between the two times
var fromTo = await context.Orders
    .TemporalFromTo(startDate, endDate)
    .Where(o => o.Id == orderId)
    .ToListAsync();

TemporalBetween is nearly identical to TemporalFromTo. The difference: it also includes rows that became active exactly on the upper boundary.

Restoring Deleted Data

One powerful use case - restoring accidentally deleted records:

// Find the deleted order in history
var deletedOrder = await context.Orders
    .TemporalAll()
    .Where(o => o.Id == orderId)
    .OrderByDescending(o => EF.Property<DateTime>(o, "PeriodStart"))
    .FirstOrDefaultAsync();

if (deletedOrder is not null)
{
    // Re-insert it
    context.Orders.Add(new Order
    {
        Id = deletedOrder.Id,
        Status = deletedOrder.Status,
        TotalAmount = deletedOrder.TotalAmount
    });

    await context.SaveChangesAsync();
}

The history table preserves deleted row versions until its retention or cleanup policy removes them, giving you a recovery path inside that window.

Queries using temporal operators are no-tracking by default. A historical version is not the current row, so EF Core keeps it out of the change tracker, and loading history while the current version is tracked doesn't cause identity conflicts. That's also why the restore example creates a new Order instead of re-attaching the historical instance.

Managing History Growth

Every update writes a row to the history table. On a hot table, that adds up fast, and the history table has no automatic cleanup by default.

SQL Server has a built-in retention policy, but EF Core doesn't expose it. Apply it with raw SQL in a migration:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(
        @"ALTER TABLE [Orders]
          SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 6 MONTHS));");
}

SQL Server then deletes history rows older than six months in the background. Pick a retention period that matches your compliance requirements. Keeping history forever on a frequently updated table just grows your storage bill.

Temporal Tables vs Application-Level Auditing

Temporal tables answer what changed and when. They can't answer who changed it or why, because SQL Server never sees your user context.

Use temporal tables when:

• You need point-in-time reconstruction of data (regulatory snapshots, debugging "what did the customer see")
• You want zero application code for history tracking
• You're on SQL Server and can afford the storage

Use application-level audit logging when:

• You need the acting user, correlation ID, or business reason attached to each change
• You're on PostgreSQL or another provider
• You only care about a handful of important entities, not every column change

In practice, many systems combine both: temporal tables for full data history, plus a lightweight audit log with user context via EF Core interceptors.

Limitations

Temporal tables have a few constraints:

• SQL Server only - PostgreSQL and other databases have different history mechanisms
• No filtering on history table - you can't add query filters to the history table
• Storage growth - frequent updates on large tables generate significant history data
• Schema changes - altering temporal tables requires extra care in migrations

Summary

SQL Server temporal tables preserve row versions independently of the application write path. Use EF Core's temporal operators for point-in-time reads and recovery, then define retention and storage policies before history grows without bound. Add a separate application audit trail when you also need to know who made a change and why.