Quick and easy table Audit with code first EF6 and Migrations

For one of my current projects, we had a requirement to create track changes made to two tables in the database for auditing purposes. Initially I thought we could just use the built in SQL change tracking features (here), however we were deploying this application to Azure and as of now, that feature is not available in SQL Azure.
I did some investigating into this and there were a few options:

Option 1: Create my own triggers on these tables and write data to the audit tables when the rows were updated.

This was some what straight forward, but I’m not a fan of triggers and I wanted to keep all of my code in C#/Entity Framework for ease of code management down the road. Also, I didn’t want to have to update the triggers when the table was altered

Option 2: Implement this work around leveraging a Local data cache

While this seems like an easier approach, I wouldn’t be fully in control of what was happening during updates. Also, this approach only tells you what rows have changed and not what the before values where. I needed to be able to tell what any change to the row was and its historical values.

Option 3: Build my own using EF Inheritance and Migrations

Building your own is always the most fun option anyway isn’t it?
I decided that I would use Migrations and the built in inheritance features of EF and override the SaveChanges function to write to my audit tables when needed.
Note: For my project I was using EF Code first with migrations, so if you are not using code first or migrations, this may take some massaging.
There are 3 steps to this
Step 1: Setting up the tables
Before I set up the auditing, I had a single object/table called “Claim” and it was defined as shown:
    public class Claim
    {
        [Key]
        public long ClaimId { get; set; }
        [Required]
        public string Title { get; set; }
        public DateTime? ClaimFiledDate { get; set; }
        [Required]
        public DateTime CreateDateTime { get; set; }
        public DateTime? UpdatedDateTime { get; set; }
       
        ...
    }
The class was pretty straight forward leveraging Data Annotations to enforce rules in the MVC View and in the database when creating migrations. For my auditing table, I wanted all of the same columns including the primary key along with a surrogate primary key for query optimization. I could have just created another table definition with the same columns, but then every time I wanted to make a change to the Claims object, I would have to remember to make the same change to the Audit table.  If we use inheritance and leveraging the “magic” of migrations, we can get there with minimal code.
What I ended up doing was creating an abstract base class with most of the data columns in it and then creating two derived classes, Claim and ClaimAudit. Making it abstract means that EF won’t try to create a table for it when you create migrations.
Here is what they ended up looking like:
    public abstract class ClaimBase
    {
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        public virtual long ClaimId { get; set; }
        [Required]
        public string Title { get; set; }
       
        ...
    }
Claim:
    public class Claim: ClaimBase
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public override long ClaimId { get; set; }
        [Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
        public DateTime CreateDateTime { get; set; }
        public DateTime? UpdatedDateTime { get; set; }
        public bool? IsDeleted { get; set; }
       
        ...
    }
Claim Audit:
public class ClaimAudit : ClaimBase
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public long ClaimAuditId { get; set; }
    [Required, DatabaseGenerated(DatabaseGeneratedOption.Computed)]
    public DateTime TimeStamp { get; set; }
}
A few things to mention:

• You will have to mark up the ClaimId primary key in the base class as DatabaseGeneratedOption.None so that you don’t get issues with multiple primary keys
• You override that markup in the derived class to set to Identity for the Claims table since it is, but you do not override it in the audit table since you will be storing multiple versions of that key in the audit table
• You create a new surrogate primary key on the audit table for query purposes

Step 2: Update “OnModelCreating”
Event though we’ve set up the inheritance in the code, we need to call out a few explicit things in the OnModelCreating function to enforce our primary keys and inheritance. The MapInheritedProperties and “toTable” function is what allows us to define the columns in the base class and have them created in the derived class. As for the HasKey, I don’t remember exactly why I had to use that, but essentially its reinforcing the primary keys that are defined in the markup.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<ClaimAudit>().HasKey(k => k.ClaimAuditId).Map(m =>
    {
        m.MapInheritedProperties();
        m.ToTable("ClaimAudits");
    });

    modelBuilder.Entity<Claim>().HasKey(k => k.ClaimId);

    base.OnModelCreating(modelBuilder);
}



Step 3: Run Up Migration

Now that we’ve updated the classes, when we create the migration script that creates the audit table, it automatically adds the columns from the Claimbase class:

CreateTable("dbo.ClaimAudits",
c => new
    {
        ClaimAuditId = c.Long(nullable: false, identity: true),
        TimeStamp = c.DateTime(nullable: false, defaultValueSql: "GETUTCDATE()"),
        ClaimId = c.Long(nullable: false),
        Title = c.String(nullable: false)
    })
.PrimaryKey(t => t.ClaimAuditId);



Step 4: Override SaveChanges

Ok, the tables are set up and created. The last step is to actually write the data to the audit table when things change. To do this, I used AutoMapper to map the Claim and ClaimAudit objects and added a row to the ClaimAudit table every time a Claim object changed. note that for new Claim Objects, I had to save them in a list and only add them to the ClaimAudit table once they were successfully added to the DB so that the ClaimID value was properly populated



public override int SaveChanges()
{
    Mapper.CreateMap<Claim, ClaimAudit>();

    var newClaims = new List<Claim>();

    //For each Claim, create and audit record if its an update. else wait until its been saved to the DB so we can get the ClaimId
    foreach (var dbEntityEntry in ChangeTracker.Entries<Claim>().Where(e => e.State != EntityState.Unchanged))
    {
        if (dbEntityEntry.State == EntityState.Added)
        {
            newClaims.Add(dbEntityEntry.Entity);
        }
        else if (dbEntityEntry.State == EntityState.Modified)
        {
            var audit = Mapper.Map<ClaimAudit>(dbEntityEntry.Entity);
            ClaimAudits.Add(audit);
        }
    }

    base.SaveChanges();

    foreach (var newclaim in newClaims)
    {
        var audit = Mapper.Map<ClaimAudit>(newclaim);
        ClaimAudits.Add(audit);
    }
   
    return base.SaveChanges();
}



That’s it Simple and relatively easy Database auditing in Azure with EF 6 and Migrations. Enjoy!