Entity Framework Core – Auditing Changes

An update for Entity Framework core to a prior post on Auditing EF changes in EF 5/6, so for my thinking on this issue please read that post.

To start with here’s my table definition, note that I don’t have UserID in this variant as I did not need it for the project that I was working on. But if you reference my older post, you can see how it is used.

CREATE TABLE [dbo].[AuditLog](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Action] [nvarchar](2) NOT NULL,
	[TableName] [nvarchar](100) NOT NULL,
	[RecordKey] [nvarchar](255) NULL,
	[ColumnName] [nvarchar](100) NOT NULL,
	[OldValue] [nvarchar](max) NULL,
	[NewValue] [nvarchar](max) NULL,
	[CreateDateTime] [datetime2](7) NOT NULL,
	[AuditGroupingKey] [uniqueidentifier] NOT NULL,
 CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED ([ID] ASC)
 )

Which gives an entity definition as below, along with code in the datacontext. In this project I was using EF migrations, so the database definition was not required but it’s shown for clarity.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

public partial class AuditLog
{
	[Key]
	[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
	public int Id { get; set; }
	public string Action { get; set; }
	public string TableName { get; set; }
	public string RecordKey { get; set; }
	public string ColumnName { get; set; }
	public string OldValue { get; set; }
	public string NewValue { get; set; }
	public DateTime CreateDateTime { get; set; }
	public Guid AuditGroupingKey { get; set; }
}

Now, for EF core, I started with overriding the SaveChanges method, similar to the EF 5/6 code and it did not work. Couple of minutes later I realized I was using the SaveChangesAsync method and not the SaveChanges, so I needed to add an override for SaveChangesAsync as well. Note that I only override one of the aysnc methods as that is the one I used. Below is just the Async method, the non-async is the same except for the base calls.

public override Task SaveChangesAsync(CancellationToken cancellationToken = default)
{
	// Generate new group key
	var auditGroupingKey = Guid.NewGuid();

	// Get all added entries (save for later)
	var addedEntries = ChangeTracker.Entries().Where(p => p.State == EntityState.Added).ToList();

	// Get all Deleted/Modified entities (not Unmodified or Detached)
	foreach (var entry in ChangeTracker.Entries()
		.Where(p => p.State == EntityState.Deleted || p.State == EntityState.Modified))
	{
		// For each changed record, get the audit record entries and add them
		AuditLogs.AddRange(GetAuditRecordsForChange(entry, auditGroupingKey));
	}

	// Call the original SaveChanges(), which will save both the changes made and the audit records
	var result = base.SaveChangesAsync(cancellationToken);

	if (addedEntries.Count > 0)
	{
		// Process each added entry now that the keys have been created
		foreach (var entry in addedEntries)
		{
			// For each added record, get the audit record entries and add them
			AuditLogs.AddRange(GetAuditRecordsForChange(entry, auditGroupingKey, true));
		}

		// Call the original SaveChanges() to save the audit records
		base.SaveChangesAsync(cancellationToken);
	}

	return result;
}

And finally the code that processes the changes and creates the Audit rows. Note that EF core provides much cleaner methods to get the tablename, keyname and values. So lets start by getting these values.

private IEnumerable GetAuditRecordsForChange(EntityEntry dbEntry, Guid auditGroupingKey, bool isNewEntry = false)
{
	// Current DateTime
	var currentDateTime = DateTime.Now;

	// Get table name
	var tableName = Model.FindEntityType(dbEntry.Entity.GetType()).GetTableName();

	// Create results
	var results = new List();

	// We do not want to audit the audit log
	if (tableName.Equals("AuditLog", StringComparison.OrdinalIgnoreCase))
	{
		// Return empty results
		return results;
	}

	// Get key name from meta data
	var keyProperty = dbEntry.Properties.FirstOrDefault(p => p.Metadata.IsKey());
	var keyValue = keyProperty?.CurrentValue.ToString();

	// Get foreign key values
	var foreignKeyNames = dbEntry.Properties.Where(p => p.Metadata.IsForeignKey()).Select(p => p.Metadata.Name).ToList();

Based on the action we are dealing with we need to populate the table rows with different data. So for insert we only care about the new values, that are modified i.e. not the default value for the field or null and also a column name we want to audit. So in my projects, I don’t need to record things like the createdatetime as that is part of the auditrow.

	if (dbEntry.State == EntityState.Added || isNewEntry)
	{
		results.AddRange(
			dbEntry.Properties
				.Where(p => p.CurrentValue != null && ValidColumnName(p.Metadata.Name))
				.Select(mp => new AuditLog
				{
					Action = ActionInsert,
					TableName = tableName,
					RecordKey = keyValue,
					ColumnName = mp.Metadata.Name,
					NewValue = mp.CurrentValue?.ToString(),
					CreateDateTime = currentDateTime,
					AuditGroupingKey = auditGroupingKey
				}));
	}

For deleted rows, we care only about the original values, so no checks for modified values. I could just have recorded the row key and no other value as it is being deleted and we probably don’t care what was in the row at that time.

	else if (dbEntry.State == EntityState.Deleted)
	{
		// For deletes, add the whole record
		results.AddRange(
			dbEntry.Properties
				.Where(p => ValidColumnName(p.Metadata.Name))
				.Select(mp => new AuditLog
				{
					Action = ActionDelete,
					TableName = tableName,
					RecordKey = keyValue,
					ColumnName = mp.Metadata.Name,
					NewValue = mp.OriginalValue?.ToString(),
					CreateDateTime = currentDateTime,
					AuditGroupingKey = auditGroupingKey
				}));
	}

For modified rows, we care modified values and foreign key values, for referencing other entities that have been modified. This is where the AuditGroupingKey comes in usefull. If I modify entity A, which has a child of enitty b which i also modify i.e. order, orderline, then with the group key and foreign key I can match them all up.

	else if (dbEntry.State == EntityState.Modified)
	{
		// For updates ....
		results.AddRange(
			dbEntry.Properties
				.Where(p => (p.IsModified || foreignKeyNames.Contains(p.Metadata.Name)) && ValidColumnName(p.Metadata.Name))
				.Select(mp => new AuditLog
				{
					Action = ActionUpdate,
					TableName = tableName,
					RecordKey = keyValue,
					ColumnName = mp.Metadata.Name,
					OldValue = mp.OriginalValue?.ToString(),
					NewValue = mp.CurrentValue?.ToString(),
					CreateDateTime = currentDateTime,
					AuditGroupingKey = auditGroupingKey
				}));
	}

And finally here column name check code, very simple, it could certainly be made more flexible perhaps with attributes on properties to ignore auditing on a per table per property basis.

	private bool ValidColumnName(string columnName)
	{
		return !columnName.Equals("CreateDateTime", StringComparison.OrdinalIgnoreCase) &&
		       !columnName.Equals("LastModifiedDateTime", StringComparison.OrdinalIgnoreCase) &&
		       !columnName.Equals("UpdateDateTime", StringComparison.OrdinalIgnoreCase) ;
	}

So this is the update to my old EF5/6 code for EF Core.

About Colin Blakey

Way to many years (30+) working in the Financial software world. Now working in a commercial tax software company in the Innovation labs working on machine learning, blockchain and other emerging technologies. I really should add more blogs.
This entry was posted in Entity Framework. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s