# Temporal Tables in EF Core for Data Auditing

> Temporal tables automatically track the full history of every row. EF Core 6+ has built-in support for configuring and querying temporal tables on SQL Server.

Published: 2026-08-31. Author: Milan Jovanović.

Canonical: https://milanjovanovic.tech/blog/temporal-tables-ef-core

**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](https://milanjovanovic.tech/blog/audit-logging-ef-core) 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:

```csharp
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](https://milanjovanovic.tech/blog/ef-core-migrations-best-practices), EF Core generates:

```sql
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:

```csharp
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`:

```csharp
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:

```csharp
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](https://milanjovanovic.tech/blog/change-tracker-ef-core) for audit tracking. The database does everything.

![When an Order row is updated, SQL Server updates the current row in the Orders table and automatically copies the previous version into the OrdersHistory table](https://milanjovanovic.tech/blogs/articles/temporal-tables-ef-core/temporal-update-flow.png)

## Querying Current Data

Regular queries work exactly as before:

```csharp
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:

```csharp
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:

```csharp
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:

```csharp
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:

```csharp
// 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:

```csharp
// 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](https://milanjovanovic.tech/blog/change-tracker-ef-core), 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:

```csharp
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](https://milanjovanovic.tech/blog/audit-logging-ef-core) 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**](https://milanjovanovic.tech/blog/how-to-use-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](https://milanjovanovic.tech/blog/how-to-use-global-query-filters-in-ef-core) 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.

## Frequently asked questions

### What are temporal tables in SQL Server?

Temporal tables (system-versioned tables) automatically keep the full history of every row. On each update or delete, SQL Server copies the previous version to a history table with period timestamps, so you can query the data as it existed at any point in time.

### Does EF Core support temporal tables?

Yes. Since EF Core 6 you can map an entity with builder.ToTable(name, b => b.IsTemporal()) and query history with TemporalAsOf, TemporalAll, TemporalBetween, TemporalFromTo, and TemporalContainedIn.

### Do temporal tables work with PostgreSQL or MySQL?

No, EF Core temporal table support is SQL Server only. On PostgreSQL you can approximate the feature with triggers and extensions or use application-level audit logging instead.

### Do temporal tables replace audit logging?

Only partially. Temporal tables capture what changed and when, but not who changed it or why. If you need user context or business-level audit events, combine temporal tables with application-level auditing or use interceptors.

### How do I limit the size of a temporal history table?

Use SQL Server's history retention policy (HISTORY_RETENTION_PERIOD) to automatically purge old history rows. EF Core does not expose this setting, so apply it with raw SQL in a migration.