DEV Community

loading...
Cover image for EF Core: How to implement basic Auditing on your Entities

EF Core: How to implement basic Auditing on your Entities

Ricky Stam
Technical Consultant @Microsoft. I love to work with C# and .NET but also enjoy writing JavaScript and Angular. Opinions are my own.
・3 min read

EF Core Audit properties

A common requirement but also a good practice is to add some some auditing properties in all the entities you save in the database.

The most common properties to use are:

  1. CreateBy (The user who created the initial entry)
  2. ModifiedBy (The user who modified the entry last)
  3. CreatedAt (The datetime of creation of initial Entry)
  4. ModifiedAt (The datetime the entry was last modified)

Let's start implementing this functionality

First we are going to create a new base class named AuditableEntity that every other entity will inherit from when we need to add the audit functionality.

public class AuditableEntity
{
    public string CreatedBy { get; set; }
    public string ModifiedBy { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ModifiedAt { get; set; }
}

For example if we have a Transaction entity and we want to add audit functionality we would inherit from our AuditableEntity like that:

public class Transaction : AuditableEntity
{
    public long TransactionId { get; set; }
    public string FromIban { get; set; }
    public string ToIban { get; set; }
    public decimal Amount { get; set; }
}

Now our Transaction class will have all the audit properties but we don't want to manually set these properties each time, we need to somehow move this responsibility to a single place.
The best place for this is the SaveChanges and SaveChangesAsync, so we'll have to override them in our DbContext class.

Here is how you would do it for the SaveChangesAsync:

public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
{
    // Get all the entities that inherit from AuditableEntity
    // and have a state of Added or Modified
    var entries = ChangeTracker
        .Entries()
        .Where(e => e.Entity is AuditableEntity && (
                e.State == EntityState.Added
                || e.State == EntityState.Modified));

    // For each entity we will set the Audit properties
    foreach (var entityEntry in entries)
    {
        // If the entity state is Added let's set
        // the CreatedAt and CreatedBy properties
        if (entityEntry.State == EntityState.Added)
        {
            ((AuditableEntity)entityEntry.Entity).CreatedAt = DateTime.UtcNow;
            ((AuditableEntity)entityEntry.Entity).CreatedBy = this.httpContextAccessor?.HttpContext?.User?.Identity?.Name ?? "MyApp";
        }
        else
        {
            // If the state is Modified then we don't want
            // to modify the CreatedAt and CreatedBy properties
            // so we set their state as IsModified to false
            Entry((AuditableEntity)entityEntry.Entity).Property(p => p.CreatedAt).IsModified = false;
            Entry((AuditableEntity)entityEntry.Entity).Property(p => p.CreatedBy).IsModified = false;
        }

        // In any case we always want to set the properties
        // ModifiedAt and ModifiedBy
        ((AuditableEntity)entityEntry.Entity).ModifiedAt = DateTime.UtcNow;
        ((AuditableEntity)entityEntry.Entity).ModifiedBy = this.httpContextAccessor?.HttpContext?.User?.Identity?.Name ?? "MyApp";
    }

    // After we set all the needed properties
    // we call the base implementation of SaveChangesAsync
    // to actually save our entities in the database
    return await base.SaveChangesAsync(cancellationToken);
}

In this example I am using the HttpContextAccessor to get the current user in my .NET Core Web API.
To have HttpContextAccessor available you just inject it into your DbContext class.
In order for HttpContextAccessor to be available for injection you need to add it in your Startup.cs file inside ConfigureServices method like that:

services.AddHttpContextAccessor();

And that's it! That's how simple it is to implement basic auditing for your entities using Entity Framework Core.

MS SQL Server Audit Features

Please keep in mind that for a full featured auditing you can use SQL Server's Track Data Changes, it is available in two flavors:

Change Data Capture

Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

Change Tracking

Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.

You can read more about it on the official docs here

You can find the full code on my GitHub

This post was written with love ❤️

Discussion (4)

Collapse
jaoude profile image
Sassine

Thank you for this.
I have used on numerous occasions.
A minor issue; I only modified the DateTime.UtcNow (in both location) to use a variable dtcNow, so that the record in the db shows the same exact CreatedAt and ModifiedAt When it was just created

Collapse
arminzia profile image
Armin Zia

Thank you. But I have a follow-up question. There's a problem with how you're storing references to the current user. The CreatedBy and ModifiedBy columns store the current user's username (or email address). there are 2 problems with that. first, there are no FK constraints and you can insert any value. secondly, since we don't have an FK navigation , there's no way to Include such properties. Say we're listing a Ticket entity which has a collection of messages posted by different users (clients, admins, etc.). How would we load the related data? The only piece of information we'd have is their usernames and we can't execute tons of GET queries to load user entities for each record. I'm having trouble wrapping my head around this limitation, any ideas are appreciated.

Collapse
rickystam profile image
Ricky Stam Author • Edited

Hi Armin,

In my example I saved the name or email address of the current user only for auditing purposes. I don't expect to do queries based on CreatedBy or ModifiedBy columns. If you have a different use case where you want to actually do queries on these columns there is nothing stopping you from creating these columns as FKs to the Users Table and instead of the Name property you can save the UserId property of the currently logged in user. If you need help with anything you can DM me here or on Twitter.

Thanks,
Ricky

Collapse
xavieracostapolo profile image
Xavier Acosta Polo

Thank you.