Change Tracking with a Linq to Sql DataContext


So we all probably know by now that a Linq DataContext object is used for both establishing and accessing SQL Server databases. 

You might be also aware that to take advantage of Linq to SQL’s functionality, changes must be performed within the scope of a DataContext.

This entry will focus on taking advantage of a DataContext to provide for you (at absolutely no additional cost) some neat auditing-like functionality.

When we create a context and select some records, the context in which the request is made subscribes to change notifications which all Linq 2 Sql entities implement, through the INotifyPropertyChanging and INotifyPropertyChanged interfaces.  This is why Linq 2 Sql doesn’t work so well in disconnected environments – it can’t tell what has changed in an entity.  We aren’t going to focus on that today (although I have a few  solutions) – I want to show you a nice little by-product of DataContext change tracking.

Suppose we have a simple select, update and save scenario, like below:

using(DataContext db = new DataContext())
{           
     Product existingProduct = (from p in db.Products
                                where p.ProductId == 1
                                select p).FirstOrDefault();

     if(existingProduct == null)
          return;

     existingProduct.Name = “Edited Product”;
     existingProduct.ModifiedDate = DateTime.Now;
     existingProduct.Available = false;

     EntityLogging<Product>.LogChanges(db, existingProduct);
 
     db.SubmitChanges();
}

There is a very handy way to get those three changes in a generic way.  Let’s implement some logging.  First things first though, have you generated all your entities with a common base class? 

You should consider doing so, and it’s pretty easy – you just have to use SqlMetal and specify a parameter, example (where EntityBase is a base class we’ve created):

"C:\Program Files\Microsoft SDKs\Windows\v6.0A\bin\SQLMetal.exe" /code:DataEntities.cs /entitybase:EntityBase

Once each entity has a common base class, you can implement:

public static class EntityLogging<T>
{
    /// <summary>
    /// Audits Changes to a Specific Entity
    /// </summary>
    /// <typeparam name="T">The Data Entity Type</typeparam>
    /// <param name="dataContext">The Data Context</param>
    /// <param name="modifiedEntity">The Entity To Audit</param>
    public static void LogChanges<T>(DataContext dataContext, T modifiedEntity) where T : EntityBase
    {
        if (dataContext == null || modifiedEntity == null)           
            return;           

        foreach(ModifiedMemberInfo modifiedProperty in dataContext.GetTable<T>().GetModifiedMembers(modifiedEntity))
        {
            //log changes-
            //modifiedProperty.CurrentValue
            //modifiedProperty.Member
            //modifiedProperty.OriginalValue
        }
    }
}

Be warned, this is probably an expensive exercise (CPU) however, for the odd object this is probably going to save you a lot of time.  The obligatory caveat is that this approach will obviously only pick up modifications tracked by the DataContext.  This is OK though, since a call to db.SubmitChanges will only attempt to change what it is tracking.  Makes sense?

Anyhow, just a little something I thought would be nice to share.  Leave comments per usual.

Rob


About Rob Sanders

IT Professional and TOGAF 9 certified architect with nearly two decades of industry experience, 18 years in commercial software development and 11 years in IT consulting. Check out the "About Rob" page for more information.

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>