Temporal tables
SQL Server temporal tables keep the full history of every row automatically. This lesson shows how to turn them on with EF Core, query a row as it was at any time, list all changes, and restore deleted data.
- StartCurrent rowProducts: Price 500, valid from 1 Oct
- Step 2UPDATE to 450Old version moves to ProductsHistory
- Step 3History rowPrice 500, valid 1 Oct to 20 Oct
- Step 4TemporalAsOf(15 Oct)Returns the 500 version
- EndTemporalAllReturns every version in order
What temporal tables do
A system-versioned temporal table has a hidden history table. Every UPDATE or DELETE copies the old version of the row there, with the time range when it was valid. You get an audit trail and point-in-time views without writing triggers.
Two period columns (PeriodStart and PeriodEnd) record when each version was current. EF Core maps them as shadow properties.
Turning it on
One line in the Fluent API; the next migration converts the table.
modelBuilder.Entity<Product>()
.ToTable("Products", t => t.IsTemporal(h =>
{
h.UseHistoryTable("ProductsHistory");
h.HasPeriodStart("ValidFrom");
h.HasPeriodEnd("ValidTo");
}));Querying history
// The product as it was last Monday at 9:00 UTC
var then = await db.Products
.TemporalAsOf(new DateTime(2026, 10, 26, 9, 0, 0, DateTimeKind.Utc))
.SingleAsync(p => p.Id == 7);
// Every price it has had
var history = await db.Products
.TemporalAll()
.Where(p => p.Id == 7)
.OrderBy(p => EF.Property<DateTime>(p, "ValidFrom"))
.Select(p => new { p.Price, From = EF.Property<DateTime>(p, "ValidFrom"),
To = EF.Property<DateTime>(p, "ValidTo") })
.ToListAsync();Restoring a deleted row and good to know
Find the last version before the delete with TemporalAll or TemporalAsOf, then add it back. IDENTITY keys need IDENTITY_INSERT, so restoring is often easier with a raw SQL INSERT ... SELECT from the history table.
- Other operators: TemporalBetween, TemporalFromTo and TemporalContainedIn.
- History grows forever unless you set a retention period (HISTORY_RETENTION_PERIOD) in SQL.
- Times are UTC.
- Temporal tables suit audit needs and accidental-change recovery; they are not a replacement for backups.
Interview question
How can you see what a row looked like last week using EF Core?
Make the table a SQL Server temporal table with IsTemporal() in the model, then query with db.Products.TemporalAsOf(dateUtc), which translates to FOR SYSTEM_TIME AS OF. SQL Server returns the version of each row that was current at that moment from the main or history table.
Want the full details? Read the official Microsoft documentation for this topic.