In this lesson, you will learn how to leverage SQL Server Temporal Tables through EF Core to query historical data states and reconstruct a complete audit trail for your domain entities.
Understanding Temporal Tables
When you manage data that changes frequently, a standard table only reflects the "current" state. To see how an entity looked last Tuesday, you would typically need a separate audit log table. Temporal tables simplify this by automatically maintaining a version history directly in the database. EF Core maps these using two hidden shadow properties: PeriodStart and PeriodEnd.
Querying Across Time
Once you have configured a table as temporal via the Fluent API using .ToTable(t => t.IsTemporal()), you can use EF Core’s EF.Functions.TemporalAsOf() or TemporalBetween() to travel back in time. This allows your application to provide an audit trail without complex joins against manual history tables.
Consider a scenario where you need to verify the state of a Product entity at a specific point in time:
var historicalDate = new DateTime(2023, 05, 01, 12, 0, 0);
// Retrieve the product state as it existed at the historical date
var oldVersion = await context.Products
.TemporalAsOf(historicalDate)
.FirstOrDefaultAsync(p => p.Id == productId);
The Power of Audit Trails
Because the database handles the versioning logic, you can easily query the history of an entity to present a timeline to the user. For instance, you can use TemporalBetween to fetch all versions of a record between two timestamps, allowing you to build a "diff" view or a full history report of price changes or status updates.
This approach ensures that your domain logic remains clean. You don't need to manually intercept SaveChanges to write to an audit table; instead, you rely on the database engine's native temporal capabilities to ensure data integrity. By keeping the history coupled with the entity schema, you ensure that as your domain models evolve, your audit logs evolve alongside them automatically.
Key Takeaway: Temporal tables provide a native, high-performance way to query historical data states in EF Core, transforming your audit trail from a manual maintenance burden into a simple, time-based query operation.