Audit Trail Implementation in ASP.NET Core with Entity Framework Core

In this article, we will go through Audit Trail Implementation in ASP.NET Core (.NET 5) using Entity Framework Core. So what’s this Audit Trail about? Well, it’s a handy technique to track changes that are done by your logged-in users. Ever wondered who had updated the value of a certain entity record in your ASP.NET Core Application? You would want to always keep a record of each and every modification made to your application data. This is quite vital for many businesses as well. The entire source code of this implementation is available on my Github here.

I will show you the exact process that happens in the background while logging the audit trails. We will track the following with our implementation.

  • TableName
  • Affected Column(s)
  • Primary Key / Value of the Table
  • Old Values
  • New Values
  • Type – Create/Delete/Update/Delete
  • User Responsible for the modification.
  • Date/Time

Here are some Audit Trail logs generated by the .NET 5 Clean Architecture Boilerplate template. You can see that we are also recording the user logged in / out activities as well.

Ssms MrsoJKBZj7 Audit Trail Implementation in ASP.NET Core with Entity Framework Core
Audit Trail Implementation in ASP.NET Core with Entity Framework Core.

Seems interesting, yeah?. At the end of this article, we will be able to completely secure our data and track any changes associated with it at this detailed level.

Let’s get started with Audit Trail Implementation in ASP.NET Core. So, here is how we will go about the tutorial. I will create a very basic CRUD Application using .NET 5 MVC, Microsoft Identity, and Entity Framework Core. Once this is done, we will start integrating Audit Trail into the Application. I will go through 2 variants, coding everything from the ground up, and using a library to achieve the same. It’s quite important to understand how the process works, thus we will code it up initially.

Scaffolding the CRUD Application.

I will be using Visual Studio 2019 Community for this demonstration. Let’s create a new Solution and name it AuditTrail.EFCore.Demo. Make sure to choose the Framework as .NET 5 and the Authentication Type as Individual accounts. This includes the Microsoft Identity / Entity Framework Core package to your application out-of-the-box.

devenv wm8HeWAGeO Audit Trail Implementation in ASP.NET Core with Entity Framework Core

First off, let’s get done with the CRUD Implementation. For this, let’s make a simple Product Data Model and Scaffold the view to auto-generate the CRUD code. We are rushing through this as the primary aim is to learn about Audit Trail Implementation in ASP.NET Core with Entity Framework Core.

In the Models Folder, add a new class, Product.cs. This is as simple as it gets 😛

public class Product
{
    public string Id { get; set; }
    public string Name { get; set; }
    public int Rate { get; set; }
}

Now, let’s scaffold a Controller along with Views. Right-click on the Controllers folder and select Add Controller. In the dialog that appears, select ‘MVC Controller with views using Entity Framework‘. The intention here is to quickly create the CRUD application with minimal code.

devenv 35L24fLUQU Audit Trail Implementation in ASP.NET Core with Entity Framework Core

In the next dialog box, select appropriate Model class (here it is Product), and then select the Context class. Make sure that Generate Views checkbox is selected and Click Add.

devenv 5Ke7r2rLJC Audit Trail Implementation in ASP.NET Core with Entity Framework Core

Now, Visual Studio does the heavy lifting for you and creates Views and Controllers to Create, Read, Update and Delete Products. But before continuing, let’s add the Migrations and update our Database. PS, we are using MSSQL LocalDB instance. You can change it in the appsettings.json/ConnectionStrings.

Open up Package Manager Console and run the following.

add-migration addedProducts
update-database
devenv mBsq7CF74j Audit Trail Implementation in ASP.NET Core with Entity Framework Core

With that done, let’s run the application and navigate to /products to make sure that everything is working fine.

chrome Vf5pv3U1g1 Audit Trail Implementation in ASP.NET Core with Entity Framework Core

One minor change we will do is to secure the products controller so that only Authenticated users can do operations on the product entity. Navigate to the ProductsController.cs and add the [Authorize] attribute.

[Authorize]
public class ProductsController : Controller

Run the application again and navigate to /products. This time you will be prompted to login. Let’s register a new account and login.

chrome ij0VTXFYfY Audit Trail Implementation in ASP.NET Core with Entity Framework Core

Getting Started with Audit Trail Implementation in ASP.NET Core

Now, let’s get started with the actual audit trail implementation. The idea is quite simple. We will be creating an abstract Context class that inherits from the DBContext / IdentityDbContext of Entity Framework Core / Identity. In this context class, we will add the Model of AuditTrail and override the SaveChangesAsync base function so that, every time any change occurs, we are able to track it using Entity Framework’s powerful ChangeTracker. But there is one catch. We will be using SaveChangesAsync(userId) instead of SaveChangesAsync(). This ensures that we get the related user as well.

First, Create a new class under the Models Folder and name it Audit.cs. These are the properties and data that we are going to track with the implementation. It covers pretty much all the vital property fields.

public class Audit
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string Type { get; set; }
    public string TableName { get; set; }
    public DateTime DateTime { get; set; }
    public string OldValues { get; set; }
    public string NewValues { get; set; }
    public string AffectedColumns { get; set; }
    public string PrimaryKey { get; set; }
}

We talked about tracking the type of change associated with the DB. This includes Create, update and delete. Let’s make an Enum class for this. I created a new folder name Enums and added a new enum , AuditType.cs.

public enum AuditType
{
    None = 0,
    Create = 1,
    Update = 2,
    Delete = 3
}

Next, we will make a simple abstraction over the previous DB Model (Audit). This is more like a DTO object for essential mappings and type conversions. In the Models folder, add another class and name it AuditEntry.cs

public class AuditEntry
{
    public AuditEntry(EntityEntry entry)
    {
        Entry = entry;
    }
    public EntityEntry Entry { get; }
    public string UserId { get; set; }
    public string TableName { get; set; }
    public Dictionary<string, object> KeyValues { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> OldValues { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> NewValues { get; } = new Dictionary<string, object>();
    public AuditType AuditType { get; set; }
    public List<string> ChangedColumns { get; } = new List<string>();
    public Audit ToAudit()
    {
        var audit = new Audit();
        audit.UserId = UserId;
        audit.Type = AuditType.ToString();
        audit.TableName = TableName;
        audit.DateTime = DateTime.Now;
        audit.PrimaryKey = JsonConvert.SerializeObject(KeyValues);
        audit.OldValues = OldValues.Count == 0 ? null : JsonConvert.SerializeObject(OldValues);
        audit.NewValues = NewValues.Count == 0 ? null : JsonConvert.SerializeObject(NewValues);
        audit.AffectedColumns = ChangedColumns.Count == 0 ? null : JsonConvert.SerializeObject(ChangedColumns);
        return audit;
    }
}

The constructor of the AuditEntry class accepts EntityEntry, which provides access to track the changes within the context. You can see that at Line 15 we are converting AuditEntry to Audit class. Here you can see that we serialize the old and new values so that it get’s saved to the database as JSON strings. Now let’s see the main class that provides data to the DTO and DB Model class.

Under the Data Folder (Or where you store your DBContext class), add a new class and name it AuditableIdentityContext.

public abstract class AuditableIdentityContext : IdentityDbContext
{
    public AuditableIdentityContext(DbContextOptions options) : base(options)
    {
    }
    public DbSet<Audit> AuditLogs { get; set; }
    public virtual async Task<int> SaveChangesAsync(string userId = null)
    {
        OnBeforeSaveChanges(userId);
        var result = await base.SaveChangesAsync();
        return result;
    }
    private void OnBeforeSaveChanges(string userId)
    {
        ChangeTracker.DetectChanges();
        var auditEntries = new List<AuditEntry>();
        foreach (var entry in ChangeTracker.Entries())
        {
            if (entry.Entity is Audit || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
                continue;
            var auditEntry = new AuditEntry(entry);
            auditEntry.TableName = entry.Entity.GetType().Name;
            auditEntry.UserId = userId;
            auditEntries.Add(auditEntry);
            foreach (var property in entry.Properties)
            {
                string propertyName = property.Metadata.Name;
                if (property.Metadata.IsPrimaryKey())
                {
                    auditEntry.KeyValues[propertyName] = property.CurrentValue;
                    continue;
                }
                switch (entry.State)
                {
                    case EntityState.Added:
                        auditEntry.AuditType = Enums.AuditType.Create;
                        auditEntry.NewValues[propertyName] = property.CurrentValue;
                        break;
                    case EntityState.Deleted:
                        auditEntry.AuditType = Enums.AuditType.Delete;
                        auditEntry.OldValues[propertyName] = property.OriginalValue;
                        break;
                    case EntityState.Modified:
                        if (property.IsModified)
                        {
                            auditEntry.ChangedColumns.Add(propertyName);
                            auditEntry.AuditType = Enums.AuditType.Update;
                            auditEntry.OldValues[propertyName] = property.OriginalValue;
                            auditEntry.NewValues[propertyName] = property.CurrentValue;
                        }
                        break;
                }
            }
        }
        foreach (var auditEntry in auditEntries)
        {
            AuditLogs.Add(auditEntry.ToAudit());
        }
    }
}

Line 6 – We add a DbSet of Audit Model. Thus remember to add migrations and update the database once we are done with this implementation.
Line 7 to 12 – Here is where we create the SaveChangesAsync method similar to the base class, but the method would accept userId as the parameter. Note that we will be updating the Product Controller class to adapt to this method and provide the current logged in user id.

Line 15 – Scans the entities for any changes.
Line 17 – Loops through the collection of all the Changed Entity. In our case the loop will always have ONE iteration only. In the instance where we are trying to update multiple entities at a time, the loop would be of prime importance.

Line 22- Get the Table Name from the entity object.
Line 25 – Loops through all the properties of the Entity. In our demonstration, it is going to be the Product Entity.

Line 27 – If the current property is a primary key, then add it to the PrimaryKey Dictionary and skip it.
Line 33-52 – This is where the real magic happens. We use switch case to detect the state of entity (Added, Deleted or Modified). If the entity is created , we assign the Create enum to the AuditType property and add the property to the NewValues dictionary.

Else, if it is a Delete operation, the data is added to the OldValues Dictionary. If the Entity State is Modified, we add the current property name to the ChangedColumns Property and fill in the Old (Original) and New (Updated) Values into the dictionary.

Finally in Line 57, we covert all the AuditEntries to Audits and save the changes at Line 10.

That was quite easy and understandable, yeah? Just a few lines of code and you have a robust ChangeTracker implemented into your solutions.

Note that I am inheriting from the IdentityDbContext class. This ensures that the Identity Tables get generated along with the Audit Class. You can inherit from the DbContext as well, based on your requirement.

As the final steps, let’s navigate to the ProductsController to make the changes that can accomdate our ChangeTracker. Remeber, we need to provide the Current Logged in userId, yeah?

Let’s start with the Create (POST) method. The only thing to care about is, to add the currently logged in user id. Identity makes it quite easy for us. User?.FindFirst(ClaimTypes.NameIdentifier).Value returns the UserId with ease 😀 Add this to the SaveChangesAsync method as I have done in line 4 below.

if (ModelState.IsValid)
{
    _context.Add(product);
    await _context.SaveChangesAsync(User?.FindFirst(ClaimTypes.NameIdentifier).Value);
    return RedirectToAction(nameof(Index));
}
return View(product);

BONUS – Cleaner way to Update Entities via EFCore.

Now, for the update method, there will be one more change. This is some kind of BONUS you will be getting from this article. The one issue with the normal update method of the EFCore Context is that it literally updates each and every column of the table even though most of the column data remains unchanged. I will make it more clearer with an example. Let’s say we are working with Student entity that has the following columns.

  • Id
  • Age
  • Class
  • Name

Now, we run an update command to change the Student’s Class. What we usually would do is _dbContext.Update(student); . Now the issue with this is that, it generates a SQL statement that looks something like this.

Update Students Set Age = 25, Class = 12, Name = 'Mukesh' where Id = 1;

Do we really need to update the column that are already the same? We require a SQL command like this..

Update Students Set Class = 12 where Id = 1;

Does this make sense? A lot more efficient and cleaner, yeah? Let’s now see how to address this issue in the world of Entity Framework Core. In the Edit (Post) method, make the changes mentioned in the snippet below (Line 9 and 10).

if (id != product.Id)
{
    return NotFound();
}
if (ModelState.IsValid)
{
    try
    {
        var oldProduct = await _context.Product.FindAsync(id);
        _context.Entry(oldProduct).CurrentValues.SetValues(product);
        await _context.SaveChangesAsync(User?.FindFirst(ClaimTypes.NameIdentifier).Value);
    }
    catch (DbUpdateConcurrencyException)
    {
        if (!ProductExists(product.Id))
        {
            return NotFound();
        }
        else
        {
            throw;
        }
    }
    return RedirectToAction(nameof(Index));
}
return View(product);

What we did is, get the original record using the Find By Id method. using this record, we use the SetValues method of the Context class. In this way, the unchanged properties get ignored and only the changes values make it into the generated SQL Update Query! This will help a lot in the longer run.

Next, in the Delete (POST) method, at Line 3 add the userId as well. That’s it.

var product = await _context.Product.FindAsync(id);
_context.Product.Remove(product);
await _context.SaveChangesAsync(User?.FindFirst(ClaimTypes.NameIdentifier).Value);
return RedirectToAction(nameof(Index));

Finally, let’s add the migrations and update our database. Open up the Package Manager Console and run the following command.

add-migration audit
update-database

With that done, let’s run our application and navigate to the /products page. Try to add / update and delete a few products. If things went well, we would be able to see these changes in the AuditLogs Table in the Application Database. Let’s check our AuditLogs Table.

devenv zicAeelhwq Audit Trail Implementation in ASP.NET Core with Entity Framework Core

There you go. We are able to see all the changes we performed on the Product Entity. As for the scalability, you can really add all kinds of data here. Let’s say IP Address of the user, Browser Details, and much more.

An Alternative – NuGet package.

As mentioned earlier, I created a compact library out of the above implementation, so that I get to modularize my Web Projects. Feel free to use the package as well. .NET 5 Clean Architecture Template also makes use of this package out-of-the-box!

Install-Package AspNetCoreHero.EntityFrameworkCore.AuditTrail

For now, let’s wrap up this article.

Consider supporting me by buying me a coffee.

Thank you for visiting. You can now buy me a coffee by clicking the button below. Cheers!

Buy Me A Coffee

Summary

In this article, we learned about Audit Trail Implementation in ASP.NET Core (.NET 5) using Entity Framework Core. This is quite handy when you start working with applications that are used by multiple users and so on.

You can find the entire source code of the implementation here.

Leave behind your valuable queries, suggestions in the comment section below. Also, if you think that you learned something new from this article, do not forget to share this within your developer community. Happy Coding!

aspnetcorehero-logo

.NET 5 Clean Architecture – Boilerplate

ASP.NET Core Hero Boilerplate – Clean Architecture Solution Template for ASP.NET Core 5.0. Built with Onion/Hexagonal Architecture and incorporates the most essential Packages your projects will ever need. Includes both WebApi and Web(MVC) Projects. Everything is put into a Nuget Package that will allow you to start generating ASP.NET Core 5.0 Solutions within seconds..

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *

15 Comments

  1. I did almost the same two years ago and everything worked fine. But we had an issue that could not be resolved with this approach. Old and new values usually contain ids of other entities and responsible users want to see their representation instead of id.
    Here is an example:
    class Product { string Id; string Name; int Rate; }
    class ProductVariant { string Id; string ProductId; string Color }
    Imagine, you are changing the ProductVariant for product “Audi 100”, setting the color from yellow to red.
    In audit admin will see, that color changed from yellow to red for some ProductId and it won’t tell them a much. Or you could change for red ProductVariant one ProductId to another. What does it mean? Nobody knows
    We thought about dynamic joining one table to another to fetch extra details but. For this case it would be:
    SELECT * from ProductVariant left join Product on ProductVariant.ProductId = Product.Id
    But this implementation is almost impossible:
    1. Writing dynamic queries is a tricky task
    2. Product “Audi 100” could be deleted by that time. Yes, generally you have foreign keys or you might use soft deleting but still it could be the case.
    3. You could rename “Audi 100” to “BMW X5”. Yes, I know, you couldn’t, but users could. And this leads to false knowledge about what really was changed.
    4. Database structure constantly changes. You might rename ProductId field to ParentId for example (this is incorrect, but I beleive you know what I’m talking about). Would you change all audit records, renaming fields in serialized “Old values” and “New values” to be able to join those two tables?
    So, if you really need to allow user to see detailed information about changes, you might need adopt one of the two solutions:
    1. When saving to audtit table you also need to save some extra data. Basically these will be names for every id referencing another table. This can lead for several extra quries to a database to fetch those names and this leads to performance loss if your application writes much data.
    2. You might make a database structure shapshot on every migration and always change names to OldValues/NewValues fields. Then you need to join records only from audit. For example, when admin opens ProductVariant audit record, you do the following on the backend:
    a) deserialize old and new values
    b) get database structure snapshot for that period and find info about ids and referencing tables
    c) execute new query or queries to audit table: select top 1 * from Audit where Id = {ProductVariantId param here} and TableName = ‘Product’ and DateTime <= {ProductVariant audit record date here}

  2. Great tutorial, the audit trail is a very useful idea. Just for the clarity of the tutorial, would help to describe the new context and database update method, (I had to figure it myself, didn’t look into your repo).

  3. So great article.
    When selecting the template in VS 2019 my option are a little different .
    When selecting Individual User Accounts I need values for
    Domain Name
    Application ID
    Sign-up or Sign-in Policy
    Do I need an identity provider to create this demo and which did you use please
    Cheers

    1. Hi, Thanks.

      I believe you are trying to create a WebAPI Project. I created an MVC Core Project. WebAPIs do not have the built-in identity scaffolding like before. If you need to implement this tutorial with API, you can create one without authentication and add authentication later.

  4. I’m trying to implement this in my project, but I don’t understand which modifications to make.
    I’m using Windows authentication.

    I also don’t understand how the Audit table can be created.
    I did a add-migration, but because the Audit DbSet is in the “AuditableIdentityContext” and not in my “ProjectContext”, no table is created in my DB when i do update-database.

  5. Hi Mukesh, i got a question. What happend if i have a process that run into a transaction where i need to call savechanges twice? This gonna save for each saveChanges and duplicate the data?

    Thanks for all your posts, are awesome!!!!
    Keep coding!!

  6. Thanks Sir.
    Please added the step to modify ApplicationDbContext class after adding audit entity
    public class ApplicationDbContext : AuditableIdentityContext

  7. Hi Muckesh,
    Great article!!

    If I use the AspNetCoreHero.EntityFrameworkCore.AuditTrail package what are the changes needed in my mvc appaliction?
    Do you have a demo project?

    Thank you in advance.
    Br,

  8. Hi,
    I have a question:
    In OnAfterSaveChanges you call SaveChangesAsync() which ends up as recursive call.
    Is that intended, or should rather base.SaveChagnesAsync() be called?
    I came across that issue when I wanted to provide other SaveChangesAsync() overloads (and overrides) to prevent client of the code from calling base method (in DbContext) just because using e.g. CancellationToken…

  9. hi mukesh,thanks for the great tutorial , i have one doubt , how can i include identity tables to this (now its not logging AspNetUser,AspNetRoles …)