oledu.com
oledu.com

Reputation: 339

EF using and BeginTransaction, when to call Connection.Close()?

After opening a connection, Do I have to explicitly close it? or does .net close it automatically? What about if an exception is thrown?

using (DataContext pContext = new DataContext())
{
    pContext.Connection.Open()

    using (var trx = pContext.Connection.BeginTransaction())
    {
        //make changes to objects 

        //Make calls to ExecuteStoreCommand("Update table SET pee=1 AND poop=2 WHERE ETC");

        pContext.SaveChanges();

        trx.Commit();
    }
}

Does pContext.Connection.Close()` get called in all instances by the framework? I know that in a normal using statement it does, but what about when I manually open it?

I am using the Connection.BeginTransaction because In my tests, (using SaveChanges() alone without transactions) if this code executes and fails for some reason, the commands sent during ExecuteStoreCommand() are saved even though the changes to my objects aren't.

I have to call Connection.Open() before calling pContext.Connection.BeginTransaction() otherwise I get the error:

Inner Exception:
The connection is not open.

Any help would be appreciated.

Upvotes: 1

Views: 4236

Answers (2)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131413

There is no need to call DataContext.Connection.Open(). In fact, it's bad practice to do so.

The context opens a connection only when it needs to. By calling Connection.Open, you open the connection for far longer that is needed, increasing the chance that locks will accumulate and lead to blocking. It is also a good way to exhaust the connection pool in high traffic systems.

SaveChanges opens a connection and a transaction itself, so there is no need to manually open the connection or start the transaction. From the documentation:

SaveChanges operates within a transaction. SaveChanges will roll back that transaction and throw an exception if any of the dirty ObjectStateEntry objects cannot be persisted

Explicitly using connections and transactions makes sense only if you want to mix raw SQL commands inside the EF session. By itself this is not a good practice, but if you do, you don't need to close the connection because the DataContext will close the connection when it is disposed. There is a sample in the documentation, at How to: Manually Open the Connection from the Object Context

You can also handle a transaction using a TransactionScope, as described in How to: Manage Transactions in the Entity Framework. In this case, you don't need to open the connection as all changes are automatically enlisted in the current transaction. This is described in http://msdn.microsoft.com/en-us/library/bb738523(v=vs.100).aspx and an example would be :

using (DataContext pContext = new DataContext())
{

    using (TransactionScope transaction = new TransactionScope())
    {
        //execute commands   



        pContext.SaveChanges();

        transaction.Complete()
    }
}

In any case, manually handling connections and transactions is not trivial. You should read Managing Connections and Transactions for some of the things you need to be aware

Upvotes: 9

Kamil Budziewski
Kamil Budziewski

Reputation: 23087

This means

using (DataContext pContext = new DataContext())

that pContext is disposed after leaving using

it's equivalent to:

DataContext pContext = new DataContext()
try
{
   //do stuff here
}
finally
{
   if(pContext!=null)
      ((IDisposable)pContext).Dispose();
}

so answer is -> you don't need to call close after your code because

Connections are released back into the pool when you call Close or Dispose on the Connection..."

from here

Upvotes: 1

Related Questions