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?

Advertisements

6 thoughts on “EF 4.1 DbContext Issue : Manually opening and closing the database connection

  1. Thanks for this post. We experienced the same thing as you described in your first code example. We then implemented our process using the ObjectConext like you show and it seems to work. One thing that I am curious about is on the sql sever back-end. I ran a profiler trace to verify sp_reset_connection wasn’t getting called for each query. It worked properly this time. However, I’m not getting any audit logout events in the trace after I close the connection. If I call the same process or stop my service, I then see the Audit Logout event. On the .NET side the connection shows to be disposed. I was thinking that this would have signaled Sql Server to also close the connection. Maybe it does after a certain amount of time. It may be nothing to worry about but wasn’ sure.

    Anyways, I wanted to check and see if you have put your code in a production environment and if you ever found out any major differences in the ObjecContext vs the DbContext.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s