Temporal Tables, JSON Columns, Sparse/Provider Features, and Query Translation

Learning outcomes

1. The problem: history and structured data are database features, not serialization tricks

ServiceHub now needs two SQL Server-specific capabilities: reconstruct a work order as it existed at an earlier time, and persist structured routing/contact details without exploding every nested value into standalone columns. SQL Server temporal tables and EF Core 10 JSON mapping can solve those problems, but they have versioned engine behavior, migration consequences, and query translations that differ from SQLite and older SQL Server versions.

2. Temporal tables externalize row history to SQL Server

A system-versioned temporal table maintains a current table plus a history table. SQL Server generates two period columns and copies old row versions into history on UPDATE/DELETE. EF Core can create/convert temporal tables with migrations and exposes temporal query operators. In the EF Core 10 baseline, period columns are normally shadow properties; mapping period columns to CLR properties is an EF Core 11 capability and is intentionally not used here.

modelBuilder.Entity<SqlServerWorkOrder>()    .ToTable("work_orders", table => table.IsTemporal(temporal =>    {        temporal.HasPeriodStart("ValidFrom");        temporal.HasPeriodEnd("ValidTo");        temporal.UseHistoryTable("work_order_history");    }));

CREATE TABLE [work_orders] (    ...,    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,    [ValidTo]   datetime2 GENERATED ALWAYS AS ROW END NOT NULL,    PERIOD FOR SYSTEM_TIME ([ValidFrom], [ValidTo])) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[work_order_history]));

SQL Server generates temporal period values in UTC. A history table is not an application audit log: it records row versions, not authenticated user intent, request IDs, or business reasons. Keep those concerns separate.

3. Query history with temporal operators, not handcrafted date predicates

TemporalAsOf returns the row version active at a UTC instant. TemporalAll returns all current/history versions. Other operators express interval semantics. Project the period shadow values to make the returned version boundaries observable.

var instant = DateTime.UtcNow.AddMinutes(-30);var snapshot = await db.SqlServerWorkOrders    .TemporalAsOf(instant)    .Where(w => w.WorkOrderNumber == number)    .Select(w => new    {        w.WorkOrderNumber,        w.CustomerName,        ValidFrom = EF.Property<DateTime>(w, "ValidFrom"),        ValidTo = EF.Property<DateTime>(w, "ValidTo")    })    .SingleOrDefaultAsync(ct);

SELECT ...FROM [work_orders] FOR SYSTEM_TIME AS OF @__instant AS [w]WHERE [w].[WorkOrderNumber] = @__number

Use database-generated history for recovery/forensics only after defining retention, access controls, and storage growth. Temporal history can contain sensitive historical values that the current row no longer exposes.

4. EF Core 10 JSON mapping: same model concept, different SQL Server storage capability

EF Core 10 can map complex types to JSON. On SQL Server 2025 at compatibility level 170+ and on Azure SQL, the provider defaults to SQL Server's native json type. With lower compatibility or older servers, JSON is stored in a text column such as nvarchar(max). The C# complex-property API can look similar while storage, translation, and available indexing differ.

public sealed record DispatchDetails(    string Region,    int ServiceTier,    string? EscalationCode,    DispatchAddress Address);public sealed record DispatchAddress(string City, string PostalCode);public sealed partial class SqlServerWorkOrder{    public DispatchDetails Dispatch { get; private set; } = null!;    public void ChangeDispatchRegion(string region)        => Dispatch = Dispatch with { Region = region };}modelBuilder.Entity<SqlServerWorkOrder>()    .ComplexProperty(w => w.Dispatch, complex => complex.ToJson());

options.UseSqlServer(connectionString, sql =>    sql.UseCompatibilityLevel(170));

5. Query JSON members and inspect the generated function

A LINQ predicate over a mapped complex property becomes SQL Server JSON access. With the native SQL Server 2025 type, EF Core 10 can use the newer JSON_VALUE(... RETURNING ...) form for typed extraction. Text-backed JSON may require different casting/translation. Treat ToQueryString() as diagnostic text and verify the server plan separately.

var regional = await db.SqlServerWorkOrders    .Where(w => w.Dispatch.ServiceTier >= 3)    .Select(w => new { w.WorkOrderNumber, w.Dispatch.Region, w.Dispatch.Address.City })    .ToListAsync(ct);

SELECT [w].[WorkOrderNumber], ...FROM [work_orders] AS [w]WHERE JSON_VALUE([w].[Dispatch], '$.ServiceTier' RETURNING int) >= 3;

6. Partial JSON updates are write-shape behavior you must observe

Changing one mapped JSON member does not necessarily mean EF rewrites the whole document. The SQL Server provider can generate partial-document updates (for text-backed JSON this commonly uses JSON_MODIFY; native-json SQL may differ by capability/version). Verify the actual command under the exact provider/server compatibility you deploy.

var workOrder = await db.SqlServerWorkOrders.SingleAsync(w => w.Id == id, ct);workOrder.ChangeDispatchRegion("south");await db.SaveChangesAsync(ct);

UPDATE [work_orders]SET [Dispatch] = JSON_MODIFY([Dispatch], 'strict $.Region', JSON_VALUE(@p0, '$[0]'))WHERE [work_order_id] = @p1 AND [Version] = @p2;

Do not hard-code the representative SQL into application logic. The purpose is to prove that the provider generated the intended write shape and retained concurrency protection.

7. Sparse columns are a storage optimization for null-heavy SQL Server columns

SQL Server sparse columns reduce storage for NULL values at the cost of overhead for non-NULL values and have engine restrictions. They can be appropriate for rarely populated provider-specific fields. EF exposes IsSparse(); the decision still belongs to SQL Server schema/workload analysis.

modelBuilder.Entity<SqlServerWorkOrder>()    .Property(w => w.ProviderEscalationNote)    .HasMaxLength(1000)    .IsSparse();

[ProviderEscalationNote] nvarchar(1000) SPARSE NULL

Do not mark every nullable column sparse. Measure null density, row shape, indexes, update patterns, and SQL Server sparse-column restrictions first.

8. Failure case: enabling compatibility 170 creates a non-trivial JSON migration

A team upgrades EF Core and switches the provider configuration from compatibility 160 to 170 without reviewing migrations. EF now sees mapped JSON columns as native json and scaffolds type changes from nvarchar(max). Even when SQL Server supports the conversion, it is a deployment/data compatibility event, not a harmless runtime toggle.

Repair: inspect the migration SQL, test representative documents, confirm dependencies/indexes/functions, stage the change, and keep compatibility below 170 or explicitly map nvarchar(max) if the organization is not ready to adopt native JSON. Record why the compatibility level is pinned.

9. Mandatory lab: build history, JSON and sparse evidence

1. Use a disposable SQL Server 2025 Developer database. Record SELECT @@VERSION and DATABASEPROPERTYEX(DB_NAME(),'Version') plus the database compatibility level.
2. Add temporal mapping to the provider-lab work_orders table and a complex Dispatch JSON property.
3. Set compatibility 170 and scaffold a migration. Confirm temporal DDL and native json column type before applying.
4. Insert one work order, update it twice, then run TemporalAll() and print ValidFrom/ValidTo.
5. Query Dispatch.Region, capture ToQueryString(), and inspect the real command/log.
6. Update only Dispatch.Region; inspect whether the provider emits a partial-document update on your exact server/provider build.
7. Add one sparse nullable lab column and verify sys.columns.is_sparse.
8. Repeat the JSON migration-generation step with compatibility 160 in a fresh throwaway database and compare DDL; do not infer behavior from memory.

10. Production judgment and bridge

Temporal tables are appropriate when database-managed row history is part of the requirement and retention/security are designed. JSON is appropriate when an aggregate substructure belongs together and query/update needs fit SQL Server's JSON capabilities; native JSON requires SQL Server 2025/Azure SQL capability and careful migration review. Sparse columns are physical storage choices, not object-model decorations. The next lesson moves to Azure SQL resiliency, where transient-fault classification and retry scope matter more than any single mapping API.

Authoritative references

Temporal and JSON behavior changes with EF, SQL Server, Azure SQL and compatibility levels; verify all four before production rollout.

• SQL Server/Azure SQL temporal tables - EF Core — temporal configuration, queries, period columns and history
• What's new in EF Core 10 — complex-type JSON mapping and SQL Server 2025 native JSON
• Breaking changes in EF Core 10 — native JSON migration behavior and compatibility mitigation
• Complex types - EF Core — ToJson mapping and value-object semantics
• SQL Server columns - EF Core — sparse-column and UTF-8 provider features
• SQL Server provider - EF Core — compatibility-level and Azure SQL provider guidance