Temporal History

Temporal history records a versioned snapshot of an entity on every insert, update and delete, so you can read its state as it was at any point in time, audit who changed what and when, restore a prior version, and diff two versions. History is opt-in per entity, append-only, and written in the same transaction as the change.

EF Core’s own temporal tables only exist on SQL Server. This package works the same on SQLite, PostgreSQL, SQL Server and MySQL: history lives in one ordinary table that your migrations create, and an interceptor fills it from the change tracker.

dotnet add package Shiny.EntityFrameworkCore.Temporal

public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    public DbSet<Order> Orders => Set<Order>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>().HasHistory(h =>
        {
            h.Retention   = TimeSpan.FromDays(90);   // prune closed versions older than this
            h.MaxVersions = 50;                      // …or cap the versions kept per entity
        });
    }
}

services.AddScoped<ICurrentUser, HttpCurrentUser>();
services.AddDbContext<AppDbContext>(o => o
    .UseNpgsql(connectionString)
    .UseEntityHistory(h =>
    {
        h.ResolveActor = sp => sp.GetService<ICurrentUser>()?.UserName;  // request-scoped "who"
    }));

Then dotnet ef migrations add AddHistory — the EntityHistory table is picked up like any other entity. No base class, no second context, and only HasHistory() entities pay for the extra write.

Why HasHistory and not IsTemporal

IsTemporal() is SQL Server’s system-versioned temporal table API. This feature doesn’t use it (it works on every provider), so it has its own name and the two can’t be confused.

Provider support

Provider	History	Notes
SQLite	✅	No ambient TransactionScope (a Microsoft.Data.Sqlite limitation)
PostgreSQL	✅
SQL Server	✅	RestoreAsync of a deleted identity-keyed row uses SET IDENTITY_INSERT
MySQL (MySql.EntityFrameworkCore)	✅

EntityVersion<T>

History reads return EntityVersion<T>:

Property	Description
Key	The entity key in its stored form (see How it works).
Version	Monotonic version number per entity, starting at 1.
ValidFrom	When this version became the current state.
ValidTo	When it was superseded — null for the version currently in effect.
Operation	Inserted, Updated, or Removed.
Actor	The captured actor, when actor capture is configured.
Entity	The entity at this version, detached (not tracked). null for Removed tombstones.

Per-entity queries

Every read is an extension method on your DbContext. Keys are passed the way DbContext.Find takes them — the key value, or an object[] in key order for a composite key.

HistoryAsync — every version of one entity

IReadOnlyList<EntityVersion<Order>> history = await db.HistoryAsync<Order>(orderId);

foreach (var v in history)
    Console.WriteLine($"v{v.Version} {v.Operation} by {v.Actor} at {v.ValidFrom}");

var grades = await db.HistoryAsync<Enrollment>(new object[] { studentId, "MATH" });   // composite key

AsOfAsync — state at a point in time

Order? lastTuesday = await db.AsOfAsync<Order>(orderId, new DateTimeOffset(2026, 6, 9, 0, 0, 0, TimeSpan.Zero));

Returns the version whose validity started at or before the instant and had not yet ended — null if the entity didn’t exist yet, or had been removed, at that instant.

RestoreAsync — reinstate a prior version

Order? restored = await db.RestoreAsync<Order>(orderId, version: 7);

RestoreAsync writes a new current version; history is never rewritten. If the entity still exists it is overwritten with the version-7 values (recorded as Updated); if it had been removed it is re-inserted with its original key — identity keys included — and recorded as Inserted. Returns the restored, tracked entity, or null if that version doesn’t exist or is a removal tombstone.

• It calls SaveChangesAsync, so any other pending changes in the context are saved with it.
• Store-computed values (row versions, computed columns) are regenerated by the database, and the live concurrency token is kept, so the restore is a deliberate overwrite rather than a stale-write conflict.
• Shadow properties are restored too (they’re part of the snapshot even though a detached Entity can’t show them).

GetDiffBetweenAsync — RFC 6902 patch between two versions

JsonPatchDocument<Order>? patch = await db.GetDiffBetweenAsync<Order>(orderId, fromVersion: 3, toVersion: 7);

string json = patch!.ToJson();   // [{"op":"replace","path":"/Status","value":"Shipped"}, …]
Order v7 = patch.ApplyTo(v3);    // replay it onto a copy

Paths are JSON Pointers over the snapshot: EF property names, with owned and complex values nested (/ShippingAddress/City, /Price/Amount). Objects are diffed member by member; arrays (owned collections, primitive collections) are replaced whole. Returns null if either version is missing or is a tombstone.

JsonPatchDocument<T> is the package’s own type, so there’s no dependency on ASP.NET Core’s JSON Patch.

Fleet-wide queries

These span every entity of the type and are backed by indexes on the history table.

AsOfAllAsync — point-in-time snapshot of every entity

IReadOnlyList<Order> endOfQuarter = await db.AsOfAllAsync<Order>(quarterEnd);

Entities that didn’t exist yet, or had been removed, at that instant are excluded.

ChangesByActorAsync — per-user audit trail

var byAlice = await db.ChangesByActorAsync<Order>("alice@corp.com");

Every version written by an actor, oldest first. Requires CaptureActor or ResolveActor.

ChangesBetweenAsync — audit log over a time window

var lastWeek = await db.ChangesBetweenAsync<Order>(from: weekStart, to: weekEnd);

Every version whose ValidFrom falls in the half-open window [from, to), oldest first.

Who made the change

Actor capture lives on the context options, not the model, because it needs code:

Option	Behaviour
CaptureActor (Func<string?>)	Called once per SaveChanges that writes history.
ResolveActor (Func<IServiceProvider, string?>)	Called with the context’s application service provider; takes precedence whenever there is one.

With AddDbContext (scoped options, the default) the application service provider is the request scope, so a scoped ICurrentUser works directly. With AddDbContextPool / pooled factories the options are singletons, so the provider is the root and scoped services aren’t reachable — use CaptureActor over something like IHttpContextAccessor there. A context you new up yourself has no application provider, so CaptureActor answers.

Retention

History is unbounded by default. Both settings run on every write of that entity, and the current version is never pruned:

Option	Behaviour
Retention (TimeSpan?)	Deletes closed versions whose ValidTo is older than now - Retention.
MaxVersions (int?)	Keeps only the newest N versions per entity.

On mobile / embedded SQLite especially, set at least one.

Other options

o.UseEntityHistory(h =>
{
    h.TimeProvider      = timeProvider;     // the clock for ValidFrom / ValidTo (tests: a fake one)
    h.SerializerOptions = jsonOptions;      // converters for property types System.Text.Json can't handle
    h.AllowBulkWrites   = false;            // see "Set-based writes" below
});

HasHistory(h => h.Name = "Order") sets the value stored in the history table’s EntityType column. It defaults to the CLR type’s full name; set it when you want history to survive renaming or moving the class.

How it works

• One shared table. HasHistory() adds an EntityHistoryRecord entity mapped to EntityHistory, with primary key (EntityType, EntityKey, Version) and indexes on (EntityType, ValidFrom) and (EntityType, Actor). Configure it like any entity — modelBuilder.Entity<EntityHistoryRecord>().ToTable("Audit") — and your settings win over the defaults. A single table means one migration, one place to query, and no per-entity schema churn.
• Snapshots come from EF’s metadata, not your object graph. Each version stores the entity’s scalar properties (shadow ones included), complex properties and owned entities, recursively, as JSON. Navigations to other entities are never followed, so there are no cycles and no accidental graph dumps; owned entities’ ownership keys are left out because they are plumbing, not state.
• Keys are stored as text: a single key is its invariant string (42, a GUID, SKU-1); a composite key is a JSON array of the parts (["1","MATH"]). Values with a value converter (strongly typed ids) are formatted from their provider value.
• Owned and complex changes are owner updates. Editing, replacing or clearing an owned entity or complex value records an Updated version of the owning entity, because that is the unit history tracks.
• Inheritance. Derived types share the history of the type that called HasHistory(); snapshots carry the concrete type, and reading with a derived T narrows to it.
• Atomic with your save. Store-generated values (identity keys, row versions) are only known after the INSERT, so history is written once they are, but always inside the transaction that carries your changes:
  • With no transaction in flight, the save runs with AutoTransactionBehavior.Always and history is written from TransactionCommitting — after the batch, before commit. That stays inside EF’s execution strategy, so EnableRetryOnFailure keeps working, and a retried save rewrites its history with the retried batch.
  • Inside your own transaction (or an ambient TransactionScope), history is written right after SaveChanges into that transaction, so it commits or rolls back with your work.
• Writing a version costs two short round trips per changed entity: read the current version number, then one batch that closes the previous version by its exact key (ValidTo = now), inserts the next one and applies retention. The SQL goes through EF’s command pipeline, so it shows up in EF’s logging and command interceptors. Timestamps are UTC with microsecond precision on every provider.

Set-based writes

ExecuteUpdate / ExecuteDelete run as one SQL statement without loading rows, so there is nothing to snapshot. Rather than let history silently drift, they throw when they target an entity with history (checked at query compilation, so it applies on every call):

await db.Orders.Where(o => o.Status == "Draft").ExecuteDeleteAsync();
// InvalidOperationException: ExecuteDelete targets 'Order', which keeps history …

Load the entities and SaveChanges when you need them recorded, or set AllowBulkWrites = true to accept writes that leave no versions (for example a deliberate purge). Entities that only appear in a filter subquery don’t count, and the history table itself can always be bulk-edited.

Limitations

• Raw SQL bypasses history. ExecuteSql*, FromSql updates, stored procedures and triggers are invisible to the change tracker and can’t be guarded. Route history-relevant writes through SaveChanges.
• Requires UseEntityHistory(). HasHistory() only marks the model; without the options call nothing is recorded, and the read methods throw to make the misconfiguration obvious.
• Rows written before HasHistory() have no prior history; their next change starts the timeline at version 1.
• History of the whole hierarchy. Reading with a derived T filters out sibling types’ versions, but a removal tombstone has no snapshot to tell its type, so it appears for every T in the hierarchy.
• Size limits. EntityType names are capped at 128 characters and keys at 256 (as text), so the primary key fits SQL Server’s and MySQL’s index limits. Actors are capped at 256.
• Concurrent writes to the same entity serialize on the entity’s row lock. A change confined to an owned entity in its own table doesn’t lock the owner’s row, so two such concurrent saves of the same owner can collide on the history key; the loser’s save fails and rolls back cleanly.
• SQLite doesn’t support ambient TransactionScope; use BeginTransaction there.