Logging changes using LINQ to SQL

In开发者_JAVA百科 a system I am creating, the customer requires all changes to data to be logged to a database table - with only changes being logged (i.e. if they only change 1 value on a form full of 10 fields, it'll only audit that one change). So essentially I need to be able to compare the old values to the new values.

This is of course easy - I've done this before, but it was rather messy - I would manually check each field in the database and compare it to the new one to decide if it should be audited. Is there any easier way, of perhaps automatically getting the changed column name/values? Or do I have to do it my 'messy' way?

Pretty much all of the changes to be logged are done using LINQ to SQL - so perhaps there's a way to compare the database table to the modified one, before calling submit changes?

---

I used the article on Code Project titled Automatic Event Logging in LINQ. This worked very well for us, it also does what you requested, as far as only logging the changes. If a record is inserted each update appears as a seperate row in the database tracking the PK, field name, old value, and new value. Of course it also tracks who performed the action and when. It tracks all actions: Create, Read, Update and Delete.

This method also satisfies your request that it dynamically include all of your tables, no seperate method for each table.

---

I have used http://doddleaudit.codeplex.com/ with great success.

It is easy to setup and adds 'automatic' logging to your data context.

---

Is doing it at DB level an option? This might be helpful in that case.

---

See CDC (change data capture) in Sql Server, might help. IIRC, it was introduced in MSSQL2008. But this is at the database level! So might not qualify as an answer! Hope it helps.