Wow, the article post sounds a bit scary - I hope the content won't be! So a time may come when you want to track what's happening to your entities. The reasons for that may vary - it might be necessary for compliance reasons, or you might just need it for tracking down bugs in the system.

Note: This post will focus solely on Microsoft SQL Server. However, it is possible to apply this to most databases without too much effort.

The first thing to check is the possibilities that the database engine of your choice provides. SQL Server provides a few ways to handle change tracking for you, but none of them are perfect. Some of them are:

• Change Tracking - a lightweight solution that tracks whether a row was changed, but does not give information about what was changed to what, when the change happened or who made the change.
• Change Data Capture - a more robust solution that records the data that was changed. It often might seem like the perfect solution, but has some drawbacks:
  • to get a clear picture of what happened to the data, some serious T-SQL knowledge is required,
  • by default, the information about the user who performed the change or even a simple timestamp is not recorded,
  • when the table schema changes, the functionality needs to be first disabled and re-enabled after the DDL statements finish executing.
• Server Audit - built on top of Extended Event functionality - the biggest downside is that it does not capture full information about rows affected by UPDATE statements, which makes it unsuitable for many cases.

Of course, it is possible to get all the data you need using these, maybe combining them with the transaction log or triggers. However, that requires a DBA who knows what they are doing.

It is important to list all the requirements and then go through all the available options and determine the best solution. I had this problem recently - and these are the requirements I ended up with:

• tracking of INSERT, UPDATE (and MERGE) and DELETE statements
• saving information about:
  • user whose actions triggered the change (application user identifier when available with a fallback to the database user's name)
  • timestamp of the changes
  • what data was inserted/removed and both the old and new data in case of updates
• resilience against table schema changes

As you can see, these are some pretty strict requirements. After going through the available out-of-the-box auditing options, none of them seemed to provide everything needed.

After some googling, I stumbled upon this post:

SQL Server audit logging using triggers - Vlad Mihalcea

Learn how to implement an audit logging mechanism using SQL Server triggers and store the old and new row states in JSON column types.

Vlad Mihalceavladmihalcea

Seemed to tick all of the boxes:

• stores all the information about updated rows
• can store information about context by utilizing SQL Server's SESSION_CONTEXT
• does not break when the table's schema changes

Of course, it had to be supercharged a bit - the trigger defined in that post does not handle multiple-row updates and records misleading information about MERGE statements. But that's nothing that could not be fixed.

Now, because the project was small and I am by no means a database expert, I thought it would be cool if Entity Framework could handle the trigger and audit table creation for me. Sounds fun, right?

Introducing EFCore.AuditExtensions

So I knew how I wanted to track the changes to my entities. And I wanted Entity Framework to create all the infrastructure for me via migrations. That's how this little repository was born:

GitHub - mzwierzchlewski/EFCore.AuditExtensions: An Entity Framework Core 6 extension providing support for auditing entities with migrations support.

An Entity Framework Core 6 extension providing support for auditing entities with migrations support. - GitHub - mzwierzchlewski/EFCore.AuditExtensions: An Entity Framework Core 6 extension providi...

GitHubmzwierzchlewski

Set up

The setup is fairly simple:

1. Add EFCore.AuditExtensions.SqlServer reference to your project.
2. Add the following attribute to your project:

[assembly: DesignTimeServicesReference("EFCore.AuditExtensions.Common.EfCore.DesignTimeServices, EFCore.AuditExtensions.Common")]

C#

Copy
3. Use the .UseSqlServerAudit() extension of the DbContextOptionsBuilder, e.g.:

var host = Host.CreateDefaultBuilder(args)
    .ConfigureServices(
        services =>
        {
            services.AddDbContext<ApplicationDbContext>(
                options =>
                    options.UseSqlServer("<connection-string>")
                           .UseSqlServerAudit());
        }).Build();
123456789

C#

Copy
4. Use the IsAudited(...) extension of the EntityTypeBuilder to select the entities which should be audited:

public class Product
{
    public int ProductId { get; set; }

    public string Description { get; set; }
}

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }
            
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    { }
            
    protected override void OnModelCreating(ModelBuilder modelBuilder) 
        => modelBuilder.Entity<Product>().IsAudited();
}
1234567891011121314151617

C#

Copy
5. Create a migration and update the database.

And that's all 🥳

Demo

Given the configuration shown above, a migration will create the following database objects:

• table Products_Audit with the following columns: ProductId, OldData, NewData, OperationType, User, Timestamp
• trigger Audit__Products_Products_Audit_ProductId_Insert
• trigger Audit__Products_Products_Audit_ProductId_Update
• trigger Audit__Products_Products_Audit_ProductId_Delete

After performing some operations on the Products table, the Products_Audit table will be populated as shown below:

Configuration

The IsAudited(...) extension method allows some customizations through its Action<AuditOptions<T>>? configureOptions parameter.

Audit Table Name

By default, the Audit Table is named using <entity-table-name>_Audit. This can be changed using the AuditOptions.AuditTableName property:

modelBuilder.Entity<Product>().IsAudited(options => options.AuditTableName = "ProductAudit");

Given the ApplicationDbContext shown above, this will change the Audit Table's name from Products_Audit to ProductAudit.

Audited Entity Key Selector

The audited entity should have a simple (composed of one property) primary key. By default, that's what the extension will use. If no such key is found, it will default to another key property. To select a specific property, the AuditedEntityKeySelector option can be used:

modelBuilder.Entity<Product>().IsAudited(
    options =>
    {
        options.AuditedEntityKeySelector = p => p.EAN;
    });

Trigger Name Format

By default, the trigger name will be generated using the following pattern:

{AuditPrefix}_{TableName}_{AuditTableName}_{AuditedEntityTableKeyColumnName}_{OperationType}

This can be changed using the AuditTriggerNameFormat option:

modelBuilder.Entity<Product>().IsAudited(options => options.AuditTriggerNameFormat = "TRIGGER_{TableName}_{OperationType}");

The above configuration would change the trigger name from Audit__Products_Products_Audit_ProductId_Insert to TRIGGER_Products_Insert.

User Provider

By default, the User column will be populated with the database user's name (with [db] postfix, e.g. sa [db]). In many cases that will not be enough. To provide more meaningful user information, implement the IUserProvider interface:

public class UserProvider : IUserProvider
{
    private readonly IHttpContextAccessor _httpContext;

    public UserProvider(IHttpContextAccessor httpContext)
    {
        _httpContext = httpContext;
    }

    public string GetCurrentUser() => _httpContext.HttpContext?.User.GetUserId() ?? "Anonymous";
}

And use the UseSqlServerAudit<TUserProvider> overload on DbContextOptionsBuilder:

var host = Host.CreateDefaultBuilder(args)
               .ConfigureServices(
                   services =>
                   {
                       services.AddDbContext<ApplicationDbContext>(
                           options =>
                               options.UseSqlServer("<connection-string>")
                                      .UseSqlServerAudit<UserProvider>());
                   }).Build();

Adding support for other database engines

As mentioned before, only SQL Server is supported by default. However, without too much effort, you can add support for other database engines. At least I tried to make it so!

These are the things that must be implemented in order to make this work with other databases.

ICreateAuditTriggerSqlGenerator implementation

This interface contains just one method:

void Generate(CreateAuditTriggerOperation operation, MigrationCommandListBuilder builder, IRelationalTypeMappingSource typeMappingSource);

As the name suggests - it is responsible for producing the SQL that creates the trigger based on the parameters passed as the CreateAuditTriggerOperation operation parameter.

You can find the SQL Server implementation here.

IDropAuditTriggerSqlGenerator implementation

You might guess what this does on the name alone and you're probably right. This is the opposite of the ICreateAuditTriggerSqlGenerator. Here you need to generate the SQL responsible for dropping a trigger.

You can find the SQL Server implementation here.

MigrationsSqlGenerator derived class

This class is responsible for showing Entity Framework that CreateAuditTriggerOperation and DropAuditTriggerOperation are supported and pointing it to the I*AuditTriggerSqlGenerator implementations.

This will probably look identical to the SQL Server version bar one thing: the base class. The example derives from SqlServerMigrationsSqlGenerator but for other databases, it needs to inherit the database-specific MigrationsSqlGenerator implementation. If there is no such thing, derive it from Microsoft.EntityFrameworkCore.Migrations.MigrationsSqlGenerator.

You can find the SQL Server implementation here.

DbContextOptionsBuilder extension

The only thing that's needed now is a nice extensions that will wrap the call to UseAuditExtension<TCreateAuditTriggerSqlGenerator, TDropAuditTriggerSqlGenerator, TMigrationsSqlGenerator>. The type parameters are the implementations created above.

You can find the SQL Server implementation here.

InternalsVisibleTo attribute

Almost the entirety of EFCore.AuditExtensions.Common is marked as internal to prevent all the non-application-specific code from leaking into the application project. To be able to use the Common project in your implementation, add this attribute:

[assembly: InternalsVisibleTo("<your-project-name>")]

to EFCore.AuditExtensions.Common/Assembly.cs.

BaseUserContextInterceptor derived class (optional)

This is an optional thing - it's needed if you want to provide the possibility to inject the current application user information into the database context.

The only thing that has to be implemented here is the SetUserContext method:

void SetUserContext(DbConnection connection, string user);

The method won't be called if the user identifier returned from IUserProvider is null or empty, so there's no need to worry about that.

You can find the SQL Server implementation here.

If you chose to implement this, and additional DbContextOptionsBuilder extension will be needed - one that wraps the call to UseAuditExtension<TUserProvider, TUserContextInterceptor, TCreateAuditTriggerSqlGenerator, TDropAuditTriggerSqlGenerator, TMigrationsSqlGenerator>.

You can find the SQL Server implementation here.

A few notes

I feel like a disclaimer is needed to prevent any problems, so here it goes.

• It is based on several internal Entity Framework APIs that may change in the future without further notice.
• You are using this at your own risk.
• The solution makes use of database triggers and cursors, which will most surely affect the performance of your database.
• You may do whatever you want with the code I share due to the Unlicense license.
• I do not provide a NuGet package for this, as you are responsible for using this in your project and making sure it works for your use case.
• This project is not a silver bullet. It has its flaws, but it fulfilled my needs and sharing it might help others in the future.
• If your database is a busy-bee and there are lots of changes to the entities, the audit tables will fill up quickly. Make sure to have a solid plan to export the audit logs to a safe place periodically.

Cover photo by Thomas Kelley on Unsplash