Temporal Table Support

In this lesson, you will learn how to enable SQL Server's built-in temporal tracking to automatically maintain a history of your data changes directly within your EF Core model.

Understanding Temporal Tables

In traditional database design, if you want to track "who changed what and when," you often end up creating complex audit tables and triggers. SQL Server’s Temporal Table feature simplifies this by keeping two versions of your data: the current row and a history row. EF Core makes this seamless by allowing you to configure this behavior via the Fluent API, offloading the heavy lifting of versioning to the database engine.

Configuring Temporal Support

To enable this, you use the IsTemporal() method within your OnModelCreating override. When configured, EF Core instructs the database to create a hidden history table and manage the start and end timestamps automatically.

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

Once configured, any Update or Delete operation on a Product entity triggers the database to move the existing data into the history table before applying the changes.

Point-in-Time Querying

The most powerful aspect of temporal tables is the ability to travel back in time. You don’t need to write custom logic to join against audit logs; instead, you use EF Core’s LINQ extensions to specify a point in history:

var oldVersion = context.Products
    .TemporalAsOf(new DateTime(2023, 1, 1))
    .FirstOrDefault(p => p.Id == productId);

By calling TemporalAsOf, EF Core generates the necessary FOR SYSTEM_TIME AS OF SQL command, letting you retrieve the exact state of your entity at that specific moment. This is invaluable for generating "as-of" reports or recovering from accidental data overwrites without ever leaving your LINQ syntax.

Key Takeaway: Temporal tables shift the responsibility of audit logging to the database engine, allowing you to track historical state changes and perform point-in-time queries through simple LINQ extensions.