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.