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

Executing .ps1 files in a DockerFile

This week I was trying to containerize an existing java application. Part of "installing" the application  on the container required executing an PowerShell script in the container during the Image build. Based on the documentation here  I thought i could add the following command to my dockerfile and it would work: RUN install.ps1 However, when I went to build the image, it just hung on that step. I tried several other variations of the run command including: RUN ["Powershell", ".\install.ps1"] which resulted in the following error: '["Powershell"' is not recognized as an internal or external command,operable program or batch file. RUN ["Powershell.exe", ".\install.ps1"] which returned the same error as above. I was about to give up and move the PowerShell commands from the .ps1 file directly into the dockerfile itself as described here , but I had an "A HA!" moment and decided to give a simpler a

Get NodeAuthorization working in Kubernetes with acs-engine

Node Authorization in k8s I'm starting to get into the container world and I'm loving it. Recently we helped a client build out and deploy a micro-services application in Kubernetes. We created the cluster in Azure using the open source project  acs-engine . After we got the cluster set up, our client asked for some updates to the cluster for security reasons. One of those updates was to enable Node Authorization . What is Node Authorization? Node Authorization locks down each Node in the cluster to only be able to do actions on itself. If this is not turned on, its possible for a malicious pod to take actions on any other node, including reading secrets, deleting pods, etc. There is an excellent post by Antoine Cotten that explains this very well ( as well as RBAC, which is a different subject altogether). How do I set it up? Based on the current documentation, it looks like setting up Node Authorization should be easy. Basically follow these steps Turn on TLS

Keep a website alive – PowerShell style

  Recently, We had a website that didn’t have frequent visitors, but when the visitors did come, the website would take a long time to load up the first time. This is expected behavior because IIS shuts down its worker threads. 1 approach would be to set the IdleTimeout to 0 which means the threads are never aborted (details here: http://technet.microsoft.com/en-us/library/cc771956(v=ws.10).aspx ). Instead of that though I decided to try my hand at PowerShell and came up with the following script: 1: # List of URLS to Ping 2: $urls = @( "Http://URL1.com" , "https://URL2.com" ) 3:   4: #Ping all URLs in the list 5: foreach ($objItem in $urls) { 6: $req=[system.Net.HttpWebRequest]::Create($objItem); 7: $res = $req.getresponse(); 8: $stat = $res.statuscode; 9: $res.Close(); 10: 11: #Pump it out to a text file 12: $res | Out-File pingresults.txt -append 13: } After that I set up a simple