ORM with built-in audit trail

Question

I am designing an ASP.NET MVC web app with a database backend that requires a full audit trail for regulatory purposes.

I've implemented audit trails in the past but it feels like I would be safer using an ORM tool with built-in features for this. It looks like NHibernate would be one way to go - could you recommend other options?

To clarify - I am not interested in a debate or ORM comparisons. I am effectively asking what ORM tools have built-in support for audit trails.

If you're going with a mini orm like dapper, you could include http://miniprofiler.com/ into your applications. — Alex, Jul 27 '12 at 17:47
Thanks for the suggestion, I think miniprofiler could be useful. If you could suggest other ORMs with build-in audit trails I would have a good starting point to research the best option. — alexsome, Jul 30 '12 at 13:04
I know this isn't quite what you are asking, but EventSourcing is another option. then persistence and audit trail is the exact same thing. — Roger Johansson, Oct 17 '12 at 11:19
NHibernate can be extended using Interceptors, which can be used for auditing. I once used an interceptor to log all changes in certain tables, including old and new value. — Dirk Trilsbeek, Oct 17 '12 at 11:20
Also [AuditDbContext for EntityFramework](http://auditdbcontext.codeplex.com/) seems to solve this task. — alex.b, Oct 17 '12 at 11:25

score 1 · Answer 1 · answered Oct 17 '12 at 12:33

I think you can intercept the actual db operation in all full ORMs (but usually not in a micro-orm like dapper). You can catch new records, deletes, and modifications including the original, and modified state of the object.

Here is an example in Linq2SQL, that uses Newtonsoft to dump the properites to a StringBuilder (add the code to your DataContext class):

    public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
    {
        var changes = GetChangeSet();
        var inserts = changes.Inserts;
        var deletes = changes.Deletes;
        var updates = changes.Updates;

        var sbLog = new StringBuilder();

        sbLog.AppendLine("Inserts:");
        sbLog.AppendLine(Newtonsoft.Json.JsonConvert.SerializeObject(inserts, Newtonsoft.Json.Formatting.Indented,
            new Newtonsoft.Json.JsonSerializerSettings { ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore }));

        sbLog.AppendLine("Deletes:");
        sbLog.AppendLine(Newtonsoft.Json.JsonConvert.SerializeObject(deletes, Newtonsoft.Json.Formatting.Indented,
            new Newtonsoft.Json.JsonSerializerSettings { ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore }));

        sbLog.AppendLine("Updates:");
        foreach(object x in updates) {
            var original = this.GetTable(x.GetType()).GetOriginalEntityState(x);

            sbLog.AppendLine(Newtonsoft.Json.JsonConvert.SerializeObject(new { original = original, mod = x },
                Newtonsoft.Json.Formatting.Indented,
                new Newtonsoft.Json.JsonSerializerSettings { ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore }));
        }

        //"logger" can be anything, you use to log the changes...
        logger.Info(("db operations:" + Environment.NewLine + sbLog.ToString()).Replace(Environment.NewLine, Environment.NewLine + " | "));

        base.SubmitChanges(failureMode);
     }

ORM with built-in audit trail

1 Answers1