.NET Entity Framework 4 - How to run custom SQL when a certain table is updated

Question

When Table1.Foo changes, I want to update a column on Table2. What would be easiest is if I could just "run" some custom SQL after the update to Table1 is committed, however there doesn't seem to be a place to do this.

It seems the "recommended" solution is to override the SaveChanges() method of EntitiesContext. However, I have several dozen models and hundreds of columns - it seems rather hacky and inefficient to execute code every time any model anywhere is changed, and then say "If it happens to be this property of this model, then do this.."

My first approach was to use a database trigger, which would be great. Unfortunately, Oracle doesn't appear to support this type of updating I need to do in a trigger.

Any other ideas for this sort of thing?

score 1 · Answer 1 · answered Sep 22 '11 at 16:34

1

Are you sure you can't just add a trigger that calls a stored procedure like this:

http://searchoracle.techtarget.com/answer/Calling-a-stored-procedure-from-a-trigger

It'll let you put in the functionality you want without disturbing your dozens of models/programs.

answered Sep 22 '11 at 16:34

E.J. Brennan

45,870
7
88
116

Man I wish, that would be an ideal solution. However, since I need to query other values from the same table and those values are in a state of flux, it isn't supported. See Justin Cave's response to my question here: http://stackoverflow.com/questions/7507636/oracle-problem-creating-trigger-that-updates-another-table – Mike Christensen Sep 22 '11 at 16:39

score 0 · Answer 2 · answered Sep 22 '11 at 16:14

0

you can create a stored procedure and call it in a very easy way... I think

answered Sep 22 '11 at 16:14

Daniel Kienböck

105
4

The question is, where do I call this stored procedure? – Mike Christensen Sep 22 '11 at 16:25
ctx.Database.ExecuteSqlCommand() http://stackoverflow.com/questions/5474264/how-to-pass-parameters-to-the-dbcontext-database-executesqlcommand-method – jrummell Sep 22 '11 at 16:37
Yes, I know /how/ to execute a stored proc from .NET - The question is WHERE do you put that logic? After anything in the context changes? That seems hacky. – Mike Christensen Sep 22 '11 at 16:45

score 0 · Answer 3 · answered Sep 22 '11 at 17:06

0

use the POCO feature in EF 4.0.

check this link: http://www.devart.com/blogs/dotconnect/index.php/entity-framework-tips-and-tricks-part-2.html

specially this section 'ObjectContext.ExecuteStoreQuery'

answered Sep 22 '11 at 17:06

Boomer

1,468
1
15
19

score 0 · Accepted Answer · answered Sep 25 '11 at 05:32

0

You can create a partial class for the given entity in your project, if you did not create one.

In your partial class, you override PropertyChangedEvent handler to update other table. I don't think you have to create a stored procedure for that.

answered Sep 25 '11 at 05:32

Changgyu Oh

16
1

I'll look into this method. So if I change a property in another table, the Entity framework will just automatically track this change and update everything when the transaction commits? – Mike Christensen Sep 28 '11 at 15:38

.NET Entity Framework 4 - How to run custom SQL when a certain table is updated

4 Answers4