views:

155

answers:

2

Here's another one of these LinqToSQL questions where I'm sure I must have missed the boat somewhere, because the behavior of the O/R Designer is very puzzling to me...

I have a base class for my LinqToSQL tables, which I called LinqedTable. I've successfully used reflection to get hold of all the properties of the descendant classes and do other standard stuff.

Now I want to have some automatic auditing of my tables, so that whenever a LinqedTable record is inserted or deleted, or a field value changes, I will insert a record into an audit table, detailing the change type, the field name, and its value pre- and post-save.

I thought I would be able to do it using the PropertyChanging event, keeping track of all the changed properties before a save, then clearing the collection of changes after each SubmitChanges() call. But - the generated code from the O/R designer, for some bizarre reason, doesn't give you the property name in the PropertyChanging event - it sends an empty string! (WHY?!) It does send the property name in the PropertyChanged event, but that's already too late for me to get the original value.

I thought to grab all the original values of all properties using the OnLoaded() partial method - but that is private by definition, and I need access to that method in the base class. Even if I used reflection to get hold of that method, that would mean I would have to implement the other half of the partial method for every one of my tables, which kinda defeats the purpose of having inheritance!

I also can't find any suitable method in the DataContext to use or override.

So what would you recommend to get this audit functionality working?

+7  A: 

You can use GetChangeSet on the DataContext to retrieve a list of updates, inserts and deletes that have occurred on all tables within a context. You can use ITable.GetOriginalEntityState to retrieve the original values of a changed entity. However, when you retrieve the original values of a deleted or updated record, the associations will not be available so you will have to rely on foreign key values only in that area if you need to process related entities. You can Use ITable.GetModifiedMembers to help retrieve only values that have changed.

BlueMonkMN
Thanks, man - perfect answer!
Shaul
A: 

Forgive me for perhaps a stupid answer, but how about doing the audit directly in the SQL Server using triggers (if you are in SQL Server 2005 or 2008 standard) or using the change tracking facilities in SQL server 2008 Enterprise?

no_one
Just a philosophical thing: I don't like to put business logic in the data layer if I can help it. I guess it's arguable whether this is "business logic", but anyway I like code to be managed in source control, where I can see it.
Shaul
well neither do I, but in this case, I beg to differ. For me this is not really a business logic.And you can of course maintain the code in source control. We do it with SQL scripts all the time.
no_one
And this will work regardless of whether you are hitting the database from your application, or from somewhere else.
no_one
If you were using triggers in a database, how would you identify the user that made the change? I would suppose you can get the database login (assuming each user has a separate login to the DB) But, what about a web-based or n-tier application where there is only one database user? Would you add an updated_by field to each table and store that as one of the changed values?
Vic Boudolf