on 2012 Apr 26 4:35 PM
I am using SQLAnywhere 11.0.1 with Code First Entity Framework 4.1. I would like to put two SaveChanges() statements in a single transaction, both using the same connection to a single database. By default, this requires DTC to get involved, since each SaveChanges() opens and closes the connection.
Involving DTC here causes a lot of unnecessary runtime overhead, and will also cause a lot of unnecessary server configuration when my application is deployed, for no benefit whatsoever. I've seen examples for other databases that use a TransactionScope, and explicitly open the connection. This supposedly causes the connection to stay open until it is explicitly closed, which prevents the escalation to DTC.
I have not suceeded in getting this technique to work with SQL Anywhere. No matter what I've tried, SaveChanges() results in the connection being closed. Is this possible to do with SQLAnywhere? If so, can someone provide a code example? Here is what I have tried:
this.context.Database.Connection.Open(); using (TransactionScope trans = new TransactionScope(TransactionScopeOption.Required)) { this.context.SaveChanges(); // Connection is closed after this line // Other code using autoincrement values generated from the previous save this.context.SaveChanges(); // Causes "Underlying provider failed on open; // Unable to enlist transaction, DTC may be down" trans.Complete(); }
Is there a way to get DTC out of the picture here?
Thanks, Eric
Request clarification before answering.
Figured it out. The above code is using a DbContext object, which tries to make things simple and easy. The underlying ObjectContext is able to do what's needed. The following code works (where this.context is the DbContext instance):
ObjectContext oCtx = ((IObjectContextAdapter) this.context).ObjectContext; oCtx.Connection.Open(); DbTransaction trans = oCtx.Connection.BeginTransaction (System.Data.IsolationLevel.ReadUncommitted); this.context.SaveChanges(); // other updates this.context.SaveChanges(); trans.Commit(); oCtx.Connection.Close();
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
As Calvin has supposed in your other question, please change your solution posting to an answer and mark it as accepted.
FWIW, I'm not EF-savvy, but when you use a DbTransaction object, would it be possible to convert this to an SATransaction object (which is derived from DbTransaction) - as I had initially suggested? - Note, I'm not aware if this would be better...I just don't know:)
User | Count |
---|---|
64 | |
8 | |
7 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.