Skip to main content
The Intent.EntityFrameworkCore.TemporalTables module adds SQL Server temporal tables support to Entity Framework Core, enabling automatic history tracking for entities.

​
Overview

Temporal tables (also known as system-versioned tables) automatically track the full history of data changes. This module:
  • Configures entities as temporal tables
  • Automatically tracks all changes with timestamps
  • Enables querying historical data
  • Supports point-in-time queries
  • Provides period columns for version tracking

​
Installation

Intent.EntityFrameworkCore.TemporalTables
This module requires SQL Server 2016 or later (or Azure SQL Database).

​
Configuration

Mark entities as temporal in the Domain Designer by applying the Temporal Table stereotype to your entity.

​
Entity Configuration

The module generates temporal table configuration:
Temporal Configuration
public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
{
    public void Configure(EntityTypeBuilder<Customer> builder)
    {
        builder.ToTable("Customers", b => b.IsTemporal(temporal =>
        {
            temporal.HasPeriodStart("ValidFrom");
            temporal.HasPeriodEnd("ValidTo");
            temporal.UseHistoryTable("CustomerHistory");
        }));

        builder.HasKey(x => x.Id);
        
        builder.Property(x => x.Name)
            .IsRequired()
            .HasMaxLength(200);
    }
}

​
Usage

​
Current Data Queries

Regular queries return only current data:
Current Data
// Returns only current customers
var customers = await _repository.FindAllAsync();

// Standard filtering
var customer = await _repository.FindByIdAsync(customerId);

​
Historical Queries

​
Query as of Specific Time

Point-in-Time Query
// Get customer as they were on January 1, 2024
var customerAtPoint = await _dbContext.Customers
    .TemporalAsOf(new DateTime(2024, 1, 1))
    .FirstOrDefaultAsync(x => x.Id == customerId);

​
Query All History

Full History
// Get all versions of a customer
var customerHistory = await _dbContext.Customers
    .TemporalAll()
    .Where(x => x.Id == customerId)
    .OrderBy(x => EF.Property<DateTime>(x, "ValidFrom"))
    .ToListAsync();

​
Query Between Dates

Date Range Query
// Get all versions between two dates
var customerVersions = await _dbContext.Customers
    .TemporalBetween(
        new DateTime(2024, 1, 1),
        new DateTime(2024, 12, 31))
    .Where(x => x.Id == customerId)
    .ToListAsync();

​
Query from Date to Now

From Date Query
// Get all versions since a specific date
var recentChanges = await _dbContext.Customers
    .TemporalFromTo(
        new DateTime(2024, 1, 1),
        DateTime.UtcNow)
    .Where(x => x.Id == customerId)
    .ToListAsync();

​
Query Contained in Period

Contained Query
// Get versions that existed entirely within a period
var containedVersions = await _dbContext.Customers
    .TemporalContainedIn(
        new DateTime(2024, 1, 1),
        new DateTime(2024, 12, 31))
    .Where(x => x.Id == customerId)
    .ToListAsync();

​
Practical Examples

​
Audit Trail

Audit Trail Service
public class CustomerAuditService
{
    private readonly ApplicationDbContext _dbContext;

    public async Task<List<CustomerAuditEntry>> GetAuditTrailAsync(Guid customerId)
    {
        var history = await _dbContext.Customers
            .TemporalAll()
            .Where(x => x.Id == customerId)
            .Select(x => new CustomerAuditEntry
            {
                Id = x.Id,
                Name = x.Name,
                Email = x.Email,
                ValidFrom = EF.Property<DateTime>(x, "ValidFrom"),
                ValidTo = EF.Property<DateTime>(x, "ValidTo")
            })
            .OrderByDescending(x => x.ValidFrom)
            .ToListAsync();

        return history;
    }
}

public class CustomerAuditEntry
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public DateTime ValidFrom { get; set; }
    public DateTime ValidTo { get; set; }
}

​
Change Detection

Change Detection
public class ChangeDetectionService
{
    private readonly ApplicationDbContext _dbContext;

    public async Task<List<Change>> GetChangesAsync(
        Guid customerId, 
        DateTime since)
    {
        var versions = await _dbContext.Customers
            .TemporalFromTo(since, DateTime.UtcNow)
            .Where(x => x.Id == customerId)
            .OrderBy(x => EF.Property<DateTime>(x, "ValidFrom"))
            .ToListAsync();

        var changes = new List<Change>();
        
        for (int i = 1; i < versions.Count; i++)
        {
            var prev = versions[i - 1];
            var curr = versions[i];

            if (prev.Name != curr.Name)
            {
                changes.Add(new Change
                {
                    Property = "Name",
                    OldValue = prev.Name,
                    NewValue = curr.Name,
                    ChangedAt = EF.Property<DateTime>(curr, "ValidFrom")
                });
            }

            if (prev.Email != curr.Email)
            {
                changes.Add(new Change
                {
                    Property = "Email",
                    OldValue = prev.Email,
                    NewValue = curr.Email,
                    ChangedAt = EF.Property<DateTime>(curr, "ValidFrom")
                });
            }
        }

        return changes;
    }
}

​
Restore Previous Version

Restore
public async Task RestoreCustomerToDateAsync(Guid customerId, DateTime restoreDate)
{
    // Get the version at the restore point
    var historicalVersion = await _dbContext.Customers
        .TemporalAsOf(restoreDate)
        .FirstOrDefaultAsync(x => x.Id == customerId);

    if (historicalVersion == null)
    {
        throw new InvalidOperationException("No version found at that date");
    }

    // Get current version
    var currentVersion = await _dbContext.Customers
        .FindAsync(customerId);

    if (currentVersion != null)
    {
        // Restore properties
        currentVersion.Name = historicalVersion.Name;
        currentVersion.Email = historicalVersion.Email;
        // ... restore other properties

        await _dbContext.SaveChangesAsync();
    }
}

​
Database Schema

Temporal tables create two tables:

​
Main Table

Main Table
CREATE TABLE Customers
(
    Id uniqueidentifier NOT NULL PRIMARY KEY,
    Name nvarchar(200) NOT NULL,
    Email nvarchar(256) NOT NULL,
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

​
History Table

History Table
CREATE TABLE CustomerHistory
(
    Id uniqueidentifier NOT NULL,
    Name nvarchar(200) NOT NULL,
    Email nvarchar(256) NOT NULL,
    ValidFrom datetime2 NOT NULL,
    ValidTo datetime2 NOT NULL
);

​
Migrations

When adding temporal table support to an existing entity:
Migration
public partial class AddTemporalToCustomers : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterTable(
            name: "Customers")
            .Annotation("SqlServer:IsTemporal", true)
            .Annotation("SqlServer:TemporalHistoryTableName", "CustomerHistory")
            .Annotation("SqlServer:TemporalHistoryTableSchema", null)
            .Annotation("SqlServer:TemporalPeriodEndColumnName", "ValidTo")
            .Annotation("SqlServer:TemporalPeriodStartColumnName", "ValidFrom");

        migrationBuilder.AddColumn<DateTime>(
            name: "ValidFrom",
            table: "Customers",
            type: "datetime2",
            nullable: false,
            defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified))
            .Annotation("SqlServer:TemporalIsPeriodStartColumn", true);

        migrationBuilder.AddColumn<DateTime>(
            name: "ValidTo",
            table: "Customers",
            type: "datetime2",
            nullable: false,
            defaultValue: new DateTime(9999, 12, 31, 23, 59, 59, 9999999, DateTimeKind.Unspecified))
            .Annotation("SqlServer:TemporalIsPeriodEndColumn", true);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AlterTable(
            name: "Customers")
            .OldAnnotation("SqlServer:IsTemporal", true)
            .OldAnnotation("SqlServer:TemporalHistoryTableName", "CustomerHistory")
            .OldAnnotation("SqlServer:TemporalHistoryTableSchema", null)
            .OldAnnotation("SqlServer:TemporalPeriodEndColumnName", "ValidTo")
            .OldAnnotation("SqlServer:TemporalPeriodStartColumnName", "ValidFrom");

        migrationBuilder.DropColumn(
            name: "ValidFrom",
            table: "Customers");

        migrationBuilder.DropColumn(
            name: "ValidTo",
            table: "Customers");
    }
}

​
Performance Considerations

Query Performance

Historical queries can be slower. Use appropriate indexes on ValidFrom and ValidTo columns.

Storage

History tables can grow large. Consider retention policies for old data.

Indexes

Add indexes to history tables for frequently queried columns.

Data Retention

Implement cleanup jobs for old historical data if needed.

​
Retention Policy Example

Retention Policy
ALTER TABLE Customers
SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE Customers
SET (SYSTEM_VERSIONING = ON (
    HISTORY_TABLE = dbo.CustomerHistory,
    HISTORY_RETENTION_PERIOD = 6 MONTHS
));

​
Limitations

  • Only available for SQL Server 2016+ and Azure SQL Database
  • Cannot use with Cosmos DB or other NoSQL databases
  • Period columns (ValidFrom, ValidTo) are read-only
  • Cannot use with In-Memory tables
  • Some data types have restrictions (e.g., FILESTREAM)

​
Best Practices

  1. Use for audit-sensitive entities - Apply temporal tables to entities that require compliance or audit trails
  2. Monitor history table size - Implement retention policies to manage storage
  3. Index appropriately - Add indexes to history tables for common queries
  4. Consider query costs - Historical queries can be expensive; use caching when appropriate
  5. Test migrations - Thoroughly test migrations when adding temporal support to existing tables

​
Additional Resources

Build docs developers (and LLMs) love

Get started for freeTalk to us