An audit trail with EF Core interceptors: who changed what, and when

Short answer: mark sensitive entities with an IAudited interface, and add one SaveChangesInterceptor that, just before saving, writes an AuditEntry row for every added, modified or deleted entity: the changed properties with old and new values, the user, the time and the correlation id. Because it is added to the same SaveChanges, the audit row commits or rolls back together with the change.

A real example: the premium that changed by itself

A customer complains that their policy premium went up between two statements. The Policies table shows the current value, nothing else. Was it a pricing job, an operator in the back office, or a bug? Three teams spend a day comparing backups.

With an audit trail, one query answers it: the premium changed from 18,400,000 to 21,100,000 Rials on Tuesday at 10:42, by user ops.ahmadi, in request 3b9e.... The conversation moves from "what happened" to "was it right", which is the question that matters.

Runnable code

The audit table:

public interface IAudited { }

public sealed class AuditEntry
{
    public long Id { get; set; }
    public string Entity { get; set; } = "";
    public string EntityId { get; set; } = "";
    public string Action { get; set; } = "";      // Added, Modified, Deleted
    public string Changes { get; set; } = "";     // JSON: { "Status": { "Old": 1, "New": 2 } }
    public string? UserId { get; set; }
    public string? CorrelationId { get; set; }
    public DateTimeOffset AtUtc { get; set; }
}

The interceptor:

using System.Diagnostics;
using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;

public sealed class AuditInterceptor(ICurrentUser user, TimeProvider clock) : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        if (eventData.Context is AppDbContext db)
            AddAuditEntries(db);

        return base.SavingChangesAsync(eventData, result, cancellationToken);
    }

    private void AddAuditEntries(AppDbContext db)
    {
        var now = clock.GetUtcNow();
        var changed = db.ChangeTracker.Entries()
            .Where(e => e.Entity is IAudited &&
                        e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted)
            .ToList(); // materialise before adding AuditEntry rows

        foreach (var entry in changed)
        {
            db.AuditEntries.Add(new AuditEntry
            {
                Entity = entry.Metadata.ClrType.Name,
                EntityId = KeyOf(entry),
                Action = entry.State.ToString(),
                Changes = JsonSerializer.Serialize(ChangesOf(entry)),
                UserId = user.Id,
                CorrelationId = Activity.Current?.TraceId.ToString(),
                AtUtc = now,
            });
        }
    }

    private static string KeyOf(EntityEntry entry) => string.Join(",",
        entry.Properties.Where(p => p.Metadata.IsPrimaryKey()).Select(p => p.CurrentValue));

    private static Dictionary<string, object?> ChangesOf(EntityEntry entry) => entry.Properties
        .Where(p => entry.State != EntityState.Modified || p.IsModified)
        .ToDictionary(
            p => p.Metadata.Name,
            p => (object?)new
            {
                Old = entry.State == EntityState.Added ? null : p.OriginalValue,
                New = entry.State == EntityState.Deleted ? null : p.CurrentValue,
            });
}

Registration:

builder.Services.AddSingleton(TimeProvider.System);
builder.Services.AddScoped<ICurrentUser, HttpCurrentUser>();
builder.Services.AddScoped<AuditInterceptor>();

builder.Services.AddDbContext<AppDbContext>((sp, o) => o
    .UseSqlServer(builder.Configuration.GetConnectionString("Default"))
    .AddInterceptors(sp.GetRequiredService<AuditInterceptor>()));

Now public class Policy : IAudited is all it takes to audit an entity.

Practical notes

  • Same transaction, on purpose. Audit rows are added before the save, so a failed save leaves no audit row, and a successful one always has it.
  • Generated keys. For new rows with identity keys, the id is temporary at this point. Prefer Guid.CreateVersion7() keys for audited entities, or write the key in SavedChangesAsync.
  • Bulk operations bypass it. ExecuteUpdate, ExecuteDelete and raw SQL don't go through the change tracker. Audit those paths explicitly, or don't use them on audited tables.
  • Mask what you must. Don't store full national IDs, card numbers or tokens in Changes. Exclude or mask those properties.
  • Protect the table. Give the application login INSERT and SELECT only on AuditEntries, so an audit trail can't be edited by the code it audits.

Pros and cons

Pros

  • Answers "who, what, when" with one query, for support, compliance and debugging.
  • Zero changes in business code: one interface on each audited entity.
  • Atomic with the change, so the trail is never ahead of or behind the data.

Cons

  • Bulk updates and raw SQL are invisible to it.
  • The table grows quickly on busy entities; plan retention and archiving.
  • JSON changes are harder to report on than typed columns; temporal tables are better for point-in-time history.

Need an audit trail that would stand up to a compliance review? See how I can help.

Frequently asked questions

Why not use SQL Server temporal tables instead?

Temporal tables are great for "what did this row look like on date X" and need no application code. They don't record who made the change or why, unless you add those columns yourself. Many teams use both: temporal tables for history, an audit table for user and request context.

Will the audit table slow down writes?

Slightly: each audited save inserts extra rows in the same transaction. For typical business entities this is negligible. Don't audit high-volume technical tables like logs or outbox messages, and keep the audit table insert-only with a clustered key on Id.

How do I handle entities whose id is generated by the database?

For added entities with identity keys, the id is still temporary in SavingChanges. Either use client-generated keys such as Guid.CreateVersion7(), or record the key in SavedChangesAsync after the insert, at the cost of a second save.