Who modified it and when? A clean way to update audit columns in a SQL database

Introduction

In the world of software development, it’s crucial to maintain an audit trail of changes made to the data stored in our databases. These audit trails can help us identify potential security issues, track performance, and maintain a history of changes for debugging purposes. One common approach to implementing this functionality is by automatically updating audit columns like CreatedBy, UpdatedBy, CreatedDate, and UpdatedDate in our database tables.

This blog post will demonstrate how to achieve this goal by leveraging the ChangeTracker feature in Entity Framework Core. We will explore how to set up automatic population of these audit columns when creating and updating entities in .NET applications.

Prerequisites

To follow along with the examples in this article, you should have:

1. A basic understanding of C# and .NET Core.
2. Familiarity with EntityFrameworkCore.
3. A Microsoft SQL Server or any other supported database system installed.

Setting up the database context

First, let’s create a base class for our entities, which will include the audit columns. This base class can be inherited by all entities in our application that need to have audit information.

public abstract class AuditableEntity
{
    public string CreatedBy { get; set; }
    public DateTime CreatedDate { get; set; }
    public string UpdatedBy { get; set; }
    public DateTime? UpdatedDate { get; set; }
}

Now, let’s create a simple entity that inherits from our AuditableEntity base class.

public class Product : AuditableEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Next, we need to create our database context class, which will inherit from DbContext. In this class, we’ll override the SaveChanges and SaveChangesAsync methods to automatically update the audit columns before saving the changes to the database.

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    public override int SaveChanges()
    {
        UpdateAuditColumns();
        return base.SaveChanges();
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
    {
        UpdateAuditColumns();
        return await base.SaveChangesAsync(cancellationToken);
    }

    private void UpdateAuditColumns()
    {
        // Code to update audit columns will go here.
    }
}

Updating the audit columns using ChangeTracker

The UpdateAuditColumns method will utilize EF Core’s ChangeTracker to determine which entities have been added or modified. For each such entity, we’ll update the corresponding audit columns.

private void UpdateAuditColumns()
{
    var currentUserId = "JanKowalski"; // This should be replaced with the actual user ID/Name from the user context.
    var now = DateTime.UtcNow;

    foreach (var entry in ChangeTracker.Entries<AuditableEntity>())
    {
        switch (entry.State)
        {
            case EntityState.Added:
                entry.Entity.CreatedBy = currentUserId;
                entry.Entity.CreatedDate = now;
                break;

            case EntityState.Modified:
                entry.Entity.UpdatedBy = currentUserId;
                entry.Entity.UpdatedDate = now;
                break;
        }
    }
}

In the code snippet above, we have first retrieved the current user’s ID. This should typically be done using the user context or identity provided by your application’s authentication and authorization mechanisms. For demonstration purposes, we’ll use a hardcoded value of “JanKowalski”.

We then loop through all the entities being tracked by the ChangeTracker and check their EntityState. If the entity is being added, we update the CreatedBy and CreatedDate columns. If it’s being modified, we update the UpdatedBy and UpdatedDate columns. Note that we’re using DateTime.UtcNow to store the current date and time in the UTC format, which is a best practice to avoid issues related to time zones and daylight saving time.

Using the updated ApplicationDbContext

Now that we’ve set up our ApplicationDbContext to automatically update the audit columns, we can use it in our application to create and update entities.

Let’s say we have a simple console application that adds a new product and updates an existing one. Here’s how it would look like:

class Program
{
    static void Main(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder.UseSqlServer("Server=localhost;Database=MyDatabase;Trusted_Connection=True;");

        using (var context = new ApplicationDbContext(optionsBuilder.Options))
        {
            // Add a new product
            var newProduct = new Product { Name = "Laptop", Price = 1200m };
            context.Products.Add(newProduct);
            context.SaveChanges();

            Console.WriteLine("New product added with ID: {0}", newProduct.Id);

            // Update an existing product
            var existingProduct = context.Products.First();
            existingProduct.Price = 1900m;
            context.SaveChanges();

            Console.WriteLine("Updated product with ID: {0}", existingProduct.Id);
        }
    }
}

In this example, when we call SaveChanges on our ApplicationDbContext, the audit columns for the new and existing products will be updated automatically.

Alternative approach: Using Interceptors

While using the ChangeTracker is a straightforward and effective method for automatically updating audit columns, you can also achieve a similar result by using the Interceptor feature available in Entity Framework Core 3.0 and later versions. Interceptors allow you to intercept and modify the SQL commands that EF Core generates before they are sent to the database.

In this section, we’ll demonstrate how to use Interceptors to update audit columns for our entities.

First, create an interceptor class that implements the ISaveChangesInterceptor interface:

public class AuditSaveChangesInterceptor : ISaveChangesInterceptor
{
    public async ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken cancellationToken = default)
    {
        var context = eventData.Context as ApplicationDbContext;
        context.UpdateAuditColumns();
        return result;
    }

    public InterceptionResult<int> SavingChanges(
        DbContextEventData eventData,
        InterceptionResult<int> result)
    {
        var context = eventData.Context as ApplicationDbContext;
        context.UpdateAuditColumns();
        return result;
    }

    // Other interface methods can be left empty or throw a NotImplementedException if not required
    // ...
}

In the AuditSaveChangesInterceptor class, we implement the SavingChangesAsync and SavingChanges methods, which are called before the ApplicationDbContext SaveChanges and SaveChangesAsync methods, respectively. In these methods, we call the UpdateAuditColumns method from our ApplicationDbContext class to update the audit columns.

Next, update the ApplicationDbContext class to register the interceptor:

public class ApplicationDbContext : DbContext
{
    public DbSet<Product> Products { get; set; }

    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.AddInterceptors(new AuditSaveChangesInterceptor());
        base.OnConfiguring(optionsBuilder);
    }

    // The rest of the ApplicationDbContext class remains the same
    // ...
}

In the OnConfiguring method, we use the AddInterceptors method to register our custom AuditSaveChangesInterceptor.

With this approach, the audit columns will be updated automatically whenever you call SaveChanges or SaveChangesAsync on your ApplicationDbContext instances. This provides an alternative to overiding SaveChanges/SaveChangeAsync methods on ApplicationDbContext, allowing you to choose the approach that best suits your application’s requirements.

Conclusion

In this blog post, we have demonstrated how to automatically update audit columns in an SQL database using Entity Framework Core’s ChangeTracker. By implementing this functionality in a clean and reusable way, we can ensure that our application maintains a reliable audit trail of changes to our data, helping us improve security, performance, and maintainability.

Remember that the current user’s ID should be fetched from the user context or identity provided by your application’s authentication and authorization mechanisms. This will ensure that the correct user information is stored in the audit columns for every change made to the data.

Leveraging ChangeTracker in Entity Framework Core provides a powerful and clean way to manage audit columns, allowing developers to focus on building the core functionality of their applications without worrying about manually updating these columns.

References

• https://github.com/dotnet/efcore