What is the best way to transparently log changes to objects when using LINQ-to-SQL?

Question

I keep track of all changes that are made to objects so that the user can view and rollback to any previous version of any item in the database.

The history database table looks like this:

Item     | ItemId | Field     | WhenChanged         | OldValue | NewValue
customer | 6      | LastName  | 2009-12-31 13:00:04 | Sanders  | Sanders-Smith
customer | 5      | FirstName | 2009-12-31 12:11:14 | Jym      | Jim

Currently I record these changes whenever the user fills out a form so I have complete information of the old and new state of the object.

However, now I need to make this historical data logging available from code. It needs to work transparently when LINQ-to-SQL is used, i.e. the developer should not have to do any extra work, i.e. the following code should cause a write to the history table as well:

using (var db = Datasource.GetContext())
{
    var customers = from c in db.Customers
     where c.Status == "waiting"
     select c;
    foreach(var customer in customers)
    {
        customer.Status = "finished";
    }
}
db.SubmitChanges();

I can imagine I can accomplish this in two ways:

1. override db.SubmitChanges() but then the question is how do I get access to the objects which are awaiting changes.
2. attach my logging method to an OnSubmitChanges event but I haven't been able to find a solution to this yet

Has anyone ever worked on this problem or know of a good approach to solve it?

Answer 1

I've done some similar work on auditing changes -- which is the first step. My solution works by making my designer context abstract then deriving the real context from it and overriding SubmitChanges. It works in conjunction with a separate audit context and a helper class that can take an object and construct the audit object from it. It relies on attributes to give the necessary information on which class is the audit class for a particular object.

You can find more information on my blog at http://farm-fresh-code.blogspot.com. It's too involved to repeat here.

Answer 2

Since L2S generates code for you, and you wish to alter the behaviour of the generated code I see two possibilities:

1. You start using T4 templates to generate the code from the dbml file and alter the template to add your functionality. This method is described here
2. You add the functionality to the generated (partial) datacontext class by writing a partial class yourself. In your partial class you can implement the create/update/delete methods (CreateCustomer, UpdateCustomer and DeleteCustomer) adding the functionality to store the change.

I hope these pointers can help you on your way.

Answer 3

For your fist question, you can get access to the objects awaiting changes with db.GetChangeSet() and to know which fields changed and what was the original value, you can use:

ITable table = db.GetTable(entity.GetType());
ModifiedMemberInfo[] modifiedMembers = table.GetModifiedMembers(entity);
object original = table.GetOriginalEntityState(entity);