Versions with metadata: History<T>

Who changed this policy, when, and why?

var audit = await db.History<Policy>()
    .Where(v => v.Entity.Id == policyId)
    .Select(v => new { v.ValidFrom, v.ValidTo, v.Operation, v.ChangedBy, v.Reason, v.Entity.Status })
    .ToListAsync();

History<Policy>() returns every stored version wrapped in a Version<Policy>: the entity snapshot plus the version's period and change context. It is an extension on the DbContext, not on a DbSet, so you name the entity type.

member	value
Entity	the Policy as it was in this version
ValidFrom, ValidTo	the half-open period [ValidFrom, ValidTo), as UTC DateTimeOffset
IsCurrent	true for the version that is current now (ValidTo is DateTimeOffset.MaxValue)
Operation	VersionOperation.Insert, Update or Delete
ChangedBy, ChangedByName, CorrelationId, Reason, Extra	the change context, or null
DbSessionUser	PostgreSQL's own session_user (opt-in, see Database session user), or null if the entity did not opt in

Deletes

Unlike AllVersions, History<T> includes tombstones. A delete comes back as a version with Operation == VersionOperation.Delete and an empty period (ValidFrom == ValidTo), carrying the last values and the change context of the delete. That makes it the delete audit:

var deletions = await db.History<Policy>()
    .Where(v => v.Operation == VersionOperation.Delete)
    .Select(v => new { v.Entity.Number, DeletedAt = v.ValidFrom, v.ChangedBy, v.Reason })
    .ToListAsync();

Composing

Rows come back newest first, by ValidFrom descending and then in insertion order; your own OrderBy replaces that. Where and Select translate to SQL through both the metadata members and the snapshot (v.Entity.Id, v.Entity.Status), with no client-side evaluation.

The generated SQL

For a filter on Entity.Status (from Hindsight's test suite):

SELECT p.id, p.metadata, p.number, p.premium, p.status, p.tags, p.valid_from::timestamptz, p.valid_to::timestamptz, p.operation::smallint, p.changed_by, p.changed_by_name, p.correlation_id, p.reason, p.extra
FROM policies_history AS p
WHERE p.status = 'Active'
ORDER BY p.valid_from DESC, p.history_id DESC

To list which properties changed from one version to the next, pass two versions to Diff.

The same rules as for AsOf and AllVersions apply, including to Version<T>.Entity: it is read-only.