One Query Across Every Version

Part 7 left us with a deliberately awkward database: current rows in Orders, older rows in Orders_v2 and Orders_v1, and a registry that knows which is which. Awkward is fine for storage; it is unacceptable for callers. Application code should say “find order 42” and “list the orders” and never learn that schema eras exist.

That is the job of two scoped services — VersionedDbReader and VersionedDbWriter — registered by the EF integration's setup call:

builder.Services.AddVersionKitEntityFramework<AppDbContext>(options =>
{
    options.EnableBackfillService = true;    // the drain worker — Part 9
});

The point lookup: a fallback chain

FindAsync is the workhorse, and its structure is a chain of increasingly expensive questions:

public async Task<T?> FindAsync<T>(Guid id, CancellationToken ct = default) where T : class
{
    // 1. Fast path — check current version table
    var entity = await _repository.FindInCurrentTableAsync<T>(id, ct);
    if (entity is not null) return entity;

    // 2. If not versioned, stop
    if (!MigrationRegistry.IsVersioned(typeof(T))) return null;

    // 3. Check registry
    var registry = await _repository.FindRegistryEntryAsync(id, ct);
    if (registry is null) return null;
    if (registry.CurrentTable == VersionTableSchema.GetBaseTableName(typeof(T)))
        return null;   // registry says current table — already checked, row is gone

    // 4. Read from archive table
    var row = await _repository.ReadFromArchiveTableAsync(typeof(T), registry, ct);
    if (row is null)
        return await _repository.FindInCurrentTableAsync<T>(id, ct);  // mid-move retry

    // 5. Migrate via the existing MigrationRunner pipeline
    row["__version"] = registry.SchemaVersion;
    var result = _runner.Migrate<T>(row);
    return result.Entity;
}

The happy path — the row already lives in the current table — is one FindAsync against EF, same cost as not using VersionKit at all (the entity is detached before returning, so reads never pollute the change tracker). Only a miss escalates: registry lookup, then a raw SQL read against whichever archive table the registry names.

Step 4's null branch is my favourite line in the EF package. Between the registry lookup and the archive read, a concurrent backfill (Part 9) may have moved the row — drained it from Orders_v2 into Orders. The archive read comes back empty not because the entity is gone but because it just changed address. The retry against the current table turns that race into a correct answer. It is two lines, it is untestable by any unit test that does not orchestrate a concurrent drain, and it is the difference between “eventually consistent” being a promise or an excuse.

Step 5 is the reuse moment: the archive row becomes a dictionary, gets stamped with the registry's version, and goes through the same MigrationRunner from Part 4 — attribute steps, hand-written overrides, type migrators, all of it. The relational side did not grow a second migration engine; it grew a way of feeding rows to the first one.

Reading an old table that doesn't match the class

Step 4 hides its own problem: the archive table has old columns — ClientId, not CustomerId — and there is no v2 entity class to map them onto. The answer is VersionTableSchema, which reconstructs any version's column list from the property attributes:

public static Dictionary<string, string> GetColumnsAtVersion(Type entityType, int version)
{
    // for each readable property, merge its [PropertyVersion] stack, then:
    if (merged.AddedIn > version) continue;                                  // didn't exist yet
    if (merged.RemovedInVersion > 0 && merged.RemovedInVersion <= version) continue; // already gone

    string columnName = GetColumnNameAtVersion(prop.Name, merged, version);  // old name pre-rename
    columns[columnName] = prop.Name;   // column-at-that-version → current property
}

This is the same declared history from Part 2, projected onto a third consumer (after the JSON pipeline and the model conventions): what did the table look like at version N? From that map, BuildSelectById emits a SELECT whose aliases translate eras:

SELECT "Id", "OrderNumber", "Total", "ClientId" AS "CustomerId",
       "ShippingCity" AS "City", "LegacyNotes", "IsExpedited"
FROM "Orders_v1" WHERE "Id" = @id

The row comes back keyed by current property names, values still in their old types (IsExpedited is a string here) — exactly the shape the migration walk expects a v1 payload to have. Note the values are genuine CLR types from the data reader, not JsonElements; this is why Part 5's adapters and providers are written to accept both.

The list query: a generated UNION view

Point lookups chase one address. Queries are worse: “all orders this month” may have answers in every era. VersionKit's answer is a database view, regenerated by the migration helper on every bump, that presents all versions in the current shape:

CREATE OR REPLACE VIEW "__AllOrders" AS
  SELECT "Id", ..., "CustomerId", "City", "Status", "IsExpedited", "IsHighValue",
         3 AS "__version" FROM "Orders"
  UNION ALL
  SELECT "Id", ..., "CustomerId", "City", "Status", "IsExpedited",
         NULL::boolean AS "IsHighValue", 2 AS "__version" FROM "Orders_v2"
  UNION ALL
  SELECT "Id", ..., "ClientId" AS "CustomerId", "ShippingCity" AS "City",
         NULL::text AS "Status", "IsExpedited",
         NULL::boolean AS "IsHighValue", 1 AS "__version" FROM "Orders_v1";

Each branch is built by the same column-map logic: old names aliased to current, columns that did not exist at that version emitted as typed NULLs (NULL::boolean — the cast matters, because a bare NULL would let PostgreSQL infer mismatched types across UNION branches), and every branch stamped with its version as a literal column. The __version column is the watermark, relationally.

The reader consumes it like this:

var rows = await _repository.ReadAllFromViewAsync("__AllOrders", ct);
foreach (var row in rows)
{
    int version = Convert.ToInt32(row["__version"]);
    if (version < currentVersion)
    {
        var result = _runner.Migrate<T>(row);   // NULL defaults, type conversions
        results.Add(result.Entity);
    }
    else { /* current rows deserialise directly */ }
}

The view gives structural alignment; the migration walk still runs per old row to fill computed defaults and convert types the view could only NULL or pass through. And this is where I owe you the honest costs. QueryAllAsync returns a materialised list, not an IQueryable — filtering happens after every row crosses the wire, because predicates against the view would need translating into per-era column names to push down. A SELECT * over the union of all history is a query whose cost grows with your past. My guidance in practice: Query<T>() (current table, fully composable LINQ, no tracking) for the 99% of application queries that only care about live data; QueryAllAsync<T>() for the admin screens, exports, and audits that genuinely need everything; and if a hot query must span eras, that is your cue to drain the archives (Part 9) rather than optimise the view. The view also quietly enables plain SQL consumers — reporting tools can read __AllOrders without knowing VersionKit exists.

Writes: keeping the registry honest

Every write path in VersionedDbWriter exists to maintain one invariant: the registry always knows where every row lives.

Add inserts the entity and its registry row in one SaveChanges:

_repository.AddRegistryEntry(new EntityRegistryEntry
{
    Id = id, EntityType = typeof(T).Name,
    CurrentTable = tableName, SchemaVersion = currentVersion,
    CreatedAtUtc = DateTime.UtcNow
});
_repository.AddEntity(entity);
await _repository.SaveChangesAsync(ct);

Update is where the design gets interesting. If the registry says the row still lives in an archive table, the update becomes a write-through migration:

if (registry is not null && registry.CurrentTable != tableName)
{
    _repository.AddEntity(entity);                                        // insert into current
    await _repository.DeleteFromArchiveTableAsync(registry.CurrentTable, id, ct);
    registry.CurrentTable = tableName;                                     // repoint the address
    registry.SchemaVersion = currentVersion;
}
else
{
    _repository.UpdateEntity(entity);
}
await _repository.SaveChangesAsync(ct);

Think about what the caller did: read an old order (which FindAsync migrated on the fly), changed a field, saved. The entity in hand is already current-shape — so saving it into the current table and deleting the archive row is the migration. Touched data promotes itself; the backfill only ever handles what nobody touches. Rows migrate in order of how much anyone cares about them, which is the correct priority order, discovered for free.

Remove deletes from whichever table the registry names — EF for the current table, raw SQL for archives (there is no entity type mapped to Orders_v1, so EF cannot help there) — and removes the registry row.

Two caveats, recorded plainly. First, the writer identifies entities by reflecting on a public Guid Id property and throws otherwise — a convention the whole EF package shares (the registry PK, archive deletes, and ON CONFLICT ("Id") all assume it). Composite or non-Guid keys are outside the current design. Second, the options class exposes a WriteThrough flag and the writer never reads it: write-through updates are currently always on. The flag is an intention — opt-out for callers who want archive rows immutable until drained — that has not grown its implementation yet. If you set it expecting behaviour to change, it will not.

Reads fall back, queries span, writes promote. What remains is the janitor: the background worker that drains archive tables row by row until FullyDrained flips true and the old era can be dropped — without ever losing a row to the races all this concurrency implies.

Next: Draining the Archive Without Losing a Row — the EF backfill worker and its optimistic concurrency choreography.