Skip to main content

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!

Comments

Popular posts from this blog

Build/Deploy Windows service with TFS

Building and deploying a web service or website via TFS is pretty straight forward, but automatically deploying a windows service or console application takes a b it of setup. I’ve outlined the steps below to get your service deployed. Step 1: Set up Automated Build and MSDeploy on the Target server. If you are doing any sort of automated build/deploy I recommend using MSDeploy. You can follow steps 1-3 from a previous post here.Step 2: Edit the .csproj fileThe project file for the app you are trying to deploy needs to be edited so that TFS will create a directory for the output of the project. Otherwise the project will be built, but the assemblies will be placed in the code drop location with no organization to it.To edit the file, go to the open file dialog and locate the csproj file. Select it but don’t click “Open. Instead click on the dropdown arrow next to the Open button, select “Open with”, and choose the XML (Text) editor as shown:Once the file is open, add the following “Pr…

MSDEPLOYAGENTSERVICE 401 unauthorized–Resolution

We recently migrated a production environment for a client to new Servers. I had previously been using MSDeploy to deploy the websites/services to the servers so I figured all I had to do was install MSDeploy, point Update my deploy scripts to point to the new servers, and deploy! I was using MSDeploy 2 on the previous servers so I figured it would work on the new ones. Unfortunately it didn’t turn out to be that easy.
When I ran the updated scripts I got the following error:
Fatal: Request to remote agent URL 'http://myserver/MSDEPLOYAGENTSERVICE' failed.
Fatal: The remote server returned an error: (401) Unauthorized. Fatal count: 1
I was using an admin account and I could hit that URL above in a browser so I knew it wasn’t an authorization issue.
Here are the things I tried that DIDN’T work:
Uninstall/Reinstall MSDeploy 2Install MSDeploy 3Create the fake user group on the server per these instructions:http://www.iis.net/learn/publish/troubleshooting-web-deploy/web-deploy-er…

Quickly and Easily Deploy Websites/Web Services with TFS Build via Web Deploy (MSDeploy)

When I first started deploying code from TFS I took the simple approach and created a batch file and deployed the websites via RoboCopy. I’m a very “Keep it simple” kind of guy, so this worked for us for a long time and so nothing was changed. With my most recent project however, we were deploying code over a slow VPN tunnel from our servers in Chicago to servers located in Europe. Due to this, the RoboCopy was taking over 4.5 hours to complete. I needed a better/faster way so I started looking into Web Deploy (MSDeploy). I was able to get it working fairly easily and I was pleasantly surprised how easy it was to get it working, and how much time its saved me! I can now deploy the code in less than 20 minutes!I’ve outlined the process in detail below, but in general you only need to do this:Add MSBuild parameters to your automated buildCustomize the deployment parameters for the websiteCreate a batch file to an auto-generated MSDeploy scriptExecute the batch file from the automated bu…