EF Core Soft Delete vs Temporal Tables vs Audit Trail: Which Data History Strategy Should Your .NET Team Use in 2026?

When your enterprise .NET application needs to answer questions like "Who deleted this record?", "What did this order look like three days ago?", or "Which field changed and when?" — you're facing a data history problem. EF Core gives you at least three distinct approaches: soft delete, temporal tables, and custom audit trails. Each solves a different piece of the puzzle, and choosing the wrong one leads to bloated schemas, missing history, or compliance failures at the worst possible time.

🎁 Want implementation-ready .NET source code you can drop straight into your project? Join Coding Droplets on Patreon for exclusive tutorials, premium code samples, and early access to new content. 👉 https://www.patreon.com/CodingDroplets

What Problem Does Each Strategy Actually Solve?

Before comparing mechanics, it helps to be clear about what each approach is designed for:

• Soft delete answers: "Is this record logically gone, and can we recover it?"
• Temporal tables answer: "What did this row look like at any point in time?"
• Audit trails answer: "Who changed what field, from what value, to what value, and why?"

These are overlapping but not identical questions. The mistake most teams make is picking one approach and expecting it to answer all three.

Soft Delete in EF Core: Overview

Soft delete is the pattern of setting an IsDeleted flag (and often a DeletedAt timestamp) instead of issuing a SQL DELETE. EF Core's Global Query Filters make this straightforward: you define a filter on your DbContext that automatically appends WHERE IsDeleted = 0 to every query for the filtered entity.

This pattern is well-suited to scenarios where:

• The application UI needs a "recycle bin" or undo-delete capability
• Foreign key constraints prevent hard deletes
• Your business logic depends on whether an entity is "active"
• You need to filter deleted records from standard queries without modifying every LINQ expression in your codebase

Soft Delete Trade-Offs

Advantages:

• Simple to implement — just a boolean flag and a global query filter
• Native EF Core support via HasQueryFilter()
• Works with any database provider (SQL Server, PostgreSQL, SQLite, MySQL)
• Low infrastructure overhead — no extra tables, no SQL Server features required

Disadvantages:

• Pollutes every table with IsDeleted and timestamp columns
• Unique constraints become complex — you must include IsDeleted in constraint definitions
• ExecuteDeleteAsync and ExecuteUpdateAsync (bulk operations introduced in EF Core 7) bypass the change tracker and Global Query Filters entirely — they'll hard-delete rows or update soft-deleted rows unless you explicitly filter them
• Does not track what changed — only whether the row is logically deleted
• Does not capture field-level change history

When Soft Delete Falls Short

Soft delete alone is not an audit strategy. It records that a record was deleted, but not who deleted it, what the row looked like before deletion, or what changed during the record's lifetime. For compliance requirements (GDPR, HIPAA, SOX), soft delete is necessary but not sufficient.

Temporal Tables in EF Core: Overview

SQL Server Temporal Tables (System-Versioned Tables, introduced in SQL Server 2016 and supported since EF Core 6) automatically maintain a parallel history table that stores every version of each row with PeriodStart and PeriodEnd timestamps managed entirely by the database engine.

EF Core maps temporal tables via IsTemporal() in your model configuration. Once enabled, the database engine silently writes every INSERT, UPDATE, and DELETE to the history table — no application code changes required for history capture.

You can then query historical data using TemporalAsOf(DateTime point), TemporalBetween(), TemporalFromTo(), and TemporalContainedIn() — all surfaced as LINQ extension methods on your DbSet<T>.

Temporal Tables Trade-Offs

Advantages:

• Zero application code for history capture — the database handles it automatically
• Full row-level history at any point in time — great for point-in-time recovery
• Works with EF Core's LINQ integration — AsOf() queries are strongly typed
• No risk of bypassing history capture via ExecuteDeleteAsync — the DB engine always captures the change
• Excellent for time-travel queries ("show me the state of all orders as of last Tuesday")

Disadvantages:

• SQL Server and Azure SQL only — no PostgreSQL, MySQL, or SQLite support
• Does not capture who made the change — only what changed and when
• The history table grows unbounded — you need a retention policy
• Adds storage overhead on every table you enable it for
• Schema changes (adding/removing columns) require carefully managed migrations
• EF Core ExecuteDeleteAsync issues a hard delete — but SQL Server still captures the row in the history table before deletion, so the history is preserved even if IsDeleted isn't

What Temporal Tables Cannot Do

Temporal tables capture the database state, but they have no awareness of the application context. They cannot record the authenticated user who made the change, the HTTP request ID, the business reason for the modification, or whether the change was part of a specific workflow step. For regulatory compliance scenarios that require who and why, you need an audit trail layer on top.

Custom Audit Trail in EF Core: Overview

A custom audit trail logs field-level changes to a dedicated AuditLogs table, capturing the entity name, changed properties, old values, new values, the acting user, timestamp, and optionally a correlation ID or reason. This is typically implemented via an ISaveChangesInterceptor (EF Core 7+) or by overriding SaveChangesAsync on the DbContext.

The interceptor pattern hooks into EF Core's change tracker before each save, inspects all Modified, Added, and Deleted entries, serialises before/after values (usually as JSON), and writes an audit record alongside the data change — within the same database transaction.

Audit Trail Trade-Offs

Advantages:

• Captures who, what, when, and optionally why — the full compliance picture
• Works with any database provider — no SQL Server dependency
• Flexible schema — you can store JSON blobs, separate property-level rows, or a hybrid
• Can include application-level context (user ID, request ID, IP address)
• Survives table schema changes more gracefully than temporal history tables

Disadvantages:

• Requires application code — the interceptor must be carefully maintained
• ExecuteUpdateAsync and ExecuteDeleteAsync bypass SaveChanges — you must never use bulk operations on audited entities without supplementary logging
• Audit tables can grow large — you need archival and retention strategies
• Serialising property values to JSON has edge cases: complex types, shadow properties, and navigation properties need special handling
• The interceptor runs synchronously on the save path — slow serialisation increases overall write latency

Is ISaveChangesInterceptor the Right Hook?

For most teams, yes. ISaveChangesInterceptor (implementing SavingChangesAsync) runs before the data is committed and participates in the same transaction. This means either both the data and the audit record are written, or neither is — no partial audits. Compared to overriding SaveChangesAsync directly on the context, the interceptor pattern is cleaner to register and easier to test in isolation.

Side-by-Side Comparison

Criterion	Soft Delete	Temporal Tables	Audit Trail
History captured	Deletion flag only	Full row history (system clock)	Field-level changes (application-managed)
Who made the change	❌ Not captured	❌ Not captured	✅ Captured
Point-in-time query	❌ Not supported	✅ Native LINQ support	⚠️ Possible but manual
DB provider	Any	SQL Server / Azure SQL only	Any
Bulk op safety	⚠️ Must handle explicitly	✅ DB engine captures always	⚠️ Must handle explicitly
Schema impact	Adds columns to entity tables	Adds system period columns + history table	Adds separate audit tables
Compliance-ready	Partial	Partial	✅ Full (when implemented correctly)
Setup complexity	Low	Medium	Medium–High
Storage cost	Low	Medium–High	Medium

How Do They Combine in Practice?

The most robust enterprise data history architecture layers all three — not as alternatives, but as complementary tools:

• Soft delete for entities that need recoverability and active/inactive status in the application
• Temporal tables for critical data domains (orders, payments, contracts) where point-in-time reconstruction matters and SQL Server is the database of record
• Audit trail for any entity where regulatory compliance requires knowing who changed what

This is not over-engineering. A financial SaaS platform that stores customer invoices, for example, benefits from: soft delete so account managers can "recover" a deleted invoice; temporal tables so support teams can reconstruct exactly what the invoice looked like during a dispute window; and an audit trail so compliance can demonstrate who modified the line items and when.

Is There a Clear Winner?

For general-purpose recoverability in any application: soft delete is the right starting point. It's simple, portable, and well-supported in EF Core.

For time-travel queries and point-in-time recovery on SQL Server: temporal tables are the right choice. The zero-code history capture and native LINQ integration make them a strong fit for financial and legal data domains.

For compliance-driven audit requirements: a custom audit trail via ISaveChangesInterceptor is the only approach that captures application-level context. Nothing else answers "who" and "why."

For most enterprise .NET teams on SQL Server, the recommended default is temporal tables for state history + a targeted audit trail for compliance entities. Soft delete can coexist with both — it operates at the application query layer and is orthogonal to the others. Avoid defaulting to soft delete as your sole data history strategy; it is a recoverability tool, not an audit tool.

☕ Prefer a one-time tip? Buy us a coffee — every bit helps keep the content coming!

Frequently Asked Questions

Can I use soft delete and temporal tables together on the same entity? Yes. Soft delete operates at the EF Core application layer (Global Query Filters and an IsDeleted column), while temporal tables operate at the database engine layer. Setting IsDeleted = true is just an UPDATE in SQL Server's eyes — the temporal history table captures that change automatically. You get the application-level "recycle bin" behaviour from soft delete and the full row history from temporal tables at no extra cost.

Do temporal tables work with PostgreSQL in .NET? No. EF Core's IsTemporal() configuration is SQL Server-specific. PostgreSQL has its own temporal/audit extensions (such as pgaudit and the temporal_tables extension), but EF Core does not provide a provider-agnostic abstraction for these. If you need cross-database temporal history, a custom audit trail is your portable option.

What happens when I call ExecuteDeleteAsync on a soft-deleted entity table? ExecuteDeleteAsync generates a raw SQL DELETE statement that bypasses EF Core's change tracker and Global Query Filters. It will permanently delete rows regardless of their IsDeleted value — unless you explicitly add a .Where(x => x.IsDeleted) filter in your LINQ query before calling it. Always treat bulk operations as filter-aware by convention in your team.

How do I capture the current user inside ISaveChangesInterceptor? Inject IHttpContextAccessor into your interceptor (or a dedicated ICurrentUserService that wraps it). Then in SavingChangesAsync, read httpContextAccessor.HttpContext?.User?.FindFirst(ClaimTypes.NameIdentifier)?.Value to get the authenticated user ID and write it into the audit record. Avoid calling IHttpContextAccessor directly in background service contexts — in that case, pass the user context explicitly through a scoped service.

Will adding temporal tables cause EF Core migrations to become complex? Yes, more than standard migrations. Renaming a column on a temporal table requires disabling system versioning, altering both the main and history tables, then re-enabling versioning. EF Core migrations do not automate this sequence. Microsoft Docs provides the manual T-SQL steps required, and it is worth scripting these in a migration helper for teams that make frequent schema changes to temporally-versioned tables. Reference: Temporal Tables — EF Core | Microsoft Learn

Does the audit trail interceptor affect write performance? It introduces overhead proportional to the number of changed entities per save. For typical transactional writes (1-10 entities per request), the overhead is negligible. For batch imports or high-throughput pipelines that update hundreds of rows per save, you may observe measurable latency increases. In those scenarios, consider bypassing the auditable interceptor explicitly (or use ExecuteUpdateAsync with a separate logging path) and accepting that bulk operations are audited at the batch level rather than the row level.

Should I use a separate database for the audit log? Only if your audit requirements mandate physical separation for tamper-resistance (e.g., financial services regulations that prohibit audit data from being modified by application credentials). For most enterprise systems, writing audit records within the same database transaction is preferable because it guarantees atomicity — the data change and its audit record either both commit or both roll back. A separate audit database introduces a distributed write that can fail independently.