How to Query SQL Server Temporal Tables with EF Core 10 and ASP.NET Core (2026)

When a customer disputes the price of a product last Tuesday, the current row cannot answer the question. A SQL Server system-versioned temporal table keeps earlier row versions automatically. EF Core 10 can query those versions as a timeline or reconstruct the row at a specific UTC instant. This lab builds both endpoints and shows where temporal history stops short of a business audit trail.

In this guide

1. What does a temporal table record?
2. Create the EF Core and SQL Server lab
3. Query an exact instant or inspect a version timeline
4. Plan permissions, retention and migration
5. Review an existing table before making it temporal
6. References

What does a temporal table record?

SQL Server keeps the current row in the application table and moves its previous image into a history table when it is updated or deleted. The two period columns describe when each version was valid. SQL Server supplies their values. EF Core exposes them as shadow properties, so the C# entity does not need mutable audit timestamps.

SQL Server preserves old prices in a history table while EF Core queries an instant or version history.

Mechanism	Question it answers	Information to supply separately
Temporal table	What values existed at this instant?	Actor, reason, business operation
Business audit record	Who approved this change and why?	Complete before-image unless recorded
Transactional outbox	Which integration event should be sent?	Past values unless included in the event

The clock used for periods is UTC, and the time is tied to the transaction’s begin time. Avoid treating the period columns as a commit order for a domain event stream. Multiple changes to a row in a transaction can produce zero-duration versions, which temporal queries do not return.

Create the EF Core and SQL Server lab

Use .NET 10, a disposable SQL Server database that supports temporal tables, and a login that may create the local schema. Create an empty web project and put the file below in its root as Program.cs. The project uses only EF Core’s SQL Server provider at runtime.

BASH

dotnet new web -n TemporalCatalog -f net10.0
cd TemporalCatalog
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version '10.0.*'
dotnet user-secrets init

Set ConnectionStrings:History in user secrets or ConnectionStrings__History in the local environment. The connection string must target a new database. Use your real hostname and a valid TLS certificate. Enable Development only for the local run. The sample initializes with EnsureCreated, which is convenient for a disposable database. Use a reviewed EF migration for a deployment, and avoid combining EnsureCreated with the migration history on one database.

Program.cs

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddDbContext<HistoryDb>(o => o.UseSqlServer(
    builder.Configuration.GetConnectionString("History")
        ?? throw new InvalidOperationException("Set ConnectionStrings__History.")));
var app = builder.Build();
if (app.Environment.IsDevelopment())
{
    await using var scope = app.Services.CreateAsyncScope();
    await scope.ServiceProvider.GetRequiredService<HistoryDb>().Database.EnsureCreatedAsync();
}
app.MapPost("/products", async (PriceInput input, HistoryDb db, CancellationToken ct) =>
{
    if (!Valid(input.Price)) return Results.BadRequest();
    var item = new Product { Price = input.Price };
    db.Products.Add(item);
    await db.SaveChangesAsync(ct);
    return Results.Created($"/products/{item.Id}/history", new { item.Id, item.Price });
});
app.MapPut("/products/{id:int}/price", async (int id, PriceInput input,
    HistoryDb db, CancellationToken ct) =>
{
    if (!Valid(input.Price)) return Results.BadRequest();
    var item = await db.Products.FindAsync([id], ct);
    if (item is null) return Results.NotFound();
    item.Price = input.Price;
    await db.SaveChangesAsync(ct);
    return Results.NoContent();
});
app.MapGet("/products/{id:int}/history", async (int id, DateTimeOffset? at,
    HistoryDb db, CancellationToken ct) =>
{
    if (at is { } when)
    {
        var value = await db.Products.TemporalAsOf(when.UtcDateTime).Where(p => p.Id == id)
            .Select(p => new { p.Id, p.Price }).SingleOrDefaultAsync(ct);
        return value is null ? Results.NotFound() : Results.Ok(value);
    }
    var versions = await db.Products.TemporalAll().Where(p => p.Id == id)
        .OrderByDescending(p => EF.Property<DateTime>(p, "ValidFrom")).Take(50)
        .Select(p => new
        {
            p.Id, p.Price,
            fromUtc = EF.Property<DateTime>(p, "ValidFrom"),
            toUtc = EF.Property<DateTime>(p, "ValidTo")
        }).ToListAsync(ct);
    return Results.Ok(versions);
});
app.Run();

static bool Valid(decimal price) => price is >= 0 and <= 9999999.99m &&
    decimal.Round(price, 2) == price;
record PriceInput(decimal Price);
sealed class Product
{
    public int Id { get; set; }
    public decimal Price { get; set; }
}
sealed class HistoryDb(DbContextOptions<HistoryDb> options) : DbContext(options)
{
    public DbSet<Product> Products => Set<Product>();
    protected override void OnModelCreating(ModelBuilder model)
    {
        model.Entity<Product>().ToTable("Products", table => table.IsTemporal(period =>
        {
            period.HasPeriodStart("ValidFrom");
            period.HasPeriodEnd("ValidTo");
            period.UseHistoryTable("ProductPriceHistory");
        }));
        model.Entity<Product>().Property(x => x.Price).HasPrecision(12, 2);
    }
}

Query an exact instant or inspect a version timeline

Create a product with a price of 12.50 and save the returned identifier. Record a UTC timestamp from the database with SELECT SYSUTCDATETIME(); after the first call completes. Then send the price update and read both views. Capturing the instant before the update avoids relying on two calls landing on different ticks.

BASH

curl -i http://localhost:5067/products -H 'Content-Type: application/json' \
  -d '{"price":12.50}'
curl -i -X PUT http://localhost:5067/products/1/price \
  -H 'Content-Type: application/json' -d '{"price":15.75}'
curl -s http://localhost:5067/products/1/history
curl -sG http://localhost:5067/products/1/history \
  --data-urlencode 'at=REPLACE_WITH_CAPTURED_UTC_INSTANT'

Run the project with ASPNETCORE_ENVIRONMENT=Development dotnet run --no-launch-profile --urls http://localhost:5067. A successful write returns 204. The timeline should include the original and current price if the value actually changed. A point-in-time request before the row existed returns 404. The historical query is read-only. If a business process restores an older price, read the value and apply it to the current row through a separately authorized command. That restore is itself a new version.

For direct SQL inspection use a parameterized FOR SYSTEM_TIME AS OF @instant against the current table, or filter the FOR SYSTEM_TIME ALL results by product. Verify plan shape and I/O for realistic histories. The lab caps the timeline at 50 versions, and larger histories need an explicit pagination and retention policy.

Plan permissions, retention and migration

Temporal tables preserve old values after a current-row delete. Avoid storing a secret in a temporal entity unless your governance explicitly permits it. Grant access to history based on its own sensitivity. Publish history APIs only with authentication, row-level authorization, and rate limits. The code is intentionally a loopback lab.

For an existing table, review the generated DDL and check schema locks, data growth and backup size in staging. Document retention and what happens when a customer requests erasure. A system-versioning operation affects both the current and history tables. Restore procedures should account for both. Keep business actor and correlation IDs in an independent audit model when they are required.

Why does my history contain only one row?

Check that the mapped table is system-versioned and that the update changed a tracked value. EF may issue no update for a no-op. Check which database the application is connected to and avoid reusing a database created with an older model.

Can I query all entities at a past time by joining one temporal table?

Each participating table needs its own appropriate history semantics. A join with a current-only table can mix data from different times. Decide whether the question requires past values, present relationships, or both.

Review an existing table before making it temporal

Start by inventorying the table’s primary key, foreign keys, row volume, update frequency and current retention rules. A price that changes once a month may need little history storage. A job that updates every product every hour can produce substantial history even when the business value barely changes. Measure the actual write pattern rather than forecasting from the row count alone.

Use an EF migration to add the period and link the history table. In a disposable database, inspect the generated statements before applying them. On a production-sized copy, measure lock waits and log growth. Confirm that backups include both current and historical rows, that operators know how to find the history table, and that the application role has only the permissions its API requires. Add a test that updates a row twice and checks a timestamp between the updates. Record the timestamp using the same database clock used for the period, and keep the two updates in separate transactions.

A schema rollback and data rollback have different effects. Disabling system versioning stops automatic history recording until it is enabled again. Restoring an old price through the API creates another version. Plan how to retain period metadata if you have to change the schema, and document the distinction for operators.

Takeaway. Use the temporal API to answer what a row contained and when. Record the business reason and responsible actor separately if your system needs to explain the change.

References

• EF Core temporal tables
• Query temporal data in SQL Server
• EF Core source
• ASP.NET PB SQL Server CRUD walkthrough