EF 4.1 Transactions without DTC

[Former Member]

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

[VolkerBarth]

Is the following doc topic on "Transaction processing" of help?

Note that it does use the particular SATransaction class so I do not know if this meets your needs.

[Former Member]

Thanks, but no. I'm using Entity Framework, so I don't have direct access to the Command objects, or the SQL that is being run. I can create an SATransaciton object, but have nothing to pass it to. SaveChanges() handles all of this under the hood. What's needed is some way to signal the SQL Anywhere Entity Framework Provider to relinquish its management of the connection.

[Former Member · Accepted solution]

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();

[VolkerBarth · Accepted solution]

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:)