Using Entity Framework 4.1 DbContext Change Tracking for Audit Logging

Many applications have a need to keep audit information on changes made to objects in the database. Traditionally, this would be done either through log events, stored procedures that implement the logging, or the use of archive/tombstone tables to store the old values before the modification (hopefully enforced through stored procedures). With all of these, there is always a chance that a developer could forget to do those things in a specific section of code, and that changes could be made through the application without logging the change correctly. With Entity Framework 4.1’s DbContext API, it is fairly easy to implement more robust audit logging in your application.

The DbContext exposes the ChangeTracker property, which contains all of the changes made since the most recent SaveChanges() call. This property can be used to get information about the current and previous values of any entities in the DbContext object. By overriding the SaveChanges() method, we can implement audit logging any time changes are saved to the database. For the sake of this example, let’s assume that we want to write a row to a database table that contains the entire contents of the new entity for inserts (there were no previous contents), the entire previous contents for deletes (there are no current contents). For updates to existing records, a column-by-column audit is desired, where a row exists in the audit table for each column that was changed, and the before-and-after values are shown in that row. For example, if we have a database containing the following tables:

CREATE TABLE Items
	(
		ItemID UNIQUEIDENTIFIER NOT NULL,
		ItemDescription NVARCHAR(100) NOT NULL,
		CONSTRAINT PK_Items PRIMARY KEY NONCLUSTERED (ItemID)
	)
GO

CREATE TABLE ItemAdditionalAttributes
	(
		ItemAdditionalAttributeID UNIQUEIDENTIFIER NOT NULL,
		ItemID UNIQUEIDENTIFIER NOT NULL,
		AttributeName NVARCHAR(50) NOT NULL,
		AttributeValue NVARCHAR(500) NOT NULL,
		CONSTRAINT PK_ItemAdditionalAttributes PRIMARY KEY NONCLUSTERED (ItemAdditionalAttributeID)
	)
GO

ALTER TABLE ItemAdditionalAttributes ADD CONSTRAINT FK_ItemAdditionalAttributes_Items FOREIGN KEY (ItemID) REFERENCES dbo.Items (ItemID)
GO

We can then add an additional table for logging, similar to this one:

CREATE TABLE AuditLog
	(
		AuditLogID UNIQUEIDENTIFIER NOT NULL,
		UserID NVARCHAR(50) NOT NULL,
		EventDateUTC DATETIME NOT NULL,
		EventType CHAR(1) NOT NULL,
		TableName NVARCHAR(100) NOT NULL,
		RecordID NVARCHAR(100) NOT NULL,
		ColumnName NVARCHAR(100) NOT NULL,
		OriginalValue NVARCHAR(MAX) NULL,
		NewValue NVARCHAR(MAX) NULL
		CONSTRAINT PK_AuditLog PRIMARY KEY NONCLUSTERED (AuditLogID)
	)
GO

Using EF Code-First, the model classes for these tables will look like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace EFChangeTrackingDemo.Models
{
    [Table("Items")]
	public class Item
	{
        [Key]
		public System.Guid ItemID { get; set; }

        [Required]
        [MaxLength(100)]
		public string ItemDescription { get; set; }

		public virtual ICollection ItemAdditionalAttributes { get; set; }

        public Item()
        {
            this.ItemAdditionalAttributes = new List();
        }
    }
}

 

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace EFChangeTrackingDemo.Models
{
    [Table("ItemAdditionalAttributes")]
	public class ItemAdditionalAttribute
	{
        [Key]
		public System.Guid ItemAdditionalAttributeID { get; set; }

        [Required]
		public System.Guid ItemID { get; set; }

        [Required]
        [MaxLength(50)]
		public string AttributeName { get; set; }

        [MaxLength(500)]
		public string AttributeValue { get; set; }

        [ForeignKey("ItemID")]
        public virtual Item Item { get; set; }
    }
}

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations;

namespace EFChangeTrackingDemo.Models
{
    [Table("AuditLog")]
    public class AuditLog
    {
        [Key]
        public Guid AuditLogID { get; set; }

        [Required]
        [MaxLength(50)]
        public string UserID { get; set; }

        [Required]
        public DateTime EventDateUTC { get; set; }

        [Required]
        [MaxLength(1)]
        public string EventType { get; set; }

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

        [Required]
        [MaxLength(100)]
        public string RecordID { get; set; }

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

        public string OriginalValue { get; set; }

        public string NewValue { get; set; }
    }
}

In order to obtain the user ID that is making the change, in our DbContext class, we will override the SaveChanges() method to throw an exception requiring a user ID to be passed as a parameter, and create a new SaveChanges(string userId) method. Inside the new SaveChanges(userId) method, we can obtain all of the changes that were made since the last SaveChanges() call, and add records to the AuditLog table for each of those. The ChangeTracker property of the DbContext class contains all of the tracked entities, so pulling out the added, deleted, or modified entities is easy. If we have a function that returns the audit records for a single change, then the SaveChanges() code will look like this:

        // This is overridden to prevent someone from calling SaveChanges without specifying the user making the change
        public override int SaveChanges()
        {
            throw new InvalidOperationException("User ID must be provided");
        }

        public int SaveChanges(string userId)
        {
            // Get all Added/Deleted/Modified entities (not Unmodified or Detached)
            foreach (var ent in this.ChangeTracker.Entries().Where(p => p.State == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))
            {
                // For each changed record, get the audit record entries and add them
                foreach(AuditLog x in GetAuditRecordsForChange(ent, userId))
                {
                    this.AuditLog.Add(x);
                }
            }

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

In order to make the output of the entire contents of an entity easier to obtain, we can create an interface that has a Describe() method that can be placed on our model classes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace EFChangeTrackingDemo.Models
{
    public interface IDescribableEntity
    {
        // Override this method to provide a description of the entity for audit purposes
        string Describe();
    }
}

With this in place, our entities will look something like this:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace EFChangeTrackingDemo.Models
{
    [Table("Items")]
	public class Item : IDescribableEntity
	{
        [Key]
		public System.Guid ItemID { get; set; }

        [Required]
        [MaxLength(100)]
		public string ItemDescription { get; set; }

		public virtual ICollection ItemAdditionalAttributes { get; set; }

        public Item()
        {
            this.ItemAdditionalAttributes = new List();
        }

        public string Describe()
        {
            return "{ ItemID : \"" + ItemID + "\", ItemDescription : \"" + ItemDescription + "\" }";
        }
    }
}

So now the only remaining item to implement is the method that creates AuditLog entries from a changed entity. To complete the AuditLog entries, we need the table name (if one was specified via the [Table()] attribute, otherwise we will use the entity class name), the primary key value, and either the contents of the entire entity (for inserts and deletes), or the before-and-after values of the changed columns. For the contents of the entire entity, if the entity implements the IDescribableEntity interface, we will call the Describe() method to get the content data, otherwise we will just use the ToString() method. For updates, we will just loop through all of the properties of the entity and add an AuditLog record to the output for each one where the original value is not equal to the new value. The GetAuditRecordsForChange() method will then look like this:

        private List<AuditLog> GetAuditRecordsForChange(DbEntityEntry dbEntry, string userId)
        {
            List<AuditLog> result = new List<AuditLog>();

            DateTime changeTime = DateTime.UtcNow;

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

            // Get table name (if it has a Table attribute, use that, otherwise get the pluralized name)
            string tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;

            // Get primary key value (If you have more than one key column, this will need to be adjusted)
            string keyName = dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).Name;

            if (dbEntry.State == System.Data.EntityState.Added)
            {
                // For Inserts, just add the whole record
                // If the entity implements IDescribableEntity, use the description from Describe(), otherwise use ToString()
                result.Add(new AuditLog()
                        {
                            AuditLogID = Guid.NewGuid(),
                            UserID = userId,
                            EventDateUTC = changeTime,
                            EventType = "A", // Added
                            TableName = tableName,
                            RecordID = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),  // Again, adjust this if you have a multi-column key
                            ColumnName = "*ALL",    // Or make it nullable, whatever you want
                            NewValue = (dbEntry.CurrentValues.ToObject() is IDescribableEntity) ? (dbEntry.CurrentValues.ToObject() as IDescribableEntity).Describe() : dbEntry.CurrentValues.ToObject().ToString()
                        }
                    );
            }
            else if(dbEntry.State == System.Data.EntityState.Deleted)
            {
                // Same with deletes, do the whole record, and use either the description from Describe() or ToString()
                result.Add(new AuditLog()
                        {
                            AuditLogID = Guid.NewGuid(),
                            UserID = userId,
                            EventDateUTC = changeTime,
                            EventType = "D", // Deleted
                            TableName = tableName,
                            RecordID = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
                            ColumnName = "*ALL",
                            NewValue = (dbEntry.OriginalValues.ToObject() is IDescribableEntity) ? (dbEntry.OriginalValues.ToObject() as IDescribableEntity).Describe() : dbEntry.OriginalValues.ToObject().ToString()
                        }
                    );
            }
            else if (dbEntry.State == System.Data.EntityState.Modified)
            {
                foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)
                {
                    // For updates, we only want to capture the columns that actually changed
                    if (!object.Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))
                    {
                        result.Add(new AuditLog()
                                {
                                    AuditLogID = Guid.NewGuid(),
                                    UserID = userId,
                                    EventDateUTC = changeTime,
                                    EventType = "M",    // Modified
                                    TableName = tableName,
                                    RecordID = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),
                                    ColumnName = propertyName,
                                    OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),
                                    NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()
                                }
                            );
                    }
                }
            }
            // Otherwise, don't do anything, we don't care about Unchanged or Detached entities

            return result;
        }

I have used Code-First for this, and have avoided using the Fluent API for adding attributes to the model classes. You can use Model-First or Database-First and obtain the same results, but you will need to add the DbContext API code-generation item to your .edmx model and modify the templates to add at least the [Key] attribute to key columns. The Fluent API is a bit trickier: I’m not currently aware of a way to pull those attributes out of the entity, so you would need to find some other way to determine which column to output as the key value to duplicate this example.

To exercise this code, here is a simple example in an MVC 3 project:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using EFChangeTrackingDemo.Models;

namespace EFChangeTrackingDemo.Controllers
{
    public class HomeController : Controller
    {
        DemoDBContext db = new DemoDBContext();

        public ActionResult Index()
        {
            ViewBag.Message = "Welcome to ASP.NET MVC!";

            string userId = User.Identity.Name;

            Item newItem = new Item() { ItemID = Guid.NewGuid(), ItemDescription = "Test item" };

            db.Items.Add(newItem);
            db.SaveChanges(userId);

            newItem.ItemDescription = "Test 2";
            db.SaveChanges(userId);

            newItem.ItemAdditionalAttributes.Add(new ItemAdditionalAttribute() { ItemAdditionalAttributeID = Guid.NewGuid(), ItemID = newItem.ItemID, AttributeName = "Test Attribute", AttributeValue = "This is a test" });
            db.SaveChanges(userId);

            // Not that this will cascade delete down to remove the ItemAdditionalAttribute as well
            db.Items.Remove(newItem);
            db.SaveChanges(userId);

            return View();
        }

        public ActionResult About()
        {
            return View();
        }
    }
}

Your code will (hopefully) be more complex than this, but it’s enough to demonstrate how our audit process works. When a user visits the home page of the application, the controller will create a new Item record, save it, change the description, save it again, add a child record in the ItemAdditionalAttributes table, save again, and delete the Item record, which will also cascade the delete to the record in the ItemAdditionalAttributes table. If we run this and examine the AuditLog table, we will find the following (click to enlarge):

Note that the cascade delete captures the delete from both tables, and has records of all of the changes made between calls to SaveChanges(). Now that this is in place, any change made in the application that uses that DbContext class will automatically write audit information to the AuditLog table. Of course, in your own applications, you can do anything you want with the data, such as log it via your logging framework of choice, but with the new change tracking features in the DbContext API, you no longer have to do this manually in all of your data access code. By adding this code in one place, you can get audit coverage of your entire app. That is about as DRY as you can get. I have placed my sample project here for you to download.

Advertisements

4 responses to “Using Entity Framework 4.1 DbContext Change Tracking for Audit Logging

  • Daniel

    Great article!!! Exactly what I have been looking for!!!! Thank you!

  • Daniel

    Just one thought…Not sure why you wanted to avoid using the Fluent API? It removes the dependency of EF from your models.

    • jmdority

      The reason for avoiding the Fluent API was because there doesn’t appear to be a (documented) way to find out which property of the model is the [Key] attribute. Without that, you would have to have some other means of identifying the key for this example. I think this is probably also the reason why the MvcScaffolding templates have difficulty with the Fluent API. I like it, but not quite enough to give up using MvcScaffolding.

  • Phil

    Great work, thanks so much for sharing this!

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 )

w

Connecting to %s

%d bloggers like this: