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

asked 26 Apr '12, 15:35

EMurchieBeyma's gravatar image

EMurchieBeyma
1945816
accept rate: 75%

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.

(27 Apr '12, 05:35) Volker Barth

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.

(27 Apr '12, 09:27) EMurchieBeyma

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

answered 04 May '12, 15:58

EMurchieBeyma's gravatar image

EMurchieBeyma
1945816
accept rate: 75%

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

(05 May '12, 15:45) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×65
×48
×4

question asked: 26 Apr '12, 15:35

question was seen: 2,722 times

last updated: 06 May '12, 07:36