Easily adding auditing to a Entity Framework Code First project

categories: [ Data ] tags: [ Auditing ] [ Entity Framework ]
created: 05 Jan 2017 @ 13:37 modified: 24 Apr 2017 @ 13:57

If you've done any amount of development where you need to interact with data you'd know that at some point customers or stakeholders always want to know who made that change or when was that change made. To answer these questions you'd look to your auditing which in most cases is the last thing people think about adding into their apps for some reason.

This post will show you how to easily do auditing with entity framework projects. We'll also mention a little gotcha found and a sort of hack around fixing it.

Setup

We going to do some setup first for anyone following along from scratch but feel free to skip to the implementation part to see the auditing specific bits.

Project

First off we going to create a new ASP.NET Web Application

and we'll choose the MVC template

devenv_2017-01-05_11-50-51

If we look in the IdentityModels.cs we'll see a ApplicationDbContext class that's we'll just add to for this sample

ZoomIt64_2017-01-05_11-54-03

Let's pull that out into it's own class file to make it easier to find Smile

devenv_2017-01-05_11-55-54

Our project is ready to use now

Scenario

We going to create a simple contact list that we'll scaffold. To do this we'll start by creating a new class in the Models folder called Contact and we'll add some basic properties to it

devenv_2017-01-05_12-00-28

Next Build your solution and then add a new MVC 5 Controller with views, using Entity Framework 

devenv_2017-01-05_12-02-48

Next select the

  • Contact class as the Model
  • ApplicationDbContext class as the Data context
  • Use async controller actions if not ticked
  • Default layout page if it's blank
  • Name of the controller as ContactsController

devenv_2017-01-05_12-03-58

Your solution should be able to run, browse to /Contacts

iexplore_2017-01-05_12-07-49

You will see all the CRUD that was generated for us and it should all work

iexplore_2017-01-05_12-08-54

All the setup is complete and we can now continue on to add in the auditing.

Implementation

For our implementation we are going to create an Audit class with the properties we'd like to capture as well as a bit of logic in the context class to intercept Save operations to add in our auditing.

Create a Audit class in the Models folder

devenv_2017-01-05_12-17-20

With an implementation like this we'll link to a logged in user if we have one otherwise we'll expect to have null's if nobody is logged in

Next in the context class (ApplicationDbContext is this example) we need to add the audits table

We'll also need to override the SaveChanges method

For the code we'll need to add a reference to System.Transactions as well as

devenv_2017-01-05_12-25-49

as well as the OwinContextHelper class

Now add the logic below to the SaveChanges method

This logic requires a couple of helper methods which you can find below

Migration

Because we've changed the database structure in our context let's do the database migrations. Open the Package Manager Console

Start by enabling migrations if you haven't already using the command Enable-Migrations

devenv_2017-01-05_12-47-16

Then add the migration using the command Add-Migration AddingAuditing

devenv_2017-01-05_12-48-45

and lastly apply this migration to your database by running the Update-Database command

devenv_2017-01-05_12-49-48

Testing

If we now Build and play with our app you'll notice that all the data changes are audited

Ssms_2017-01-05_13-06-05

If we look at just the from and to json you can see how we

  • Created John Doe which has a from json as empty and to json with data
  • We then modified that record but here it shows the same from and to json (more on this next)
  • Finally we deleted the record

Ssms_2017-01-05_13-08-44 

Tracking State

As you can see above the state of our entity seems to not have changed, this is because the Entity didn't actually come from the database and so it wasn't able to track values changing in the object. If we look in the ContactsController at the Edit post method

We can see that the Contact object is populated from the post data and then the entry is linked to the context and the State is switched to Modified. If in our auditing logic we try call the GetDatabaseValues method we get still the same values which match the CurrentValues property values. The solution/hack for this would be to change the code to be something like below

What we are doing now is finding the Contact in the context then modifying it's values, you could set each property but I'm using this ReflectionHelper class which basically just looks through the first object and copies it's value to the other so if I add new properties I don't need to come back and update this logic. Changing the code like this adds some overhead because we are now getting the values each time we use it instead of just linking the object to the context and saying it has changed but if you need to audit you need to audit Smile with tongue out. Another solution could be to only track the To values and then as part of your auditing you review the previous audit record to view what the From is which will save you space for your data and will be quicker at runtime Smile.

Testing

If we test now we can see that the From and To json values show the change as we expect

Ssms_2017-01-05_15-31-55

Failsafe

Because we can't always rely on us just doing the right thing and wouldn't want to have to go and check each change that we are auditing correctly we can uncomment the below code which is in the context code which will check if the from and to values are the same and throw an exception if they are the same.

You can run this piece of code in the #if DEBUG pragma so that it only errors while you debugging so that your system isn't affected by this in live while you changing all the save operations to get the objects before updating them.

Conclusion

Auditing is an imported part of most applications and you should really think about how you want to handle it. In this example we used bits available to us with the entity framework to do auditing but this also means that we not auditing if changes are made via other mechanisms. Perhaps you could do your auditing with a trigger on tables in the database if you need to capture changes outside your Entity Framework code which for bigger applications would probably be the case.

The code for this sample is on GitHub if you want it Smile