Temporal Querying Syntax

Retrieve historical snapshots of data using point-in-time query modifiers.

In this lesson, you will learn how to use EF Core’s temporal query modifiers to travel back in time and retrieve historical versions of your data.

Understanding Temporal Tables

A temporal table (or system-versioned table) automatically tracks the history of changes made to your data. When you update or delete a row, the database moves the "old" version into a separate history table, keeping a record of exactly when that change occurred. EF Core provides a fluent API syntax to query these historical states without needing to write raw SQL.

Point-in-Time Querying

To retrieve data as it existed at a specific moment in the past, we use the TemporalAsOf method. This method acts as a query modifier, shifting the scope of your IQueryable from the current data to the version that was active at the timestamp you provide.

Consider a scenario where you want to see what a Product's price was on a specific date:

var targetDate = new DateTime(2023, 01, 01, 12, 0, 0);

var historicalProduct = context.Products
    .TemporalAsOf(targetDate)
    .FirstOrDefault(p => p.Id == productId);

Key Query Modifiers

Beyond simply looking at a specific point in time, EF Core offers several modifiers to explore time ranges:

.TemporalAsOf(dateTime): Returns the row as it existed at the specified time.
.TemporalFromTo(start, end): Returns rows that were active within the specified range.
.TemporalBetween(start, end): Returns rows that were modified or existed between the start and end times (inclusive of the start, exclusive of the end).
.TemporalContainedIn(start, end): Returns rows that were both created and deleted within the specified time window.
.TemporalAll(): Returns all historical versions of the entity, including the current one.

These methods are appended to your DbSet before you execute your filtering logic (e.g., .Where(...)). Because these modifiers return an IQueryable, you can continue to apply standard LINQ operators like .Where, .OrderBy, or .Select to refine your historical results.

Key Takeaway: Temporal query modifiers allow you to treat historical database states as first-class citizens in your LINQ queries by simply prepending methods like TemporalAsOf to your existing data access patterns.