Linq To Sql - Update a value when another value changes

Question

With Linq To Sql - what is the best way to update the value of one table in a database when a value changes in a different table?

For example in TableA, there is a column called DateModified. TableA has an association to TableB. Now I want to set the value of the DateModified field to the current date every time the record changes in tableA AND everytime a child record is created/updated/deleted in TableB.

How can this be achieved?

Answer 1

If the change was just in the one record, you can use either the On*Changed partial methods, or you can override SubmitChanges on the data-context, call GetChangeSet, and apply the changes just before they are updated. The latter (data-context) approach is useful for catching a broad spectrum of changes, where-as the On*Changed approach is useful for logic specific to individual properties.

However, in the case presented (non-homogeneous updates), I expect a database trigger may be more appropriate.

Answer 2

Either do Insert/Update/Deletes on TableB via a StoredProcedure which allows you to perform the update on TableA or have Insert/Update/Delete triggers on TableB which updates TableA

I wouldn't try to accomplish this in LINQ as it will involve either overriding various IDE-generated code structures (maintenance nightmare) or extra calls to the Database to get the related TableA item.

Answer 3

You could register a PropertyChanged event handler on the child entity in OnCreated (implemented in a partial class) and have that handler update the parent property. If you don't care about the already loaded classes getting the change, using a DB trigger as @Marc suggests would also be an option.

EDIT: If you need to catch deletes as well, you can implement the delete partial method for the data context to call the same handler method to update the parent of the entity being deleted.