I'm using SQL Anywhere 11.0.1 with Entity Framework 4.1. When I make changes to the entities, then call SaveChanges(), the connection is immediately closed. Unless I involve Distributed Tranaction Coordinater, the transaction is also committed.

I would like to make two SaveChanges() calls in one transaction, without the overhead of DTC. (DTC should not really be necessary when a single client to a single database is involved.) In MS SQL Server, you can do this by explicitly managing the connection and transaction in your code.

Since Entity Framework generates the SQL and Command objects itself, I could not find a way to open a transaction and pass it to a connection object as you would in straight ADO code.

Is there a way to prevent the SQL Anywhere Entity Framework provider from opening and closing the connection on every call to SaveChanges()?

Here's an example of what I want to do:

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

asked 01 May '12, 12:21

EMurchieBeyma's gravatar image

EMurchieBeyma
1945816
accept rate: 75%

edited 05 May '12, 12:58

Calvin%20Allen's gravatar image

Calvin Allen
1.5k232638


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

EMurchieBeyma's gravatar image

EMurchieBeyma
1945816
accept rate: 75%

You should make your comment an answer and mark it as accepted so if anyone else comes along with the same issue, they'll find help :)

(05 May '12, 12:57) Calvin Allen
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
×47
×22
×2

question asked: 01 May '12, 12:21

question was seen: 8,667 times

last updated: 06 May '12, 07:37