EF: ObjectContext SaveChanges and MSDTC escalation

Question

Hello,

The application I'm developing requires a number of tigger actions to run whenever certain entities are saved with a state of EntityState.Modified. These trigger actions are stored procedures executed using an SqlConnection and SqlCommand. I need their execution to be enlisted in the same transaction the Framework uses during its operation, so if any of it (stored procs or subsequent Framework save) throws an exception, I need it all rolled back, or all committed.

 

To do this, I've 'overridden' the SaveChanges() method of ObjectContext and initiated my own System.Transactions.Transaction scope, run my trigger operations, called base.SaveChanges() and then checked the result of that before committing. Simplification:

public new int SaveChanges(bool acceptChangesDuringSave) { 
    int result; 
    using (TransactionScope transactionScope = new TransactionScope(TransactionScopeOption.Required)) { 
        this.OnSaveChangesTransactionStarted(); //private DB connection opened and stored procs executed here 
        result = base.SaveChanges(acceptChangesDuringSave); 
        this.OnSaveChangesTransactionCompleting(); 
        transactionScope.Complete(); 
    } 
    this.OnSaveChangesTransactionCompleted(); 
    return result; 
}

However this always seems to trigger the Distributed Transaction Coordinator to get involved on most occasions, which I didn't really want because of the overhead it creates, and also means any server the app is deployed to must have the Distributed Transaction Coordinator service enabled. Since there is only one resource involved here (SQL Server), I thought the framework wouldn't escalate/enlist the MSDTC.

 

I've read the guide on how and when .NET enlists the MSDTC and was wondering if the escalation is because of a second (internal) database connection the Entity Framework might be creating in addition to my own that would mandate MSDTC to get involved. Is that the case, and is there is any way I can get the Entity Framework to use my transaction and connection?

 

Cheers

 

Allister

Answer 1

Sorry my bad.

 

I should be able to use ObjectContext.Connection to access the same underlying connection the framework uses, rather than creating my own SqlConnection. I hadn't noticed this inherited property before. Hopefully if I do this I can avoid MSDTC escalation because only one connection will be used for the entire save operation.

 

Answer 2

There are a couple of things going on here.

 

First, yes, you can use the ObjectContext connection property to get to the EntityClient connection, and then you can use its StoreConnection property to get the underlying connection used to talk to the database.  That's the one you want to use to create your own commands and avoid MSDTC escalation.

 

The other thing to keep in mind is that if you make a TransactionScope outside of ObjectContext.SaveChanges because you want to do two top-level operations with the context inside a single transaction, then you run a risk of still getting escalation to the MSDTC (depending on your version of SQL-Server, newer versions are better here) because opening, closing and then re-opening a connection within a transaction can still cause escalation.  By default the context opens and closes connections automatically as needed.  There is a simple way to avoid this problem as well, though, which is that when you start a top-level TransactionScope you can also call context.Connection.Open() and force the connection open.  If you do that, then the connection will stay open until you explicitly close it (or you dispose of the context).  This will allow you to avoid escalation in that case as well.

 

- Danny

 

Answer 3

 

Does this mean that you can use the SaveChanges event to not just "enhance" saveChanges but to completely override it? It looks like calling mybase.savechanges explicitly in the context's SaveChanges event will prevent it from running after the custom code has been run because tehre won't be any more changes to save.

 

Is this correct?

 

I'm looking at what scenarios would actually cause escalation and this looks like a doozy. :-)

Answer 4

Well, the SavingChanges event runs as the very first thing when SaveChanges is called before any information is collected to determine what work to do, etc.  This means that it is possible to add to the list of things which will be saved (by modifying some other entity or whatever) or to take away from what will be saved by something like a call to AcceptAllChanges(), but...

 

The generated context doesn't actually override the SaveChanges method on the base class (ObjectContext), and in fact SaveChanges is not virtual.  So, if you were to call SaveChanges on your base class from the event handler you would probably enter an infinite loop since the event was called from the same SaveChanges method you would be calling...

 

- Danny

Answer 5

 

infinite loop is what I would have expected. Maybe I'm misunderstanding the code sample in the original question because it looks like he's in SavingChanges and calling SaveChanges again.

 

So is there anyway to completely override savechange - just replace it with your own code if for some reason you wanted to?

 

I suppose one way would be to just acceptallchanges inside of the save changes so that when the base.savechanges DOES get called, there will be nothing to do...

 

Answer 6

Hello Julie,

 

If you look at the signature of the method:

 

public new int SaveChanges(bool acceptChangesDuringSave) { ...}

 

This is equivalent to using "Shadows" in VB and what it does is hidding instead of overriding. That means that the derived ObjectContext has a new contract with a method that has the same name "SaveChanges", but it is no longer the same as the base class SaveChanges().

 

Code invoking SaveChanges on a variable of the base type will still see the original SaveChanges().

 

Hope this helps,

Diego

Answer 7

Sorry I didn't read the original message in the thread carefully.  Diego is right.  The confusion comes as follows: I was assuming this was just in the SavingChanges event handler in which case you can't really call base.SaveChanges, but what the original user was doing was overriding SavingChanges in their child class.  This will work as long as everyone who calls SaveChanges explicitly calls the derived context class rather than somehow calling SaveChanges on ObjectContext directly which would then skip their behavior since the method is not virtual.

 

There isn't really any way to completely override the behavior of SaveChanges on the context itself given that it's not virtual except maybe to call AcceptAllChanges in the SavingChanges event which would cause it to do nothing.

 

- Danny

Answer 8

Thanks Diego and Danny.

 

Yeah that little itty bitty "new" packs a punch in that example! :-)