Creating Simple Audit Trail With Entity Framework Core

• Download DbAuditWithEfCore_7.zip - 28.3 KB
• Download DbAuditWithEfCore.zip - 29.2 KB

Introduction

In a particular project, we had to log data changes that any end-user made. This needed to be done without introducing many code changes to the existing solution. The project was using Entity Framework, so I thought why not we do things inside the SaveChanges() method.

Background

The database was SQL Server, ORM Entity Framework Core, and the application was using a custom SaveChanges(string userName) method rather than the regular SaveChanges(). So we decided to add things inside that method. Plus it was an advantage as we were getting the audit person name inside that method.

Here is the log table sample:

Let's start coding.

Audit Log Data

Audit Table Entity

This entity will be used as a database log table.

using System;
using System.Collections.Generic;
using System.Text;

namespace Db.Table
{
    public class Audit
    {
        public Guid Id { get; set; }                    /*Log id*/
        public DateTime AuditDateTimeUtc { get; set; }  /*Log time*/
        public string AuditType { get; set; }           /*Create, Update or Delete*/
        public string AuditUser { get; set; }           /*Log User*/
        public string TableName { get; set; }           /*Table where rows been 
                                                          created/updated/deleted*/
        public string KeyValues { get; set; }           /*Table Pk and it's values*/
        public string OldValues { get; set; }           /*Changed column name and old value*/
        public string NewValues { get; set; }           /*Changed column name 
                                                          and current value*/
        public string ChangedColumns { get; set; }      /*Changed column names*/
    }
}

• Id: Log id or log tables primary key
• AuditDateTimeUtc: Log date time in UTC
• AuditType: Create/Update/Delete
• AuditUser: Data changed by the user
• TableName: Table where rows been created/updated/deleted
• KeyValues: Changed row's primary key values and column names (JSON string)
• OldValues: Changed row's old values and column names (JSON string, only changed columns)
• NewValues: Changed row's current/new values and column names (JSON string, only changed columns)
• ChangedColumns: Changed row's column names (JSON string, only changed columns)

Audit Type

using System;
using System.Collections.Generic;
using System.Text;

namespace Db.Status
{
    public enum AuditType
    {
        None = 0,
        Create = 1,
        Update = 2,
        Delete = 3
    }
}

• Create: new row added to a table
• Update: existing row modified
• Delete: existing row deleted

Audit Db Context

Creating an interface to specify an audit trail based DB context for the entity framework:

using Db.Table;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;

namespace Db
{
    public interface IAuditDbContext
    {
        DbSet<Audit> Audit { get; set; }
        ChangeTracker ChangeTracker { get; }
    }
}

• DbSet<Audit> Audit { get; set; } is the audit log table.
• ChangeTracker ChangeTracker { get; } is DbContext default property which we will use to track change details.

Audit Table Configuration

Create an entity to table mapper configuration as need. If we are doing code first without using any table configuration class, this is optional.

using Db.Table;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;

namespace Db.Configuration
{
    internal class AuditConfig : IEntityTypeConfiguration<Audit>
    {
        public void Configure(EntityTypeBuilder<Audit> entity)
        {
            entity.HasKey(e => e.Id);

            entity.ToTable("tbl_test_audit_trail");

            entity.Property(e => e.Id)
                .HasColumnName("id");
            entity.Property(e => e.AuditDateTimeUtc)
                .HasColumnName("audit_datetime_utc");
            entity.Property(e => e.AuditType)
                .HasColumnName("audit_type");
            entity.Property(e => e.AuditUser)
                .HasColumnName("audit_user");        
            entity.Property(e => e.TableName)
                .HasColumnName("table_name");
            entity.Property(e => e.KeyValues)
                .HasColumnName("key_values");
            entity.Property(e => e.OldValues)
                .HasColumnName("old_values");
            entity.Property(e => e.NewValues)
                .HasColumnName("new_values");
            entity.Property(e => e.ChangedColumns)
                .HasColumnName("changed_columns");
        }
    }
}

Data Changes To Audit Table

An Entity Changes To An Audit Table Entity

Creating a helper class to map all data changes from a DB entity and create an Audit log entity using those change pieces of information. Here, we are using JSON serializer to specify column value related changes.

using Db.Status;
using Db.Table;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Db.Helper.AuditTrail
{
    public class AuditEntry
    {
        public EntityEntry Entry { get; }
        public AuditType AuditType { get; set; }
        public string AuditUser { 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 List<string> ChangedColumns { get; } = new List<string>();

        public AuditEntry(EntityEntry entry, string auditUser)
        {
            Entry = entry;
            AuditUser = auditUser;
            SetChanges();
        }

        private void SetChanges()
        {
            TableName = Entry.Metadata.Relational().TableName;                     /*For EF Core 7: Entry.Metadata.GetTableName();*/
            foreach (PropertyEntry property in Entry.Properties)
            {
                string propertyName = property.Metadata.Name;
                string dbColumnName = property.Metadata.Relational().ColumnName;  /*For EF Core 7: property.Metadata.GetColumnName();*/

                if (property.Metadata.IsPrimaryKey())
                {
                    KeyValues[propertyName] = property.CurrentValue;
                    continue;
                }

                switch (Entry.State)
                {
                    case EntityState.Added:
                        NewValues[propertyName] = property.CurrentValue;
                        AuditType = AuditType.Create;
                        break;

                    case EntityState.Deleted:
                        OldValues[propertyName] = property.OriginalValue;
                        AuditType = AuditType.Delete;
                        break;

                    case EntityState.Modified:
                        if (property.IsModified)
                        {
                            ChangedColumns.Add(dbColumnName);

                            OldValues[propertyName] = property.OriginalValue;
                            NewValues[propertyName] = property.CurrentValue;
                            AuditType = AuditType.Update;
                        }
                        break;
                }
            }
        }

        public Audit ToAudit()
        {
            var audit = new Audit();
            audit.Id = Guid.NewGuid();
            audit.AuditDateTimeUtc = DateTime.UtcNow;
            audit.AuditType = AuditType.ToString();
            audit.AuditUser = AuditUser;
            audit.TableName = TableName;
            audit.KeyValues = JsonConvert.SerializeObject(KeyValues);
            audit.OldValues = OldValues.Count == 0 ? 
                              null : JsonConvert.SerializeObject(OldValues);
            audit.NewValues = NewValues.Count == 0 ? 
                              null : JsonConvert.SerializeObject(NewValues);
            audit.ChangedColumns = ChangedColumns.Count == 0 ? 
                                   null : JsonConvert.SerializeObject(ChangedColumns);

            return audit;
        }
    }
}

All Entity Changes To Audit Table

This helper class is using the AuditEntry class and:

• creating Audit log entities considering all possible data changes from the current IAuditDbContext
• adding log entities to the log table

using Db.Table;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using System.Collections.Generic;
using System.Linq;

namespace Db.Helper.AuditTrail
{
    class AuditHelper
    {
        readonly IAuditDbContext Db;

        public AuditHelper(IAuditDbContext db)
        {
            Db = db;
        }

        public void AddAuditLogs(string userName)
        {
            Db.ChangeTracker.DetectChanges();
            List<AuditEntry> auditEntries = new List<AuditEntry>();
            foreach (EntityEntry entry in Db.ChangeTracker.Entries())
            {
                if (entry.Entity is Audit || entry.State == EntityState.Detached || 
                    entry.State == EntityState.Unchanged)
                {
                    continue;
                }
                var auditEntry = new AuditEntry(entry, userName);
                auditEntries.Add(auditEntry);
            }

            if (auditEntries.Any())
            {
                var logs = auditEntries.Select(x => x.ToAudit());
                Db.Audit.AddRange(logs);
            }
        }
    }
}

Using Audit Trail With Existing DbContext

Let's create an interface IMopDbContext by inheriting IAuditDbContext to create a DbContext object.

using Db.Table;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using System;

namespace Db
{
    public interface IMopDbContext : IAuditDbContext, IDisposable
    {
        DbSet<Role> Role { get; set; }

        DatabaseFacade Database { get; }
        int SaveChanges(string userName);
    }
}

• DbSet<Role> Role { get; set; } is an existing data table.
• Inside SaveChanges(string userName), we are going to use AuditHelper class to create Audit entities considering all entity changes. Audit entities will be then added to the audit trail table.

Creating DbContext

In our existing/test DB context, we are going to:

• add audit table DbSet<Audit> Audit { get; set; }.
• add audit table configuration modelBuilder.ApplyConfiguration(new AuditConfig()) at OnConfiguring(DbContextOptionsBuilder optionsBuilder) method, which is optional as I have mentioned previously.
• add SaveChanges(string userName) method to create audit logs.

using System;
using Db.Table;
using Db.Configuration;
using Microsoft.EntityFrameworkCore;
using Db.Helper.AuditTrail;

namespace Db
{
    public abstract class MopDbContext : DbContext, IMopDbContext
    {
        public virtual DbSet<Audit> Audit { get; set; }
        public virtual DbSet<Role> Role { get; set; }

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

        protected MopDbContext() : base()
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new AuditConfig());
            modelBuilder.ApplyConfiguration(new RoleConfig());
        }

        public virtual int SaveChanges(string userName)
        {
            new AuditHelper(this).AddAuditLogs(userName);
            var result = SaveChanges();
            return result;
        }
    }
}

Db: SQL-Server

For testing, we are using the MS SQL Server database, but it is good for any Db.

Find the related table object scripts as below.

Create Objects

CREATE TABLE [dbo].[tbl_test_audit_trail] (
    [id]                 UNIQUEIDENTIFIER NOT NULL,
    [audit_datetime_utc] DATETIME2        NOT NULL,
    [audit_type]         NVARCHAR (50)    NOT NULL,
    [audit_user]         NVARCHAR (100)   NOT NULL,
    [table_name]         NVARCHAR (150)   NULL,
    [key_values]         NVARCHAR (250)   NULL,
    [old_values]         NVARCHAR (MAX)   NULL,
    [new_values]         NVARCHAR (MAX)   NULL,
    [changed_columns]    NVARCHAR (MAX)   NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);

CREATE TABLE [dbo].[tbl_test_role] (
    [id]   INT           IDENTITY (1, 1) NOT NULL,
    [name] NVARCHAR (50) NOT NULL,
    [details] NVARCHAR (150) NULL,
    PRIMARY KEY CLUSTERED ([id] ASC)
);

• [tbl_test_audit_trail] will store audit data
• [tbl_test_role] a simple/test data table

Drop Objects

DROP TABLE [dbo].[tbl_test_audit_trail]
DROP TABLE [dbo].[tbl_test_role]

Using DbContext

Here, we are doing Insert, update and delete related operation using entity framework. Rather than calling default SaveChanges(), we are using SaveChanges(string userName) to create audit logs.

IMopDbContext Db = new MopDb();
string user = "userName";

/*Insert*/
Role role = new Role()
{
    Name = "Role",
};
Db.Role.Add(role);
Db.SaveChanges(user);


/*Update detail column*/
role.Details = "Details";
Db.SaveChanges(user);
/*Update name column*/
role.Name = role.Name + "1";
Db.SaveChanges(user);
/*Update all columns*/
role.Name = "Role All";
role.Details = "Details All";
Db.SaveChanges(user);

/*Delete*/
Db.Role.Remove(role);
Db.SaveChanges(user);

Let's check [tbl_test_audit_trail], the audit log table, the audit logs will be like:

Solution And Projects

It is a Visual Studio 2017 solution with .NET Core 2.2 projects:

• Db contains database and entity framework related codes
• Test.Integration contains integrated NUnit unit tests

Inside Test.Integration project, we need to change the connection string at appsettings.json:

"ConnectionStrings": {
  /*test*/
  "MopDbConnection": "server=10.10.20.18\\DB03;database=TESTDB;
                      user id=TEST;password=dhaka" /*sql server*/
},

References

• How to create an audit trail with Entity framework 5 and MVC 4
• Implementing Audit Trail using Entity Framework: Part 1
• My favorite, thanks to @meziantou, Entity Framework Core: History / Audit table

Limitations

• Avoid using DbContext.AutoDetectChangesEnabled = false or AsNoTracking()
• While using this trail helper, if we add/update/delete 1 row, it adds/updates/deletes 2 rows. Entity Framework is not good with the large data set. We should reinitialize the DbContext object after processing a good number of rows like 100-200.
• This audit trailer is unable to track Db generated values like IDENTITY. It is possible but this may get into a transaction failure scenario if not managed properly. Check this Audit History article of that option.
• Instead of actual column name, we are storing class property name.
• Performance could be an issue.

The code may throw unexpected errors for untested inputs. If any, just let me know.

What's Next?

• Support for Db generated values
• Creating the same thing for Entity Framework

History

• 11^th April, 2020: Initial version