CODEWITHSUNDEEP
c#.netasp.netlinqlinq-to-sql
Ronnie Overby
Ronnie Overby

Reputation: 46470

Linq To Sql - Update a value when another value changes

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?

Upvotes: 2

Views: 1147

Answers (3)

tvanfosson
tvanfosson

Reputation: 532515

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.

Upvotes: 2

Stephen Wrighton
Stephen Wrighton

Reputation: 37839

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.

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063013

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.

Upvotes: 4

Related Questions