Auditing inserts and updates using LINQ to SQL

Any time you have an application where multiple people fill different roles in the application, you probably have a need to audit at least some of the changes that those people can make in your database. Sometimes this might be for security purposes; other times you may want to be able to quickly restore the state of a particular row or rows in the database. I often do auditing for these purposes. Recently I discovered another use, which undoubtedly others discovered before me, but it's sometimes helpful to provide notifications based on changes in the database. An audit log can provide the history for these types of triggers.

The application I'm currently working on, a tracking application for the Dance Marathon [not my site] student group at the Unversity of Iowa, has some of these auditing needs. Since I'm using LINQ to SQL as my ORM, I chose to implement my auditing code-side in submit changes. To this end, I created an AuditableDataContextBase class that derives from DataContext and will be the base class for my LINQ to SQL data context. The context has an CurrentUser property that holds the identity of the current user. This property is set in the factory method that creates my data context. AuditUser is actually pretty simple:

public class AuditUser
{
    public int ID { get; set; }
    public string Name { get; set; }

    public AuditUser()
    {
        ID = 0;
        Name = "system";
    }

    public AuditUser( Participant participant )
    {
        if (participant == null)
        {
            throw new ArgumentNullException( "participant" );
        }
        this.ID = participant.ParticipantID;
        this.Name = participant.DisplayName;
    }
}

The Participant class is my user entity class for the application.


The factory method that creates the data context is also pretty simple, although, it actually returns a wrapper around the data context. I described a little bit about the wrapper in my previous post. Because the "current user" depends on the context of the request and must make a call into the database to get a user entity to use in constructing the AuditUser object, intject a copy of the wrapper into the a utility method use to extract the user's identity from the web context and retrieve the appropriate entity from the database. Here's the interface and base class that does most of the work.

public interface ICurrentUserUtility
{
    AuditUser GetAuditUser();
    AuditUser GetAuditUser( IAuditableDataContextWrapper dataContext );
}

public abstract class CurrentUserUtilityBase : ICurrentUserUtility
{
    private HttpContextBase WebContext;

    protected CurrentUserUtilityBase( HttpContextBase httpContext )
    {
        this.WebContext = httpContext ?? (HttpContext.Current != null ? new HttpContextWrapper( HttpContext.Current ) : null);
    }

    public abstract AuditUser GetAuditUser();

    public AuditUser GetAuditUser( IAuditableDataContextWrapper dataContext )
    {
        if (this.WebContext != null
            && this.WebContext.User != null
            && this.WebContext.User.Identity != null
            && this.WebContext.User.Identity != WindowsIdentity.GetAnonymous())
        {
            var participant = dataContext.Table<Participant>()
                                         .Where( p => p.UserName == this.WebContext.User.Identity.Name )
                                         .Select( p => new AuditUser { ID = p.ParticipantID, Name = p.DisplayName } )
                                         .SingleOrDefault();
            if (participant != null)
            {
                return participant;
            }
        }
        return new AuditUser();
    }
}

Each implementing class is associated with a particular data context type and thus I can have different utility classes for each data context. Note that because I'm implementing an interface I need not take advantage of the base class implementation and could have a utility that derived the user's identity from something other than the web context. This will be important later on when I have Windows services that perform updates on an automated basis so that I can inject a well-known id for auditing purposes. Here's the implementation for the data context that holds my user data.

public class CurrentUserUtility : CurrentUserUtilityBase
{

    public CurrentUserUtility()
        : this( null )
    {
    }

    public CurrentUserUtility( HttpContextBase httpContext )
        : base( httpContext )
    {
    }

    public override AuditUser GetAuditUser()
    {
        IDataContextFactory factory = new MasterEventDataContextFactory();
        using (IAuditableDataContextWrapper wrapper = factory.GetDataContextWrapper())
        {
            return GetAuditUser( wrapper );
        }
    }
}

The wrapper class encapsulates the actual data context and simply delegates actions to it (the wrapper exists to make the data context testable, so it's not very complicated). The interesting bit is in the base data context. The SubmitChanges method constructs an AuditUtility that does the actual auditing and uses the ChangeSet to know what it needs to audit. I want to audit both failure and success, so I catch any exceptions throw by the base SubmitChanges method and the presence or absence of the exception to determine whether the operation was successful. Once the changes have been made, methods on the AudityUtility are used to log the various types of changes from the ChangeSet.

public override void SubmitChanges( System.Data.Linq.ConflictMode failureMode )
{
    using (AuditUtility auditor = new AuditUtility( this.CurrentUser ))
    {
        ChangeSet changes = this.GetChangeSet();

        bool success = false;
        Exception caughtException = null;
        try
        {
            base.SubmitChanges( failureMode );
            success = true;
        }
        catch (Exception e)
        {
            caughtException = e;
        }

        foreach (object deleted in changes.Deletes)
        {
            auditor.AuditEntity( deleted, ChangeAction.Delete, success );
        }
        foreach (object inserted in changes.Inserts)
        {
            auditor.AuditEntity( inserted, ChangeAction.Insert, success );
        }
        foreach (object updated in changes.Updates)
        {
            auditor.AuditEntity( updated, ChangeAction.Update, success );
        }

        if (caughtException != null)
        {
            throw caughtException;
        }
    }
}

The AuditUtility


Finally, we come to the class that actually creates the audit records, the AuditUtility. The AuditUtility works by using an AuditContextAttribute that decorates classes that need to be audited. It assumes that for each class so decorated, there is an [Audit.] table in the data context containing the audit entities. This audit class has the schema of the decorated class with the exception that the "id" parameter of the decorated class is not an auto-generated column and it has additional AuditID (primary key, identity column), ModifiedByID (int), ModifiedByName (varchar), ModifiedAt (datetime), Modification (varchar), and Success (bit) columns.

The AuditContextAttribute specifies both that the class is able to be audited and specifies the type of the audit entity to use. It gets applied to a partial class implementation for the entities that need to be audited.

[AuditContext( AuditType = typeof( Audit_Event ) )]
public partial class Event
{
   ...
}

internal class AuditContextAttribute : Attribute
{
    public Type AuditType { get; set; }

    private string tableProperty;
    public string TableProperty
    {
        get
        {
            if (string.IsNullOrEmpty( this.tableProperty ))
            {
                this.tableProperty = this.AuditType.Name;
            }
            return this.tableProperty;
        }
        set { this.tableProperty = value; }
    }
}

The AuditUtility class has a couple of utility methods. GetAuditContext is used to extract the AuditContextAttribute from an entity, if it exists. CopyColumns is used to copy the common columns, as indicated by the ColumnAttribute on the decorated entity class, from the decorated entity to the audit entity. The latter uses reflection over the public properties of the two classes. Note that we skip any timestamp columns. The timestamp column on the audit entity reflects its version, not the version of the decorated entity.

private AuditContextAttribute GetAuditContext( object entity )
{
    return entity.GetType().GetCustomAttributes( typeof( AuditContextAttribute ), false )
                           .Cast<AuditContextAttribute>()
                           .SingleOrDefault();
}

private void CopyColumns( object from, object to )
{
    if (from == null)
    {
        throw new ArgumentNullException( "from" );
    }
    if (to == null)
    {
        throw new ArgumentNullException( "to" );
    }

    var fromType = from.GetType();
    var toType = to.GetType();

    foreach (var fromProperty in fromType.GetProperties())
    {
        var attribute = fromProperty.GetCustomAttributes( typeof( ColumnAttribute ), false )
                                    .Cast<ColumnAttribute>()
                                    .FirstOrDefault();
        if (attribute != null && !attribute.IsVersion)
        {
            var toProperty = toType.GetProperty( fromProperty.Name );
            toProperty.SetValue( to, fromProperty.GetValue( from, null ), null );
        }
    }
}

The AuditUtility class has two constructors. The first is used by the actual code, the second by my unit tests. The second allows me to inject a fake data context which is useful for testing. Notice that the AuditUtility implements IDisposable, however, when the data context is passed in, we don't need or want to dispose of the injected context. My IDisposable implementation checks the NeedDispose property before it attempts to dispose of the AuditDataContext (the context containing the audit entities). When used normally, this context will be created by the utility and disposed when the Dispose method is called. Also notice that we always inject the current user, an AuditUser object. This object is used to set the ModifiedByID and ModifiedByName columns in the audit entity.

public AuditUtility( AuditUser currentUser )
    : this( null, currentUser )
{
}

public AuditUtility( IDataContextWrapper auditDataContext, AuditUser currentUser )
{
    this.CurrentUser = currentUser ?? new AuditUser();
    if (auditDataContext == null)
    {
        this.AuditDataContext = new DataContextWrapper<MasterEventAuditingDataContext>();
        this.NeedDispose = true;
    }
    else
    {
        this.AuditDataContext = auditDataContext;
    }
}

Lastly, we have the method that pulls everything together, AuditEntity. This method takes the entity to audit, the action that was attempted, and the status of the action. It creates an appropriate audit entity for the entity being audited and populates its values based on the entity parameter. Each audit entity is required to implement IAuditEntity. Basically, IAuditEntity defines a method that is used to set the auditing properties on the entity. It would be nice to be able to provide this in a base class, unfortunately the properties that you need to modify belong to each LINQ-to-SQL designer generated class so they can't be put in a base class. The easiest thing to do is to violate DRY and repeat the code in each audit entity.

#region IAuditEntity Members

public void SetAuditProperties( int participantID, string participantName, ChangeAction action, bool success )
{
    this.ModifiedAt = DateTime.Now;
    this.ModifiedByID = participantID;
    this.ModifiedByName = participantName;
    this.Modification = Enum.Format( typeof( ChangeAction ), action, "g" );
    this.Success = success;
}

#endregion

The method defined by IAuditEntity is used in conjuntion with the private helper methods to make the audit entity and store it using the AuditDataContext.

public void AuditEntity( object entity, ChangeAction action, bool success )
{

    if (entity == null)
    {
        throw new ArgumentNullException( "entity" );
    }

    if (action != ChangeAction.None) // only audit inserts, deletes, and updates
    {
        AuditContextAttribute auditContext = GetAuditContext( entity );
        if (auditContext != null)
        {
            var auditTable = this.AuditDataContext.Table( auditContext.AuditType );
            if (auditTable != null)
            {
                try
                {
                    IAuditEntity auditEntity = Activator.CreateInstance( auditContext.AuditType ) as IAuditEntity;
                    if (auditEntity != null)
                    {
                        CopyColumns( entity, auditEntity );
                        auditEntity.SetAuditProperties( this.CurrentUser.ID, this.CurrentUser.Name, action, success );
                        auditTable.InsertOnSubmit( auditEntity );
                        this.AuditDataContext.SubmitChanges();
                    }
                }
                catch { }
            }
        }
    }
}

Alternative IAuditEntity (Updated)


As an alternative you might want to define the audit properties (ModifiedAt, ...) on the IAuditEntity interface and define the SetAuditProperties() method as an extension on IAuditEntity. This way you can define the method just once -- as long as you want it to work the same way for all audited entities. All of your additional audit properties will need to be the same for all audit entities. In practice I have found this to be the case, however, and I now define set up my auditing this way.

public interface IAuditEntity
{
    int ModifiedByID { get; set; }
    string ModifiedByName { get; set; }
    string Modification { get; set; }
    bool Success { get; set; }
}

public static class AuditEntityExtensions
{
    public static void SetAuditProperties( this IAuditEntity source, int modifiedByID, string modifiedByName, ChangeAction action, bool success )
    {
        source.ModifiedByID = modifiedByID;
        source.ModifiedByName = modifiedByName;
        source.Modification = Enum.Format( typeof( ChangeAction ), action, "g" );
        source.Success = success;
    }
}

Some Final Notes


In order to make sure that the audit records stay intact, as a final measure, I add triggers to each of the audit tables that run on UPDATE and DELETE. These triggers simply rollback the transaction. This prevents my application and any users from removing or changing the audit records accidentally. For my integration tests, I do disable the triggers so that the test data can be removed from my test database instance.


I'd be interested in hearing your solutions to the same or similar problems. Eventually, I may need to add select/read auditing to the application as well. Unfortunately, I haven't been able to think a way to do this except by implementing the OnLoad partial method in each of my entity classes. To do insert/update/delete auditing the only change to my entities is to decorate them with the AuditContextAttribute. Doing select/read auditing will require more intrusive methods I'm afraid.