In a project I am currently working on, we had a fairly common requirement of recording an Audit Trail of any data changes. The requirements were typical, provide a running log of any changes in the database, including:

• What table was modified?
• What fields changed?
• Who made the change?
• When did it occur?

Naturally, there are many ways to tackle this problem. In the past I have either relied on writing database Triggers for the tables, or wrote the Auditing logic right into the stored procedure that was doing the modification. But this time, since the project is utilizing LINQ to SQL, I had a more ambitious idea in mind.

The Goal

Create a flexible, and automated Auditing solution that would allow my team to add Audit tracking to any table in our database, with only a single line of C# code.

The code I ended up with did in fact meet my goal -- which would add Auditing to the Products table and Categories table -- can be seen below.
As long as you call the Audit<> method before calling SubmitChanges() then any INSERTED/UPDATED/DELETED entities will be properly Audited automatically.
The first parameter in the method, is telling the Auditing code how to identity which property is the Primary Key of the table.

public partial class NorthwindDataContext
{
    public override void SubmitChanges(ConflictMode failureMode)
    {
        this.Audit<Product>(p => p.ProductID, "Product modified ");
        this.Audit<Category>(c => c.CategoryID, "Category modified"); 

        base.SubmitChanges(failureMode);
    }
}

TIP: If you are interested in learning how this code was created, then please feel free to read the rest of this article. If however, you are only interested in the finished product and using the code in your projects, then head down to the bottom :)

Step 1 - Database Tables

Given my requirements I derived the following database schema. For my purposes, these tables did the trick, but of course your requirements may differ slightly so you may tweak as you wish.

• The Audits table is used to track any change. Most of the columns should be self-explanatory. The Action is what CUD event occurred (Insert, Update, or Delete). The rest of the columns record who made the change, when, what table, and the Primary Key of the modified record.
• The AuditValues table is used to track each modified column in the table being audited: the Old (Original), and the New (Current) value.

Step 2 - The DBML

Now that we have our new tables, we will update our LINQ to SQL Entity classes. I am going to be using Northwind for this example.

Step 3 - The Public Audit Extension Method

First we need to create a public method that client code will use to begin Auditing a table.

/// <summary>
/// This method will enlist a LINQ to SQL Entity for automatic Auditing
/// </summary>
/// <typeparam name="TEntity">The Entity class you want to audit changes on</typeparam>
/// <param name="tableKeySelector">A Lambda expression that will return the Primary Key of the Entity</param>
/// <param name="title">The text that will be logged in the audit table</param>
public static void Audit<TEntity>(this DataContext db, Func<TEntity, int> tableKeySelector, string title) where TEntity : class

This is our public entry point for Auditing a table. This method might look a little bit intimidating to those unfamiliar with generic classes and generic delegates. Let's dissect this method to make it a little more palatable.

• The first thing you should be aware of, is that this is an Extenstion method to the DataContext class. This is clear when you notice the "this" keyword in the first parameter. That means that this method will be available on any DataContext in our project (assuming of course, that you import the namespace the extension class resides in).
• The second thing that might look strange is the Func<T1, T2> generic parameter. This is actually a Generic Delegate that exists in the System.Core assembly that ships with 3.5.  The 5 Func<T1, T2, T3, T4, TResult> overloads are used to designate a method that operates on 1 or more inputs (T1 through T4) and returns a TResult. Func's can be seen all over the LINQ namespace and you will get probably pretty familiar with them once you being extending and using LINQ. Typically, they will define the signature for a Lambda expression (which is a new C# shorthand for defining an expression or anonymous method call). In this particular Func<> parameter, I am expecting a method (a Lambda expression, typically), that accepts a TEntity for input, and returns an int. This is required because we need to know how to find the Primary Key property of an Entity.
• The third unique syntax in this method is the "where" keyword. This is telling the generic method that any TEntity that is passed to this method will be a reference type. This is required because LINQ to SQL can only map reference types.

Step 4 - Auditing LINQ to SQL Entity Inheritance

I ran into something unexpected when I began testing the auditing code I wrote: Auditing my Entities that used inheritance. In case you didn't know, LINQ to SQL supports a type of relational inheritance known as Table-Per-Class Hierarchy. This means that every custom field for the derived entity classes are stored in the same table in the database. This article will not go into this subject any further, but please see Guy Burnstein's Inheritance Article on the subject of LINQ to SQL inheritance. The following images are courtesy of him, and only for demonstration's sake -- these entities will not be in my sample code.

Naturally, I assumed that I could use the following code to Audit entities that supported inheritance just like regular Entities:

this.Audit<SalesPerson>(sp => sp.PersonID, "SalesPerson changed");
this.Audit<Contact>(c => c.PersonID, "Contact changed");

Unfortunately for me, I was wrong. The Auditing code that I wrote relies on dataContext.GetTable<TEntity> to obtain a reference to the table being audited -- however, when I tried doing dataContext.GetTable<SalesPerson> I received an unpleasant exception stating that a derived entity cannot be used with the GetTable method -- instead, the exception actually suggested that I try dataContext.GetTable<Person>(), which did indeed work.

So due to this unexpected exception, I had to add an overload to the Audit method that supported two types, a TBaseEntity, and a TSubEntity.

this.Audit<Contact, SalesPerson>(sp => sp.PersonID, "SalesPerson changed");

The method signature for this method is listed below. You will notice an additional Generic Constraint added, defining that TSubEntity must in fact derive from TBaseEntity.

/// <summary>
/// This method will enlist a LINQ to SQL Entity for automatic Auditing

/// </summary>
/// <typeparam name="TBaseEntity">The Base Entity class you want to audit changes on</typeparam>
/// <typeparam name="TSubEntity">The Derived Entity class, for use in LINQ to SQL inheritance</typeparam>
/// <param name="tableKeySelector">A Lambda expression that will return the Primary Key of the Entity</param>
/// <param name="title">The text that will be logged in the audit table</param>
public static void Audit<TBaseEntity, TSubEntity>(this DataContext db, Func<TSubEntity, int> tableKeySelector, string title)
       where TBaseEntity : class
       where TSubEntity : TBaseEntity

Step 5 - Auditing Inserted Records

The heart and soul of my auditing code relies on the DataContext's Object Tracking Service. You see, the LINQ to SQL DataContext is of course responsible for tracking entity changes. This is a necessary service so that it is able to queue up every insert, update, and delete -- patiently waiting for a call to dataContext.SubmitChanges(), at which point the context runs through every necessary SQL command to INSERT, UPDATE, and DELETE records in the database.

Thankfully, the DataContext exposes a GetChangeSet() method, which will allow us to peek into its pending database calls.

private static void AuditInserts<TEntity, TSubEntity>(DataContext db, Func<TSubEntity, int> tableKeySelector, string title)
            where TEntity : class
            where TSubEntity : TEntity
        {
            var inserts = db.GetChangeSet().Inserts.OfType<TSubEntity>();

Here we can see a perfect example of LINQ to Objects. The GetChangeSet().Inserts property return a List<Object>, but that doesn't do us much good, because we are only interested in the Entities we want to audit. Therefore, we can use the new OfType<> extension method to only pull back the entities we are concerned with.

Next, we need to obtain a reference to the Table<> that our Entity belongs to.

Table<TEntity> table = db.GetTable<TEntity>();

Then we need to get all the public properties that our Entity has.

PropertyInfo[] props = typeof(TSubEntity).GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.FlattenHierarchy);

I have also created a quick helper-method that will create the row in the Audit table for us

private static Audit CreateAudit<TEntity>(string title, Table<TEntity> table, int key) where TEntity : class
        {
            Audit audit = new Audit();
            audit.TableName = table.ToString();
            audit.TableKey = key;
            audit.UserName = CurrentUser;
            audit.AuditDate = DateTime.Now;
            return audit;
        }

The rest of the code is simply looping through all of the Entities in the ChangeSet.

foreach (TSubEntity item in inserts)
{
    // Get the Primary Key for our table by Invoking the tableKeySelector delegate on the current TSubEntity item
    int key = tableKeySelector.Invoke(item);

    // Create the Audit
    Audit audit = CreateAudit<TEntity>(title, table, key);
    audit.Title = title + " added";
    audit.Action = "Insert";

    // Loop through every property in our inserted entity
    foreach (PropertyInfo pi in props)
    {
        // This code checks to see if the property is a LINQ to SQL column. You may change this if you need.
        if (pi.HasAttribute(typeof(ColumnAttribute)))
        {
            // I chose to ignore any Id columns in the auditing, again, you may change this
            if (pi.Name.EndsWith("Id"))
                continue;

            // Creat the AuditValue row and add it to our current Audit
            AuditValue values = new AuditValue();
            values.MemberName = pi.Name;
            values.NewValue = GetPropertyValue(pi, item);

            audit.AuditValues.Add(values);
        }
    }

    // Insert the Audit to the database
    db.InsertAudit(audit);
}

Step 6 - Audit Deletes

Very few things change from the Insert code here.

var deletes = db.GetChangeSet().Deletes.OfType<TSubEntity>();

I also set the OldValue property instead of the NewValue property when creating the AuditValues entity.

AuditValue values = new AuditValue();
values.MemberName = pi.Name;
values.OldValue = GetPropertyValue(pi, item);

Step 7 - Audit Updates

Auditing updates is greatly simplified again thanks to our hard-working DataContext. Every Table<T> class has a GetModifiedMembers method that can be invoked on any entity. This method will only return properties which were CHANGED. This is exactly what I need because I did not want to record any values that stayed the same, especially for tables with a large number of columns.

ModifiedMemberInfo[] mmi = table.GetModifiedMembers(item);

foreach (TSubEntity item in updates)
{
    int key = tableKey.Invoke(item);

    Audit audit = CreateAudit<TEntity>(title, table, key);
    audit.Title = title + " updated";
    audit.Action = "Update";

    ModifiedMemberInfo[] mmi = table.GetModifiedMembers(item);

    foreach (ModifiedMemberInfo mi in mmi)
    {
        AuditValue values = new AuditValue();
        values.MemberName = mi.Member.Name;

        values.OldValue = GetPropertyValue(mi.OriginalValue);
        values.NewValue = GetPropertyValue(mi.CurrentValue);

        audit.AuditValues.Add(values);
    }

    db.InsertAudit(audit);
}

The Finished Results

I wrote a very quick demo app for this article. You can see below a GridView of the Audits table rows. We can see what we changed, when, by whom, etc. If you select one of the Audits, a DetailsView will display all of the Changed Values that took place during the Audit.

Get the Code!

If you stuck around for that whole article then I am impressed! If however, you just want to view, experiment with, and use the code, then I provide it here for your use. I have also included the sample Northwind Project so you can play with the demo yourself.

To download the zip of the solution, click here: Download the Code

Thanks for reading, and please let me know if you end up using this code or have any cool features or optimizations that you have added or would like to see added!

This post is a follow-up to an article I wrote a few weeks ago, ADO.NET Entity Framework Comparison Frustration. As a quick re-cap, I was simply trying to filter a list of users with a LINQ query expression by comparing custom classes, not primitive types.

TorvusEntities entities = new TorvusEntities();



 
// Pull my Account Entity from the database
Account matt = entities.Accounts.First(a => a.AccountId == 10);

// Attempt to get all Teams by a Team Owner
var teams = from t in entities.Teams
                 where t.Owner == matt
                 select t;

However, an exception was thrown.

Unable to create a constant value of type 'Closure type'. Only primitive types (for instance Int32, String and Guid) are supported in this context.

So I left it at that. The Entity Framework could only handle comparison on primitive types, which makes sense of course, since the expression needs to be converted into the underlying data storage language (T-SQL in this case). I was simply hoping that they would provide a more object-oriented way of translating the comparison I hoped to express by some other means. E.g., overriding Object.Equals() or the == operator, implementing a certain interface on my entities, something that felt OO to me.

Today however, I found my answer. Embarrassingly enough, it was in the MSDN docs all along.

A comparison expression checks whether a constant value, property value, or method result is equal, not equal, greater than, or less than another value. If a particular comparison is not valid for LINQ to Entities, an exception will be thrown. [Specifically, the exception I wrote above.] All comparisons, both implicit and explicit, require that all components are comparable in the data store. Comparison expressions are often used in Where clauses for restricting the query results.

And here it is, the confirmation I wanted (albeit not the answer I was hoping for):

LINQ to Entities does not support using a user class as a constant. However, a property reference on a user class is considered a constant, and will be converted to a command tree constant expression and executed on the data store.

Oh, another thing worth mentioning, method returns don't count as so-called constant expressions, and will thrown an exception when attempted.

public class MyBusinessObject
    {
        public int GetId()
        {
            return 5;
        }
    }

    public class Test
    {
        public Test()
        {
            using (TorvusEntities context = new TorvusEntities())
            {
                MyBusinessObject myBo = new MyBusinessObject();
                var accounts = from a in context.Accounts
                               where a.AccountId == myBo.GetId()
                               select a;

                // Exception will be thrown at run-time,
                // methods do not count as Constant expressions
            }
        }
    }

The ASP.NET MVC framework ships with a number of Visual Studio project and item templates to ease our development tasks. One of these templates is a UserControl built specifically for the MVC framework. We are going to walkthrough building a re-usable Header control that can be added to the top of related pages (in this specific case, this Control will be added to all Account pages). If you want to get technical, I suppose this may be a good candidate for the new Nested Master Page support in Visual Studio 2008. But for our purposes, this will do just fine.

Our end-result will be a control that looks like this, and can be added very easily to our View Pages to provide a consistent navigation element.

Create the Control

First let's add a new MVC View User Control control to our Views folder in the solution.

Once our control is added, we need to make a few very small changes to the CodeBehind file. The MVC framework provides us the flexibility to use a strongly-typed generic version of the ViewUserControl class, much like the ViewPage class. The difference between ViewUserControl and ViewUserControl<T> is that in the generic version you explicitly define the Type of ViewData you will be passing to your UserControl. If you choose to use the non-generic version the ViewData property will be a simple dictionary of objects, instead of strongly-typed properties.

Lets go ahead and tell our UserControl we will be passing it an instance of the AccountViewData class I created. I also added a simple automatic property called SelectedItem that we will use to specify which menu item should be "selected." We will use this property in a future post.

So here's the markup for our control. You'll notice that was can access the ViewData property the same way we access it from a Page instance.

But where does the ViewData come from?

Well, let me first say, I am not an expert on this subject yet. Here is what I know.

• It Just Works if you have a ViewUserControl<T> and a ViewPage<T> where T is the same type for both!
• For example, if you have ProfilePage<AccountViewData>, TeamPage<AccountViewData>, FriendsPage<AccountViewData>, your ViewUserControl<AccountViewData> will have its ViewData property automatically populated by the MVC framework! This might not always be ideal however, as it essentially couples your user control to the page(s), as well as gives the control access to ALL data that the page has.
• An MVC UserControl also exposes a public ViewDataKey property that the page can use to specify a subset of its ViewData that it should forward to the Control. I'll try and elaborate more on this later.
• The MVCToolkit currently has an overloaded Html.RenderUserControl() method that allows you to specify custom ViewData, as well as assign any properties to the control. This is the method I will be using for the Header control in this walkthrough.

I will possibly post more on this subject once I have more time and experiment with it.

Let's Add the Control to our Page

Our first page to use the control will be the Account Profile page. It will be using the generic version of ViewPage, and will be accepting an instance of type AccountProfileViewData, which is defined for your reference above. Something worth noting: I am experimenting with standard inheritance in my ViewData containers. The thought process is simple, AccountViewData will expose the properties that are common to all of my Account-related pages, where the individual Pages themselves will typically have a derived Account__Page__ViewData class to add any additional data. The careful observer may have noticed that my UserControl is accepting the parent type AccountViewData, whereas my Profile page is actually rendering using data from the derived AccountProfileViewData. This will come in handy later.

Method 1 - Drag the UserControl onto the designer surface

This is the method we typically used in WebForms development. Be alert though, Visual Studio will add a <form runat="server"> when you do this. This should be removed, the UserControl will still function.

One difference here, as mentioned a moment ago. An MVC UserControl allows us to specify a ViewDataKey. I won't be using this method right now, but I found a good article on the subject here.

Take a look at the Controller Action below. We are creating an instance of AccountProfileViewData, which derives from AccountViewData, and passing it to the RenderView method. This makes perfect sense, since our ProfilePage<AccountProfileViewData> expects this! But what about our Header<AccountViewData> control that is expecting AccountViewData? Simply enough, the MVC framework takes care of it for us! Because of standard IS-A OO relationship, our AccountProfileViewData object IS-A AccountViewData object. Our UserControl works just fine with this! Perfect, our User Control only has access to the data it needs, the Page has access to all the data it needs, and everyone is happy.

Method 2 - Html.RenderUserControl

This is simply another method for adding a UserControl to your page. That null parameter you see in there allows you to pass in any controlData that the control expects. So your page would be responsible for sending it the proper data the control requires. I used null this time because the MVC framework passed my data for me because of the inheritance hierarchy I used. (See above). Also note, you can assign any number of Properties that the UserControl expects using anonymous-type syntax.

I apologize this post was all over the place. If anyone reading this has any questions by all means let me know and I will elaborate further.

Tomorrow I will be writing a follow-up article, where we will convert the control to use a new Html.ActionMenu extension method I wrote. This will allow us a very elegant solution for creating "menu's" of ActionLinks, as well as very cleanly wire-up our SelectedItem property of our Header control.

Many of us have probably encountered the need for this on various projects. The scenario is simple, we have optional fields that a user may or may not enter values for, and we would like to hide them from display if they were not filled in.

The above screenshot could use some cleaning up. Ideally those fields that contain no data should be hidden from the rendered HTML. Our first attempt might look something like below, but hopefully, after 1 or 2 copy pastes any developer will realize this should be handled differently. Who among us has run into maintaining code looking like this?

Ok let's go back to the drawing board. The MVCToolkit comes with many extension methods for the HtmlHelper class that ships with the ASP.NET MVC framework. The Html object is accessible to every MVC Page, and will prove invaluable for generating inputs like  RadioButtonLists, DropDownLists, Submit buttons, etc. Let's see how it will look if we add a ConditionalRender extension to the HtmlHelper class. Our ideal code will look something like this:

That looks pretty good, but we need a mechanism to tell the ConditionalRender helper how to format the returned HTML. To do this, we can add a string variable to define the format we want returned and pass it to the helper, as shown below:

Excellent, as we can see below, the fields the user entered are displayed nicely for us, while the fields that were left empty are not rendered to our output HTML at all.

Here is the code the extension method. Just be sure to <add namespace="MattHidinger.Extensions"> to your web.config, so that the extension method will be imported to all of your Pages.

namespace MattHidinger.Extensions
{
    public static class HtmlExtensions
    {
        public static string ConditionalRender(this HtmlHelper helper, object input, string label, string format)
        {
            if (input == null)
                return string.Empty;

            return string.Format(format, label, input.ToString());
        }
    }
}

Today I began playing with the ADO.NET Entity Framework. This framework looks very promising and I plan on experimenting with it heavily this weekend, so expect some more ADOe articles over the next few weeks.

[Update: I discovered more about this subject while reading in-depth the MSDN articles on the ADO.NET Entity Framework. I have updated my conclusions at Entity Framework Comparison Frustration: Explained]

Without going into too much detail, ADOe is very similar to LINQ to SQL. It is essentially a very advanced OR/M that is built into the .NET framework and integrated heavily with Visual Studio designer support, complete with a few snap-in panes for visually mapping your database schema to your CLR objects. I found this post on MSDN explaining the major differences between LINQ to SQL and the ADO.NET Entity Framework.

Later I will go into much more detail about ADOe in general, but for now I wanted to bring up a problem I ran into tonight. My database schema is simple, I have a table called Teams, and a table called Accounts. Each Team has an OwnerId which relates to the AccountId of the Account table -- every Team must have an owner. Lets try and get all Teams for a specific Account...

TorvusEntities entities = new TorvusEntities();

// Pull my Account Entity from the database
Account matt = entities.Accounts.First(a => a.AccountId == 10);

// Attempt to get all Teams by a Team Owner
var teams = from t in entities.Teams
            where t.Owner == matt
            select t;

Looks pretty simple right? The driving force behind ADOe is of course to abstract away data access, so that any .NET developer can read and understand the query intention even if he had never seen T-SQL before in his life. Well, apparently my query isn't acceptable. I am getting an exception:

Unable to create a constant value of type 'Closure type'. Only primitive types (for instance Int32, String and Guid) are supported in this context.

Hmm, ok that's unfortunate. Google is turning up nothing interesting. Apparently the Entity framework can only query primitive types. So naturally I tried to compare the AccountId property instead of a direct instance-to-instance comparison -- something that, IMHO, looks more like T-SQL than C#.

// This works, by comparing the AccountId (int) column
var teams = from t in entities.Teams
             where t.Owner.AccountId == matt.AccountId
             select t;

So I started thinking, maybe ADOe just needs to know how to compare two instances, let's override some operators

public partial class Account
 {
     public static bool operator ==(Account a, Account b)
     {
         return a.AccountId == b.AccountId;
     }
 }

Damn, same exception. Well I'm going to admit that this could very possibly be an error on my part, after all it's 2 AM and I only installed ADOe Beta 3 a few hours ago... I'll update if I discover anything.