Generic entity auditing in Linq to Sql

In many enterprise, or even non-enterprise, applications, there is generally a requirement for data change auditing.

For many applications this will come in the form of adding auditing columns to each table in the database. For example the fields, CreateDate, CreateUser, ModifiedDate and ModifiedUser would audit the username and timestamp of record creation and modification in each table.

Of course there are many ways to implement such auditing. In this article I will focus on an approach that I like when using Linq to Sql.

Overview

In general this approach uses a marker interface to decorate domain entities that require auditing and then uses the extension points of the Linq to Sql generated DataContext to do the auditing of the decorated entities. With the use of generics this can be done very simply with a few lines of code. The main benefit being that other infrastructure code - repositories, business logic etc don't need to worry about entity auditing at all.

Implementation

Let's first define our marker interface, which exposes the four auditing fields.

public interface IEntityAudit
{
    DateTime CreateDate { get; set; }
    string CreateUser { get; set; }
    DateTime ModifiedDate { get; set; }
    string ModifiedUser { get; set; }
}

The marker interface is used by the DataContext to determine which entities need to be audited when they are committed to the database. It also exposes the auditing fields so that the auditing implementation can access the fields generically.

Now let's implement the auditing. Fortunately Linq to Sql provides a very convenient extension point in SubmitChanges. SubmitChanges is called once the DataContext has determined the changeset, but before the entities are persisted.

public partial class MyStoreDataContext
{
    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {
        // Poor separation of concerns here with auditing, we'll revisit this very soon
        var auditDate = DateTime.Now;
        var auditUser = HttpContext.Current.User.Identity.Name;
   
        var changeSet = GetChangeSet();

        foreach (var insert in changeSet.Inserts.OfType<IEntityAudit>())
        {
            insert.CreateDate = auditDate;
            insert.CreateUser = auditUser;
            insert.ModifiedDate = auditDate;
            insert.ModifiedUser = auditUser;
        }

        foreach (var update in changeSet.Updates.OfType<IEntityAudit>())
        {
            update.ModifiedDate = auditDate;
            update.ModifiedUser = auditUser;
        }

        base.SubmitChanges(failureMode);
    }
}

The types and entities of changes to be committed are provided in the changeSet.Inserts and changeSet.Updates IEnumerables.
Using the nice .OfType<>() extension to IEnumerable the entities that have auditing (as indicated by the marker interface, IEntityAudit) are iterated to set the creation or modification audit values.
Lastly we call the base SubmitChanges to do the normal Linq to Sql persistence.

That is it! We have now built a generic auditing mechanism into our DataContext.

So how is it used?

Here is the domain entity in the database and the Linq to Sql designer:

Decorate the Product domain entity using the partial class

public partial class Product : IEntityAudit
{
}

Now whenever a Product is committed to the database as an insert or an update the auditing columns will be set.

public class ProductService
{
    public Product CreateProduct()
    {
        var product = new Product();

        using (var dataContext = new MyStoreDataContext())
        {
            dataContext.Products.InsertOnSubmit(product);
            dataContext.SubmitChanges(); // Here is where the Create audit is triggered
        }

        return product;
    }

    public void SetProductName(int productId, string productName)
    {
        using (var dataContext = new MyStoreDataContext())
        {
            var product = dataContext.Products.Single(p => p.ProductId == productId);
            product.ProductName = productName;
            dataContext.SubmitChanges(); // Here is where the Update audit is triggered
        }
    }
}

In my next blog post I will show how the concern for updating the audit fields (date and username) can be separated from the DataContext and extended to support multiple change processors.