Audit Trails, Concurrency and Soft-Deletion with Entity Framework

audit trails, concurrency and soft-deletion

Audit Trails

When developing enterprise applications, audit trails are almost always used, and, if not, they are at least taken into consideration. “What is an audit trail?”, you might ask. You can think of it is as:

“Who did it, and when did they do it?”

One common strategy is to save this information in the same record in the database. Fields that you might want to save in each auditable row are:

  • Created By
  • Created Date/Time
  • Edited By
  • Edited Date/Time

Concurrency

Another common consideration is concurrency – making sure that the object that I am saving into a database has not been modified since I last viewed it. An approach that one may take when dealing with concurrency is to not allow an object to be saved if it has been changed since it was loaded. Such an approach is called optimistic concurrency, and we are resolving exceptions with a reload (i.e. the database wins). Common fields saved in each versionable row are:

  • A timestamp, or
  • A version number

The chosen field can then be used to ensure that the value has not changed since it was loaded.

Soft-Deletion

One other thing that we have to keep in mind are foreign keys and maintaining the referential integrity of the relationships in a database. We don’t necessarily want to perform cascade deletes! Think about the two scenarios below:

  1. We have a system that requires application users to log in, but when a user is removed from the system, their actions, audit trails etc need to still be available.
  2. We have a clients and payments system, where we might want to remove a client from our database, but we will definitely still want to keep that client’s transaction records (and yet still keep the information about that client for reporting and accounting purposes).

Implementation Using Entity Framework

Microsoft’s Entity Framework (EF) is a really great object-relational mapper (ORM) that makes connecting to a database really easy. However, when developing enterprise applications, there is a little bit more work that needs to be done to enable audit trails, concurrency and soft-deletion described above. Let’s start building a solution!

Interfaces

We have identified three behaviours that an object may have:

IAuditable

IDeletable

IVersionable

In addition to this, let’s add an interface defining an object that has a Guid as a primary key:

IId

Database

Create a database on Microsoft SQL Server, and run the following script to add the ApplicationUser table:

Notice that the following fields have been included in the table:

  • Version
  • CreatedById
  • CreatedDateTime
  • EditedById
  • EditedDateTime
  • IsDeleted

Model

EDMX Generation

In a Visual Studio project, add a new ADO.NET Entity Data Model: EF Add Model Audit Trail Choose to generate the model from the database: EF Generate From Database Connect to the database you created earlier, and allow Visual Studio to save the connection string in App.config: EF Connection Audit Trail Include the ApplicationUser table: EF Table Selection Audit Trail We need to modify two of the fields in the CSDL (conceptual schema definition language) in the EDMX designer:

  1. The ApplicationUserId field – change the Name property to Id, and set the StoreGeneratedPattern to be Identity. This will enable us to inherit from the IId interface defined above, and EF will automatically generate a Guid for insertion. EF Guid Id Generated
  2. The Version field – change the Concurrency Mode to Fixed:EF Version Concurrency

Partial Domain Classes

When EF generates the domain classes, they are created as children of the AuditTrail.tt (T4 Template), which is a child of the .edmx file: EF Solution Model Audit Trail The contents of the generated ApplicationUser.cs file:

As intimated in the comments, we should never change these files. Luckily, the class has been marked with the partial  keyword, which means that we can complete this class in another file. Add a new file called ApplicationUser.Custom.cs:

We don’t need to do anything to this class as the generated domain class already conforms to the interfaces!

Partial DbContext

Another class that the .edmx file is responsible for generating is the database context class (inheriting from DbContext), which is responsible for the storing and fetching data from the database. Luckily, this class has also been marked as partial. The  DbContext class has a virtual method,  int SaveChanges(), that is called before any of the changes made are actually persisted to the database. Overriding this method will allow us to deal with audit trails, concurrency and soft-deletion in one place. Add a new file called AuditTrailDB.Context.Custom.cs, and override the int SaveChanges() method:

In this method, we need to implement the soft-deletion, concurrency and audit trails (in that order). For soft-deletion, we will fetch all of the changed entries that implement the IDeletable interface. If the entry has been deleted, we will set the entity’s state to EntityState.Unchanged, and then set the entity’s IsDeleted flag to true. I know what you’re thinking – “What?! Why are we setting it to unchanged?!” – it seems that setting the state to EntityState.Modified actually flags every property as modified, which throws a spanner into the works when doing the audit trail. Setting the IsDeleted flag sets this entity’s state to EntityState.Modified the next time we query the ChangeTracker anyway, so we’re safe!

For concurrency, we will fetch all of the changed entries that implement the IVersionable interface (this will include the IDeletable items whose states are now EntityState.Modified).

  • If the entry has been added, we will set the entity’s Version to 1.
  • If it has been modified, we will increment the Version.

For audit trails, we will fetch all of the changed entries that implement the IAuditable interface (this will include the IDeletable items whose states are now EntityState.Modified).

  • If the entity has been added, we will set the CreatedById and CreatedDateTime properties to mark who created the entity and when this was done. We will also set the EditedById and EditedDateTime properties with the same values (the creator is the last person to edit this entity).
  • If the entity has been modified, we will set the EditedById and EditedDateTime properties to mark who edited the entity and when this was done. We will also need to make sure that the CreatedById and CreatedDateTime properties have not been modified.

Did you see the aforementioned spanner? Just in case you didn’t, take a look at the highlighted modification check on the created audit fields. If an entity I was trying to soft-delete had its state set to EntityState.Modified, these flags would be set to true and it would appear as though the audit values were changed, resulting in an exception! Note: I am using a static class, CurrentSession. There is a static property in this class called CurrentApplicationUserId whose value is set when a user logs in. For the purposes of this post, it is created as a static variable for desktop applications. If you were to use this for web applications, you would need to store the user id in the HttpContext session variables. While we’re at it, why don’t we add a check to make sure that the user is not trying to modify the primary key:

Using/Testing the Solution

Like all good developers, we always add tests to our projects right? Here are the common variables that I will be using in each of the examples:

Inserting and Selecting

We want to make sure that we can persist a new entity to the database, and that the entity that we persisted contains the values we intended it to.

Updating

We want to make sure that fetching an entity from the database, editing it and then saving it again persists the correct values:

Soft-Deletion

We want to make sure that saving an entity to the database leaves the entity in a non-soft-deleted state, and that when we do delete it, that the entity is in a soft-deleted state:

Concurrency

We want to make sure that when we create an entity, that the version number is 1, and that when we update an entity, that the version number is incremented:

Audit Trails

We want to make sure that the correct information is saved in the audit trail on creation and when editing an entity:

We also want to make sure that when a user tries to update the created audit fields, that a validation exception is thrown (this assertion is declared in the one of the test method’s attributes):

Download the Solution

If you would like to download the complete source code, the Visual Studio 2012 solution, projects and code (without the NuGet packages) are available here

by João Lourenço
Loading Facebook Comments ...