In most of the projects I’ve worked on, auditing has been a fairly high level affair – typically recording user actions, such as user edited entity x or user deleted child entity y. This has been adequate for most of our systems where we do not need to be able to see exactly all the modifications made to an entity. However, for a recent system this style of auditing has been causing issues; on several occasions we’ve had requests from clients saying “a property on this entity is not as expected, but it was correct x days ago, can you see who changed it?”. And unfortunately, unless only a single user has edited the entity, we cannot see who made the change. And that really isn’t good enough…

So I took a look at¬†NHibernate.Envers, a project which facilitates easy entity versioning with NHibernate and would therefore enable me to save an in depth history of every version of a particular entity. The docs weren’t great I found, but Giorgetti Alessandro over at PrimordialCode has a great series of posts covering virtually everything you need to know: a quick introduction, querying (part 1 and part 2) and customising the revision entity. If you’ve got some time to spare, I’d suggest you to read the introduction before continuing, as it’s a great article with some really valuable information, and clearly demonstrates the database structures generated by Envers.

 Wiring Up .Envers with Existing Auditing

After reading the PrimordialCode posts, I was able to really quickly get Envers up and running. In order to link our existing high-level action based auditing up with the detailed low-level information provided by Envers, I needed to store the revision id as part of the existing audit, which was really easy to access using IAuditReader.GetCurrentRevision(bool persist)

public ActionResult Edit(int id, UserViewModel model)
    var auditer = _session.Auditer();
    var user = _userBuilder.BuildEntity(model, id);
    var revisionInfo = auditer.GetCurrentRevision<REVINFO>(true);

    /* Create an instance of UserEditAudit which references the
       revision info                                             */
                        user, revisionInfo));

    return RedirectToAction("Overview", new { id });

Displaying Audit Data

Now we have the revision id for the audit, we can create a view to display the revision data. Out of the box, Envers enables you to query entities at a particular revision, and also query the revision data (to get the timestamp). Unfortunately, using the built in audit reader generates very inefficient sql with multiple and unnecessary queries.

Furthermore, I wanted to be able to show not simpy the version of the entity, but the differences between it and it’s previous version. Envers doesn’t support this behaviour out of the box, so to implement it I needed to load the two versions of the entity and diff them myself. Because of the inefficiencies of looking up entity versions using Enver’s audit reader, I wrote a generic method that could loaded the two versions of any auditable entity using a single sql query with dapper:

private IEnumerable<T> EntityAndPreviousRevision<T>(int entityId, int revisionId) where T : Entity<T>
    var sql = string.Format(
        @"select * from dbo.{0}_AUD where {0}Id = @entityId and REV in (
         select top 2 a.REV
         from dbo.{0}_AUD a with (nolock)
         inner join dbo.REVINFO r with (nolock) on a.REV = r.REVINFOId
         where a.{0}Id = @entityId and a.REV <= @revisionId
         order by r.RevisionDate desc )", typeof(T).Name);
    return _connectionProvider
                    .Query<T>(sql, new { entityId, revisionId });

I then diffed the two classes by simply using reflection to iterate through each of the properties on the entity and checking for any non-matches:

public class PropertyChange
    public string From { get; set; }
    public string To { get; set; }

    public bool IsDifferent()
        if (From == null && To == null) return false;
        if (From == null || To == null) return true;
        return !From.Equals(To);

private static Dictionary<string,PropertyChange> DifferencesBetweenRevisions<T>(T from, T to)
    var properties = typeof (T).GetProperties();
    var differences = new Dictionary<string, PropertyChange>();
    foreach (var property in properties)
        var pc = new PropertyChange
            From = (property.GetValue(from, null) ?? String.Empty).ToString(),
            To = (property.GetValue(to, null) ?? String.Empty).ToString()
        if (pc.IsDifferent()) differences.Add(property.Name, pc);
    return differences;