Entity Framework – Auditing Changes

It’s taken a while to put together this post but a couple of years back I was looking for a way to audit changes to the SQL database made when using Entity Framework. After a little searching I found this post Using Entity Framework 4.1 DbContext Change Tracking for Audit Logging by Justin Dority that gave me an excellent starting point. Now this worked to some degree for me but at the time I was using EF Code First with the Fluent API . Note that in current Code First EF (6.x) we no longer use as much of the Fluent API to define keys but instead use data annotations i.e. a key field attribute which may or may not be present. Fun, fun, fun.

So some things worked and some did not (like the key field retrieval) and when adding entities the audit row was created prior to the key being generated (for IDENTITY fields) so it was not very helpful finding the row later on.

Time for me to tweak the code to a) make key retrieval as bullet proof as I could,  b) deal with IDENTITY key values and c) other minor tweaks.

So starting with my table definition we have

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

Which gives an EF Code-First definition of

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

[Table("AuditLog")]
public partial class AuditLog
{
  public int ID { get; set; }

  [Required, StringLength(2)]
  public string Action { get; set; }

  [Required, StringLength(100)]
  public string TableName { get; set; }

  public string RecordKey { get; set; }

  [Required, StringLength(100)]
  public string ColumnName { get; set; }

  public string OldValue { get; set; }

  public string NewValue { get; set; }

  [Column(TypeName = "datetime2")]
  public DateTime CreateDateTime { get; set; }

  public int CreateUserID { get; set; }

  public Guid AuditGroupingKey { get; set; }
}

So if you look at Justin’s code he does all the audit row creation up front prior to the actual save. But as I have IDENTITY fields for which we will only know the value later I will need to split the audit row creation into two steps, the first for updates and deletes, and the second for additions. Why did I split and not just move after the save changes call. To avoid multiple calls to SaveChanges if possible i.e. if all we have are updates and deletes then there is only the need for the one call. So here is my code and note that I always have the user (it’s a WPF app). Note that I check for a particular Entity Type of FileData. This is a FileStream entity class so I don’t want to attempt to store file changes in the audit log.

public override int SaveChanges()
{
  // Find current user and save
  return SaveChanges(App.CurrentUser.ID);
}

And here is the core SaveChanges code

public int SaveChanges(int userID)
{
  using (var scope = new TransactionScope())
  {
    // 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 && !(p.Entity is FileData)).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) && !(p.Entity is FileData)))
    {
      // For each changed record, get the audit record entries and add them
      AuditLogs.AddRange(GetAuditRecordsForChange(entry, userID, auditGroupingKey));
    }

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

    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, userID, auditGroupingKey, true));
      }

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

    scope.Complete();
    return result;
  }
}

And finally the code that processes the changes and creates the Audit rows. Start by getting the key fields and foreign keys

private IEnumerable<AuditLog> GetAuditRecordsForChange(DbEntityEntry dbEntry, int userID, Guid auditGroupingKey, bool isNewEntry = false)
{
    var results = new List<AuditLog>();
    var currentDateTime = DateTime.Now;

    // Get the Table() attribute, if one exists
    var tableAttribute = dbEntry.Entity.GetType().GetCustomAttributes(typeof (TableAttribute), true).SingleOrDefault() as TableAttribute;

    // Get table name (if it has a Table attribute, use that, otherwise get the name, trimmed from the proxy name if needed)
    // Is this a proxy class, if so get the base type name otherwise use the current type name
    var type = dbEntry.Entity.GetType();
    var tableName = tableAttribute != null ? tableAttribute.Name : (type.Namespace.Equals("System.Data.Entity.DynamicProxies") ? type.BaseType.Name : type.Name);

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

    // Get key name from meta data
    var keyName = ObjectContext
        .MetadataWorkspace
        .GetEntityContainer(ObjectContext.DefaultContainerName, DataSpace.CSpace)
        .BaseEntitySets
        .First(m => m.ElementType.Name == tableName)
        .ElementType
        .KeyMembers
        .Select(k => k.Name)
        .FirstOrDefault();

    // Get foreign key values (for later parsing when showing history)
    var foreignKeyProperties = GetForeignKeyValues(tableName);

Based on the action we are dealing with then

    // What was the action?
    if (dbEntry.State == EntityState.Added || isNewEntry)
    {
        // For inserts, add the record (except for null fields)
        results.AddRange(
            dbEntry.CurrentValues.PropertyNames
            .Where(cn  => dbEntry.CurrentValues.GetValue<object>(cn) != null)
            .Select(cn => new AuditLog
            {
                Action = "A",
                TableName = tableName,
                RecordKey = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),
                ColumnName = cn,
                NewValue = dbEntry.CurrentValues.GetValue<object>(cn) == null ? null : dbEntry.CurrentValues.GetValue<object>(cn).ToString(),
                CreateDateTime = currentDateTime,
                CreateUserID = userID,
                AuditGroupingKey = auditGroupingKey
            }));
    }
    else if (dbEntry.State == EntityState.Deleted)
    {
        // For deletes, add the whole record 
        results.AddRange(
            dbEntry.OriginalValues.PropertyNames
            .Select(cn => new AuditLog
            {
                Action = "D",
                TableName = tableName,
                RecordKey = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
                ColumnName = cn,
                NewValue = dbEntry.OriginalValues.GetValue<object>(cn) == null ? null : dbEntry.OriginalValues.GetValue<object>(cn).ToString(),
                CreateDateTime = currentDateTime,
                CreateUserID = userID,
                AuditGroupingKey = auditGroupingKey
            }));
    }
    else if (dbEntry.State == EntityState.Modified)
    {
        // For updates
        results.AddRange(
            dbEntry.OriginalValues.PropertyNames
                .Where(cn => !Equals(dbEntry.OriginalValues.GetValue<object>(cn), dbEntry.CurrentValues.GetValue<object>(cn)) || foreignKeyProperties.Contains(cn))
                .Select(cn => new AuditLog
                {
                    Action = "U",
                    TableName = tableName,
                    RecordKey = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
                    ColumnName = cn,
                    OldValue = dbEntry.OriginalValues.GetValue<object>(cn) == null ? null : dbEntry.OriginalValues.GetValu<object>(cn).ToString(),
                    NewValue = dbEntry.CurrentValues.GetValue<object>(cn) == null ? null : dbEntry.CurrentValues.GetValue<object>(cn).ToString(),
                    CreateDateTime = currentDateTime,
                    CreateUserID = userID,
                    AuditGroupingKey = auditGroupingKey
                }));
    }

    // Otherwise, don't do anything, we don't care about Unchanged or Detached entities so return the audit rows
    return results;
}

And finally here is the code to get the foreign key fields. I still have to figure out a better way to derive foreign key field information but the By Convention simplistic approach works I show here works with my current database design. It should be possible to get this from the meta data but as of the last time I checked it just was not. I’m hoping in EF 7 we can get this information more easily.

private IEnumerable<string> GetForeignKeyValues(string tableName)
{
    // Get entities
    var items = ObjectContext.MetadataWorkspace.GetItems<EntityType>(DataSpace.CSpace).First(i => i.Name.Equals(tableName));

    // Create foreign keys (this is based on convention i.e. foreign key table name + ID)
    var foreignKeys = items.DeclaredNavigationProperties.Select(item => string.Format("{0}", item.Name)).ToList();

    return foreignKeys;
}

So there is my variation on the great work done by Justin.

So while it’s taken a while to blog about this (or at least to finish the post), it has been in use for over 2 years without issues and I hope it helps.

Advertisements

About Colin Blakey

Way to many years (25+) working in the Financial software world.
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s