Not long ago I blogged about auditing with NHibernate.Envers. I’m using it again in another project but I don’t want to audit all the fields on the entity; furthermore, Envers automatically tries to audit referenced tables, so tables that are not configured to be audited cause an error message: An audited relation from SomeAuditedEntity to a not audited entity NonAuditedEntity! Such mapping is possible, but has to be explicitly defined using [Audited(TargetAuditMode = RelationTargetAuditMode.NotAudited)]. Understandably I expected that adding this attribute to the properties that I didn’t want auditing would prevent this behaviour, but it didn’t. I then added it to the class that I didn’t want auditing, but the message still didn’t go away. A quick google revealed another similar attribute, NotAudited, but this didn’t work either.

Finally after hacking around with my code I stumbled across the solution; the properties to be excluded must be defined in the Envers configuration. Although I think I’d prefer to annotate the fields in the entity class itself, this is the solution that worked for me:

var enversConf = new NHibernate.Envers.Configuration.Fluent.FluentConfiguration();
enversConf.SetRevisionEntity<REVINFO>(e => e.Id, e => e.RevisionDate, new AuditUsernameRevInfoListener());
enversConf.Audit<SomeAuditedEntity>()
          .Exclude(x => x.NonAuditedProperty1)
          .Exclude(x => x.NonAuditedProperty2)
          .ExcludeRelationData(x => x.NonAuditedCollection;
configuration.IntegrateWithEnvers(enversConf);

I use two different types of auditing in my various projects – low-level auditing, where individual property changes are recorded, and high-level, where I record actions (ie. created x, edited y). For the high-level auditing, I record each ‘action’ using a class, which inherits from a base Audit class. The basic audit class is as follows:

public class Audit : Entity {
    public virtual string CreatedBy { get; set; }
    public virtual DateTime CreatedOn { get; set; }
    public virtual Func<HtmlHelper,MvcHtmlString> Text {
        get { return html > MvcHtmlString.Empty; }
    }
}

CreatedBy and CreatedOn are both fairly obvious properties, recording when the audit was created and by whom. The Text property is used for showing the audit information in views and provides an easy and highly flexible way to display the audit text, as will be demonstrated shortly. As I said, each auditable ‘action’ is represented using a new class. These classes are subclasses of Audit, and can contain additional fields. For example, I may have an audit for the creation of a new user:

public class UserCreateAudit : Audit {
    public virtual int UserId { get;set; }
    public virtual string Username { get; set; }
    public virtual Func<HtmlHelper,MvcHtmlString> Text {
        get {
            return html => MvcHtmlString.Create(
                string.Format("Created new user {0}", Username)
            );
        }
    }
}

In order to store the audit data, we can use NHibernate’s inheritance mapping. NHibernate supports three strategies for polymorphism – table-per-hierarchy, table-per-class, and table-per-concrete-class. Table-per-class and table-per-concrete class are similar, storing the data of different subclasses in separate tables, while table-per-hierarchy stores the Audit class and any subclasses in a single table, containing all the entities in all the classes. I use table-per-hierarchy because it uses fewer joins so reading/writing is faster, although the trade off is that there will be many irrelevant columns stored for each audit type. Modern database management systems work very efficiently, minimising the effects of storing null/redundant data.

Configuring NHibernate for Polymorphism

Setting up inheritance is really easy:

public class AuditAutoMappingOverride : IAutoMappingOverride
{
    public void Override(AutoMapping mapping)
    {
        mapping.DiscriminateSubClassesOnColumn("Type");
    }
}

Ok, that’s a bit unfair. We use FluentNHibernate.AutoMapper, so if you do too then you can simply override the mapping; for everyone else, sorry but you’ll have to look it up yourself! Honestly though, it isn’t hard! I really just wanted to show you how NHibernate acheives polymorphism: it uses an extra column, in our case called ‘Type’, which acts as a discriminator. Each subclass of Audit will have it’s own discriminator, which is by default the full name of the type, and this is how NHibernate can differentiate between audit types.

Why is this good for auditing then?

There are three reasons why using polymorphism and NHibernate’s inheritance mapping strategies work well with the style of auditing that I use:

  1. Using a separate class per audit type makes it really easy to maintain the various audit types and the data relevant to each one. For instance when auditing that a user created a new task, in addition to the CreatedOn and CreatedBy fields, I want to store the task id, the type of task created, and the due date of the task; which results in a clear to read audit subclass:
    public class TaskCreateAudit : Audit {
        public virtual int TaskId { get; set; }
        public virtual TaskType TaskType { get; set; }
        public virtual DateTime DueDate { get; set; }
    }

    Also, if multiple subclasses have the same property NHibernate will aggregate them all when mapping the database table. For example if I have another audit class that has the properties DueDate and AnotherProperty, then the resulting table will have the fields Type, CreatedBy, CreatedOn, TaskId, TaskType, DueDate and AnotherProperty (note only a single instance of DueDate)

  2. The text shown when displaying the audit data is highly customizable, as a result of using separate classes for each audit type. I find using a Func<HtmlHelper,MvcHtmlString>  is a really simple, elegant, and flexible way to determine how the audit information is displayed in the view.
    public class TaskCreateAudit : Audit {
        public virtual int TaskId { get; set; }
        public virtual TaskType TaskType { get; set; }
        public virtual DateTime DueDate { get; set; }
        public override Func<HtmlHelper,MvcHtmlString> Text {
            get { return html => MvcHtmlString.Create(
                "Created new task (id #" + TaskId + ")"
            ); }
        }
    }

    Using @Model.Text.Invoke(Html) in a view will display: Created new task (id #1001). If later on I decide that actually I would rather link to the task instead, and show the due date in the audit, all I need to do is change the class definition:

    public override Func<HtmlHelper,MvcHtmlString> Text {
        get { return html => html.ActionLink(
            "Created new task due " + DueDate.ToString(),
            "Index", "Tasks", new { id = TaskId }, null
        ); }
    }

    And now the audits will display as: Created new task due 01/01/2012

  3. Finally, aside from ease of use and flexibility, auditing in this way allows for really easy querying of the data. For instance, to query all audit entries, I can use:
    _session.Query<Audit>();

    Similarly, if I want to query only a certain type of audit, I can use:

    _session.Query<TaskCreateAudit>();

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)

[HttpPost]
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                                             */
    _session.Save(UserEditAudit.Create(
                        LoggedInUser.UserName,
                        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
                    .Connection
                    .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;
}