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:
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; }
public DateTime AuditDateTimeUtc { get; set; }
public string AuditType { get; set; }
public string AuditUser { get; set; }
public string TableName { get; set; }
public string KeyValues { get; set; }
public string OldValues { get; set; }
public string NewValues { get; set; }
public string ChangedColumns { get; set; }
}
}
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;
foreach (PropertyEntry property in Entry.Properties)
{
string propertyName = property.Metadata.Name;
string dbColumnName = property.Metadata.Relational().ColumnName;
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";
Role role = new Role()
{
Name = "Role",
};
Db.Role.Add(role);
Db.SaveChanges(user);
role.Details = "Details";
Db.SaveChanges(user);
role.Name = role.Name + "1";
Db.SaveChanges(user);
role.Name = "Role All";
role.Details = "Details All";
Db.SaveChanges(user);
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": {
"MopDbConnection": "server=10.10.20.18\\DB03;database=TESTDB;
user id=TEST;password=dhaka"
},
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