Audit Trails in EF Core

Introduction

Audit trails is a common feature in database systems. Essentially, it's about knowing, for every table (or some selected ones), who created/updated each record, and when the creates/updates happened. It can also be useful to store the previous values too. Some regulations, such as SOX, require this for compliance.

There are some libraries that can do this automatically in EF Core, and there's also the Change Data Capture in SQL Server (and possibly other databases too), of which I wrote about some time ago, but, hey, I wanted to write my own, so here is EFAuditable, my library for doing audit trails in EF Core.

This one is designed with some extensibility in mind, so, please, read on.

Scenarios

EFAuditable allows the following scenarios:

• Entities marked for auditing will have the audit columns added and persisted automatically to the database, on the same table
• Entities marked for history keeping will have their old values (in the case of updates or deletes) persisted to the database, to a separate table

Marking an Entity for Audit

Auditing means that for each insert or update of that entity, some audit columns will be filled out for us automatically. It needs first to be enabled globally, usually from a call to AddAudit() inside DbContext.OnConfiguring, or AddDbContext, if we are configuring our context from the outside:

optionsBuilder.AddAudit();

There are three ways to mark an entity class as auditable:

1. Have it implement the IAuditable interface
2. Add an [Auditable] attribute to the entity
3. Use code

For option #1, here is how it works:

public class MyEntity : IAuditable { }

The IAuditable interface looks like this:

public interface IAuditable

{

    string? CreatedBy { get; }

    DateTimeOffset? CreatedAt { get; }

    string? UpdatedBy { get; }

    DateTimeOffset? UpdatedAt { get; }

}

The interface itself specifies the properties to be read-only, but we have to define them with a getter, which can be made private.

The meaning of the properties should be clear, but here it is:

• CreatedBy/CreatedAt: the user who created the record and the UTC timestamp when it was created
• UpdatedBy/UpdatedAt: the same for updates

As for #2, the attribute option:

[Auditable]

public class MyEntity { }

Here we do not have "physical" properties for the backing columns, we will be using shadow properties, which means that the audit properties won't be (easily) visible in the entity.

Final option, #3, is to use code. We need to add something like this to our overriden DbContext.OnModelCreating:

protected override void OnModelCreating(DbModelBuilder modelBuilder)

{

    modelBuilder.Entity<MyEntity>().Audit();

    base.OnModelCreating(modelBuilder);

}

The Audit() extension method takes an optional parameter audit, with a default of true:

modelBuilder.Entity<MyEntity>().Audit(audit: true);

Time and Identity Providers

So, by now you might be wondering where the values for the audit properties come from? Well, EFAuditable uses the concept of providers for time and identity.

For time, it's actually a class that derives from TimeProvider, the built-in .NET abstraction for getting the current time. If one is not specified, it defaults to TimeProvider.System.

As for identity, it's somewhat trickier, because console and web apps store the current identity in different ways, both of which are supported (ConsoleIdentityProvider gets the current identity from Thread.CurrentPrincipal and WebIdentityProvider, from HttpContext.User). You can, of course, roll out your own provider, for example, by deriving from CustomIdentityProvider and supplying the class:

public MyIdentityProvider(IContainerEngine engine) : CustomIdentityProvider

{

    public override string GetCurrentUser() => engine.GetCurrentUser();

}

Here, of course, IContainerEngine is just some hypothetical class that somehow knows who we are.

Many ways to register the providers:

• Through a call to AddAudit() inside DbContext.OnConfiguring, or AddDbContext, and providing the timeProvider and identityProvider parameters:

optionsBuilder.AddAudit(new MyIdentityProvider(), TimeProvider.System);

• By registering directly on the Dependency Injection (DI) framework, using AddAuditableIdentityProvider() and/or AddAuditableTimeProvider() (many overloads):

builder.Services.AddAuditableIdentityProvider<MyIdentityProvider>();

builder.Services.AddAuditableTimeProvider<MyFakeTimeProvider>();

As mentioned, the default for the time provider is TimeProvider.System, and for the identity provider, it will be automatically figured out, it will either be ConsoleIdentityProvider or WebIdentityProvider.

Marking an Entity for History

History is a different thing, we first need to enable it globally, and, then, for each entity that we want to keep history for.

To enable history globally, we call AddAudit() inside DbContext.OnConfiguring with the History option set to true (the default is false):

optionsBuilder.AddAudit(static options =>

{

    options.History = true;

});

If we so want to, we can also specify the name for the table that will hold the history (default is AuditableHistory):

optionsBuilder.AddAudit(static options =>

{

    options.History = true;

    options.HistoryTableName = "_history";

});

And, for each entity, we also have a couple options:

• Using the [Auditable] attribute introduced earlier, with the History property set to true (default is false):

[Auditable(History = true)]

public class MyEntity { }

• Using code, in our DbContext.OnModelCreating override:

modelBuilder.Entity<MyEntity>().Audit(history: true);

What's in the History

I hear you ask: so, what exactly is stored in the history? The AuditableHistory record looks like this:

public record AuditableHistory

{

    public int Id { get; set; }

    public required string Key { get; set; }

    public required string Entity { get; set; }

    public required string Values { get; set; }

    public DateTimeOffset Timestamp { get; set; }

    public required string User { get; set; }

    public required string State { get; set; }

}

The meaning of each property is:

• Id: the primary key for the table (not usually relevant)
• Key: the key, or keys, for the record that was modified or deleted, in JSON format (by default)
• Entity: the name of the entity that the history record relates to
• Values: the original values of the modified/deleted record, in JSON format (by default)
• Timestamp: the timestamp of the modification/deletion of the related record, coming from the registered time provider
• User: the user who made the change, also from the identity provider
• State: it can either be Modified or Deleted

Also, the properties that get serialised into Values can be turned off. By default, it's all of the scalar (not relations or collections) of the entity, except the auditing properties (CreatedBy, CreatedAt, UpdatedBy, and UpdatedAt). The way to have a property ignored is through the usage of the IgnoreAuditFor extension method, inside DbContext.OnModelCreating:

modelBuilder.Entity<MyEntity>().Audit().IgnoreAuditFor(x => x.SomeSensitiveProperty);

Properties Serialisation

Since I'm at it, it's worth mentioning that the serialisation (which, by the way, doesn't really have to be JSON, it's just the default) can be controlled through an implementation of IAuditableSerializer. This interface looks like this:

public interface IAuditableSerializer

{

    string Serialize(object entity, params string[] ignoreProperties);

}

The only included serialiser, JsonAuditableSerializer, uses System.Text.Json, but you can change it to another one by registering to the DI through AddAuditableSerializer:

builder.Services.AddAuditableSerializer<MyCustomSerializer>();

Getting History Records

It's also possible to query existing history records, this can be achieved through the Audits() extension method over DbContext:

var audits = ctx.Audits().Where(x => x.State == "Modified").ToList();

This method returns an IQueryable<AuditHistory>, so you can add your own filters, conditions, projections, etc.

Wrap Up

So, to finalise, here's what you need to do to add auditing to your entities:

1. Register your time, identity, and serialisation providers with DI (optional)
2. Call AddAudit() in your DbContext.OnConfiguring method (required)
3. Call AddAudit() in your DbContext.ConfigureConventions method (required)
4. Mark or otherwise identify your entities for auditing and possibly history, either by applying to them the IAuditable interface, the [Auditable] attribute, or through code (required)
5. Mark some properties as to be ignored when storing the old/deleted values by calling IgnoreAuditFor (optional)

And that's it.

Conclusion

The code appears to be working without issues, and, as always, I hope you find it useful. This is a work in progress, I will probably keep looking at other similar libraries and be adding some of its features, so consider this a version 1.0. Code is available in GitHub and a Nuget package is available too.

GitHub: https://github.com/rjperes/EFAuditable

Nuget: https://www.nuget.org/packages/EFAuditable