Entity Framework 5 using Db Transactions "connection is already part of a local or distributed transaction"

Question

I have not had any luck with transactions and entity framework 5. I have the following code:

 context.Database.Connection.Open();
 transaction = context.Database.Connection.BeginTransaction(IsolationLevel.Serializable);
//some work happens
context.SaveChanges();
//some additional work
context.SaveChanges();
transaction.Commit();

At the very first context.SaveChanges call, I get an exception: "Connection is already part of a local or a distributed transaction"

Right now I am actually just doing a trivial proof of concept where all I am doing is attaching an entity, marking it as modified and then calling save changes.

As a troubleshooting deal, I put in an event handler for when the connection state changes and had a breakpoint in there. Doing that, I verified that the connection did not close on me between when I started the transaction and when I called save changes.

Any help figuring out why it is giving me that exception would be tremendously appreciated.

I haven't found anything about how transactions work with EF 5. I found an article for transactions with EF 6 and newer, but we aren't using EF 6 because the oracle data access dlls aren't production ready. My initial proof of concept appears to behave like EF doesn't want any calls to context.SaveChanges during a transaction. If I take out the intermediate save changes and have just a save changes after commit, it behaves correctly in my trivial POC. — Danny
– Danny, Commented Dec 11, 2014 at 23:02
The stack trace goes deep into the oracle data access dll with the error. Logically I see how any context.savechanges is a transaction, so it appears that EF does a transaction under the hood. So in that regard, the notion of starting a transaction yourself is kind of rendered moot with Entity Framework. — Danny
– Danny, Commented Dec 12, 2014 at 17:45

Bjorn Vdkerckhove · Accepted Answer · 2014-12-12 14:22:43Z

1

This is the way we used transactions before. It worked for us:

public void DoSomething()
{
   using (var db = GetContext())
   {
        using (var ts = GetTransactionScope())
        {
            //do stuff
            db.SaveChanges();
            ts.Complete();
        }
   }
}

public TransactionScope GetTransactionScope()
{
    var tso = new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted };
    return new TransactionScope(TransactionScopeOption.Required, tso);
}

answered Dec 12, 2014 at 14:22

Bjorn Vdkerckhove

7458 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Danny Over a year ago

So I see a lot of examples like this, but my knee jerk reaction is to shy away from that when it appears that we have been given a specific DbTransaction methodology via the context.Database.Connection. It really seems odd to me that we are given that in EF5, but nobody works with it at all. Thoughts?

Danny Over a year ago

As I am looking at things, the behavior that you get with a given database context is itself transactional in nature. So it makes sense that context.SaveChanges() works with a transaction. In a sense it seems that there is not really an intrinsic value to starting your own transaction if you are going to use entity framework because all work on a context is itself a transaction by definition. I suppose it still is needed because at the database level you can run your own sql commands if you are crazy enough to want to do that.

Gert Arnold · Accepted Answer · 2014-12-12 20:38:25Z

If for some reason you have to do multiple SaveChanges calls in one transaction the recommended way is to wrap them in a TransactionScope:

using(var tran = new TransactionScope())
{
    using(var context = new MyContext())
    {
        //some work happens
        context.SaveChanges();
        //some additional work
        context.SaveChanges();
    }

    tran.Complete(); // without this call the transaction is rolled back.
}

The default isolation level is serializable. Each connection that is opened within the transaction enlists in this transaction. By default, EF always opens and closes connections when it executes queries.

I guess the cause of this exception you've got is that EF creates a transaction object itself when it executes SaveChanges. It tries to use its connection to start this transaction, but the connection is already part of the transaction you created. By using a TransactionScope, the EF transaction just enlists in the ambient transaction.

Collectives™ on Stack Overflow

Entity Framework 5 using Db Transactions "connection is already part of a local or distributed transaction"

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related