jinal's blog

A Deep Dive into Temporal Tables with Entity Framework Core

Modern applications often need to answer questions like:

• What did this record look like yesterday?
• Who changed this value and when?
• Can we restore previous data states?

Traditionally, solving this required complex audit tables, triggers, or manual logging. But with SQL Server Temporal Tables and Entity Framework Core (EF Core), this becomes significantly simpler and more maintainable.

In this article, we’ll explore:

✅ What temporal tables are
✅ How they work internally
✅ How to configure them in EF Core
✅ How to query historical data
✅ Real-world use cases and best practices

📌 What Are Temporal Tables?

A temporal table is a system-versioned table that automatically tracks the full history of data changes.

It consists of two parts:

+-----------------------+        +---------------------------+

|   Current Table       |        |     History Table         |

|-----------------------|        |---------------------------|

| Id | Name | Salary    |        | Id | Name | Salary        |

|    |      |           |        | ValidFrom | ValidTo       |

+-----------------------+        +---------------------------+

🔄 How It Works

Every time a record is:

• ✅ Updated
• ✅ Deleted

SQL Server automatically:

Moves the old version to the history table

Updates the current table with the new values

Why Use Temporal Tables?

• Automatic auditing (no custom code required)
• Time-travel queries
• Data recovery
• Regulatory compliance (SOX, GDPR, etc.)
• Cleaner design compared to manual audit tables

Setting Up Temporal Tables in EF Core

Temporal table support was introduced in EF Core 6+, specifically for SQL Server.

Step 1: Define Your Entity

C#

public class Employee

{

public int Id { get; set; }

public string Name { get; set; }

public decimal Salary { get; set; }

}

Step 2: Configure Temporal Behavior

Inside your DbContext:

C#

protected override void OnModelCreating(ModelBuilder modelBuilder)

{

modelBuilder.Entity<Employee>()

.ToTable("Employees", tableBuilder => tableBuilder.IsTemporal(temporal =>

{

temporal.HasPeriodStart("ValidFrom");

temporal.HasPeriodEnd("ValidTo");

temporal.UseHistoryTable("EmployeeHistory");

}));

}

What This Configuration Does

+----------------------------------------------------+

| EF Core Configuration                              |

+----------------------------------------------------+

| IsTemporal() → Enables system versioning           |

| HasPeriodStart("ValidFrom") → Start time column    |

| HasPeriodEnd("ValidTo") → End time column          |

| UseHistoryTable("EmployeeHistory") → History table |

+----------------------------------------------------+

Step 3: Apply Migration

dotnet ef migrations add EnableTemporal

dotnet ef database update

Querying Temporal Data

This is where temporal tables shine 

EF Core provides built-in temporal query extensions.

1. Query Data at a Specific Point in Time

var employees = context.Employees

.TemporalAsOf(DateTime.UtcNow.AddDays(-7))

.ToList();

2. Retrieve Full History of a Record

var history = context.Employees

.TemporalAll()

.Where(e => e.Id == 1)

.ToList();

3. Query Changes Between Dates

var changes = context.Employees

.TemporalBetween(startDate, endDate)

.ToList();

 

4. Get Records Active Within a Range

var active = context.Employees

.TemporalFromTo(startDate, endDate)

.ToList();

Performance

• History tables grow quickly
• Index period columns:

SQL

        CREATE INDEX IX_EmployeeHistory_Period

        ON EmployeeHistory (ValidFrom, ValidTo);

🔄 Updates & Deletes

• Deletes are not permanent — they move data to history
• Bulk updates may need extra care

💡 Real-World Use Cases

🧾 Audit Tracking

Track who changed salary, address, or sensitive data.

🐞 Debugging

Reproduce bugs by restoring historical data state.

🏦 Financial Systems

Maintain legally required transaction history.

🗂️ Data Recovery

Restore accidental deletions or incorrect updates.

✅ Best Practices

✔ Use clear naming for history tables
✔ Add indexes on temporal columns
✔ Avoid excessive historical queries in high-load APIs
✔ Combine with soft delete if needed
✔ Periodically archive old history for performance

When NOT to Use Temporal Tables

Avoid temporal tables when:

• ❌ You need cross-database support (only SQL Server)
• ❌ You require custom audit logic (e.g., user IDs per change)
• ❌ Data volume grows extremely fast without archiving

Conclusion

Temporal tables in EF Core are a powerful, low-effort solution for tracking data history. With just a few lines of configuration, you gain:

✅ Automatic versioning
✅ Built-in auditing
✅ Time-travel queries

They eliminate the need for complex auditing systems while improving reliability and maintainability.