Click here to Skip to main content
15,920,704 members
Articles / Database Development / SQL Server

Creating Simple Audit Trail With Entity Framework Core

Rate me:
Please Sign up or sign in to vote.
4.39/5 (6 votes)
11 Apr 2020CPOL4 min read 42.3K   944   9   11
Store all data change history in a table using Entity Framework Core
In a project using Entity Framework, we had to log data changes that any end-user made without introducing many code changes to the existing solution. In this article, you will see how to do this inside the SaveChanges() method.

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:

Click to enlarge image

Let's start coding.

Audit Log Data

Audit Table Entity

This entity will be used as a database log table.

C#
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

C#
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:

C#
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.

C#
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.

C#
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
C#
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.

C#
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.
C#
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

SQL
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

SQL
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.

C#
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:

Image 2

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:

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

References

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

  • 11th April, 2020: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Bangladesh Bangladesh
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionPrimary Key value is getting -2147482647 Pin
ysrcycle5-Feb-24 8:25
ysrcycle5-Feb-24 8:25 
QuestionChanges in EF 7 Pin
tomo_240318-Feb-23 4:06
tomo_240318-Feb-23 4:06 
AnswerRe: Changes in in EF 7 Pin
DiponRoy19-Feb-23 3:12
DiponRoy19-Feb-23 3:12 
GeneralMy vote of 5 Pin
Mou_kol5-Aug-22 22:52
Mou_kol5-Aug-22 22:52 
Nice post
GeneralRe: My vote of 5 Pin
DiponRoy20-Feb-23 3:26
DiponRoy20-Feb-23 3:26 
QuestionStoring the same values in OldValues and NewValues Pin
Harold Espinosa Ch16-Oct-20 13:53
Harold Espinosa Ch16-Oct-20 13:53 
AnswerRe: Storing the same values in OldValues and NewValues Pin
DiponRoy20-Feb-23 1:44
DiponRoy20-Feb-23 1:44 
QuestionDISCONNECTED SCENARIO Pin
Member 148794682-Jul-20 19:13
Member 148794682-Jul-20 19:13 
AnswerRe: DISCONNECTED SCENARIO Pin
DiponRoy3-Jul-20 3:05
DiponRoy3-Jul-20 3:05 
GeneralMy vote of 4 Pin
Klaus Luedenscheidt11-Apr-20 18:19
Klaus Luedenscheidt11-Apr-20 18:19 
GeneralRe: My vote of 4 Pin
DiponRoy13-Apr-20 3:03
DiponRoy13-Apr-20 3:03 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.