Point in time: AsOf

What did this policy look like when the claim was filed?

var policy = await db.Policies
    .AsOf(claim.OccurredAt)
    .SingleOrDefaultAsync(p => p.Id == claim.PolicyId);

AsOf(instant) reads from the history table instead of the main table, and keeps the version whose period contains instant:

valid_from <= @instant AND valid_to > @instant

That is the version the database held at that moment. A policy that did not exist yet at instant, or had already been deleted, returns nothing.

Composing

Everything after AsOf composes as usual and becomes one SQL query:

var largeDrafts = await db.Policies
    .AsOf(endOfLastQuarter)
    .Where(p => p.Status == PolicyStatus.Draft && p.Premium > 10_000m)
    .OrderByDescending(p => p.Premium)
    .Select(p => p.Number)
    .ToListAsync();

The instant is a query parameter, so queries at different instants share one compiled query and one query plan.

The generated SQL

For a filter on Status and a descending order on Number (from Hindsight's test suite):

-- @AsOfUtc='2026-01-01T09:00:00.0000000Z' (DbType = DateTime)
SELECT p.id, p.metadata, p.number, p.premium, p.status, p.tags
FROM policies_history AS p
WHERE p.valid_from <= @AsOfUtc AND p.valid_to > @AsOfUtc AND p.status = 'Active'
ORDER BY p.number DESC

Rules

• AsOf must be the first operator, directly on the DbSet: db.Policies.AsOf(t).Where(…), not db.Policies.Where(…).AsOf(t).
• The result is no-tracking and read-only.
• Include throws.

See Rules for historical queries for the details and the alternatives.