EF 4.1 DbContext Issue : Manually opening and closing the database connection

As I'm sure many of you are aware the Entity Framework will create and close database connections automatically when needed. This is great most of the time, however when we want to manually configure the connection for performance or to perform a list of actions within a transaction we don't want the entity framework to automatically close our connection.

I've found an issue where I'm trying to manually manage my DbContext connection and the DbContext API does not want to let me.
(I'm using Sql Server 2005 and am trying to avoid transaction promotion to the DLC which means I want to do all of my queries on the same connection).

In ObjectContext land, when I call ObjectContext.Connection.Open() I am manually opening the connection and the documentation states on MSDN that this connection will NOT be closed until I call the Close() method or dispose of the context.

It seems calling DbContext.Database.Connection.Open() does not give the same results. When called I watch the context close and reopen for each query. Below is the code that I am trying to write that presents the problem.

DbContext version:

dbContext.Database.Connection.Open();

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))  
{
    // perform a list of queries
    // the connection will close
    scope.Complete();
    dbContext.Database.Connection.Close();
}

ObjectContext version:

(dbContext as IObjectContextAdapter).ObjectContext.Connection.Open();

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required))  
{
    // perform a list of queries
    // The connection will not close!
    scope.Complete();
    (dbContext as IObjectContextAdapter).ObjectContext.Connection.Close();
}

So the fix for now is to get the ObjectContext from your DbContext. But can someone explain what the difference is and is this by design?

comments powered by Disqus