EF Core Audit Extension - now with new features and optimizations ✨

Some time ago, I published something called EF Core Audit Extension. As the name suggests, it was built to extend Entity Framework with entity auditing (a.k.a. change tracking) capabilities. If you haven't seen it, here's the announcement post:

I've been using it for some time now and am generally satisfied with the solution. However, as my audit tables grew, filtering records became slower and slower. That was, of course, due to the lack of an index on the tables.

When I finally managed to carve out some free time, I sat down and managed to fix this once and for all. But then I fell victim to feature creep, and once I did all I wanted, .NET and, therefore, EF Core 7 was published, so there was even more work to be done. But here we are.

My first draft for this post included the following statement:

So what has changed? Not much, really.

Well, that's not really true anymore - a lot has changed since then.

The changes are (in chronological order):

An index on the column representing the audited entity's primary key can be created (via migrations).
The three triggers (1 for each operation type) are replaced by one, handling all operations.
The trigger SQL no longer uses a cursor to iterate over the changed rows.
The size of the OldData and NewData columns in the audit table can be specified.
The audited entity can now have a composite primary key.
It does not matter where the IsAudited() call is placed when configuring an entity anymore.
Entity Framework Core 7 is now supported.

Unfortunately, due to the scope of the changes, the new release is not backwards-compatible, sorry.

Here's the repository with the updated code:

The following sections will contain some more details on the changes.

Composite primary keys

In the past, the audited entity could not have a composite primary key - only a key consisting of one column was supported. This decision was made due to the (assumed) increased complexity of the trigger code. In the end, it turned out that the complexity was not that much higher, and it was bugging me out since the beginning, so I'm really glad this is now done properly.

Index

The audit table contains columns which make up the audited entity's primary key. Now, as the number of rows in the audit table grows, it becomes increasingly difficult to find records related to a particular entity. By creating an index on the columns, the querying should be less painful.

There are two settings available on the AuditOptions class that can influence the trigger:

AuditOptions.AuditedEntityKeyOptions.Index - a boolean which tells the extension whether to create an index on the key column. If the KeySelector is not specified, then the Index options will default to true, as the extension will use the audited entity's primary key, and those usually benefit from being indexed. On the other hand, if the KeySelector is set, then the default value for the Index property is false.
AuditOptions.AuditedEntityKeyOptions.IndexName - you can probably guess what it does - by setting it, you set the name of the index. If not set, the name will follow Entity Framework's default convention for indexes - IX_{TableName}_{Column1Name}_{Column2Name}.

Trigger SQL changes

This is probably the most significant change. The trigger SQL code was revamped. Three triggers were merged into one. But most importantly, the trigger no longer uses cursors to iterate over the changed rows. Don't ask me why it was there in the first place - I honestly can't remember. But they're gone and let's pretend it has always been like this.

As with the index, there are a couple of ways to customize the trigger:

AuditOptions.AuditTriggerOptions.NameFormat - the name format of the trigger, defaults to Audit__{AuditedEntityTableName}_{AuditTableName]}.
AuditOptions.AuditTriggerOptions.UpdateOptimisationThreshold - this one requires a bit of an introduction, but please bear with me. Logging UPDATE statements is by far the most annoying thing. You need to join the Inserted and Deleted virtual tables to get all the information about an operation. When the operation row count is small, it may seem to be working okay. But then, when you suddenly update a lot of records at once, things will slow down A LOT. To alleviate this, table variables can be introduced with indexes on the columns used in the join statement. That speeds things up massively, but of course, comes with its own overhead. This setting decides the minimum row count to use the table variable method. Below this value, a simple join between Inserted and Deleted will be performed.
Note 1: This is written from SQL Server perspective.
Note 2: This DBA Stack Exchange answer and this blog post from Dave Britten, in particular, were very helpful when figuring this out:

Writing Well-Behaved Triggers

Dave

AuditOptions.AuditTriggerOptions.NoKeyChanges - if you can assure that a record's primary key will never change, setting this option to true can result in slightly better performance when handling UPDATE statements. By default (false), a FULL OUTER JOIN will be performed between the Inserted and Deleted tables. This is to ensure that no update is missing from the audit table, which would be the case when a primary key is changed. If set to true, an INNER JOIN will be used instead, as well as a few COALESCE() calls will be skipped.

Apart from the two links above, these three links were extremely helpful in making the trigger code better (random order):

How to Get JSON of each row of the Table

I am facing an issue getting the JSON of each row.I have the table which contains OrderId and TotalCost.I need to get the table data for OrderID and TotalCost with JsonData.Can someone please he...

Stack Overflowpreeti

Improved IsAudited() behaviour

The heading might be a bit misleading since this is more of a bug fix, but improvement sounds much better.

So after some time, I realized this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{        
    modelBuilder.Entity<Product>().IsAudited();
    modelBuilder.Entity<Product>().ToTable("FunnyTableName");
}

Worked differently to this:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{        
    modelBuilder.Entity<Product>().ToTable("FunnyTableName");
    modelBuilder.Entity<Product>().IsAudited();
}

What's more, the first case would result in an error when trying to apply the migrations. That's because when the IsAudited extension method was called, it took the information that was available at the time. For example, it would assume that the trigger needed to be created on the Products table and not on the FunnyTableName table. The second case would be fine because the updated name was already available by then.

That's a big whoops, but it's now fixed, and it no longer matters where the IsAudited call is located in the entity configuration. If you are curious about the fix, see this commit.

EF Core 7 support

Since EF Core 7 had come out before I had the chance to write and publish this post, I went ahead and added support for it. It is available on the ef7 branch. Besides some updates to the internal APIs, the biggest point was addressing this breaking change: SQL Server tables with triggers or certain computed columns now require special EF Core configuration. The extension does the "special configuration" for you inside the IsAudited call.

One addition to EF Core 7 proved particularly useful: Detect when tools are running. This allows the extensions to skip some of its work when your application is running normally.

Cover photo by Markus Spiske on Unsplash