Exception from DbContext API: EntityConnection can only be constructed with a closed DbConnection

UPDATE: After I posted this article we found that the plan we had to enable the pattern with contex.Database.Connection.Open() would cause breaking changes in important scenarios: the connection, the EF model, and potentially the database would be initialized any time you somehow acess the Database.Connection property. We decided to back out of this plan for EF 5, therefore we will revisit fixing this issue completely on EntityConnection in EF 6.0.

In several situations we recommend opening the database connection explicitly to override Entity Framework's default behavior, which is to automatically open and close the connection as needed. You may need to do this if for example you are using:

• SQL Azure and you want to test that the connection is valid before you use it (although the failures in this situation have been reduced with the release of an update to SqlClient in August).
• A federated database and you need to issue the USE FEDERATION statement before you do anything else.
• TransactionScope with a version of SQL Server older than SQL Server 2008 and you want to avoid the transaction from promoting to twi-phase commit.
• TransactuinScioe with a database - like SQL CE - that doesn't support two-phase-commit and hence you want to avoid the ambient transactions from being promoted.

The code with the ObjectContext API usually looks similar to this:

  1: using(var context = new MyEntities())
  2: {
  3:     context.Connection.Open();
  4:     var query = 
  5:         from e in context.MyEntities
  6:         where e.Name.StartsWith(name)
  7:         select e;
  8:     EnsureConnectionWorks(context.Connection);
  9:     foreach(var entity in query)
  10:     {
  11:         // do some stuff here
  12:     }
  13: }

If you try to use similar code with a DbContext in the current version of Entity Framework, i.e. if you try calling context.Database.Connection.Open(), things won’t work as expected. Most likely, you will get an exception with the following message:

EntityConnection can only be constructed with a closed DbConnection

The issue occurs because the connection object exposed in the DbContext API (context.Database.Connection) is not an EntityConnection but the actual database connection. We made that design choice on purpose because it allows us to remove the need to learn about the existence of a whole API layer in order to use Entity Framework. Unfortunately, the choice also kills the pattern of opening the connection explicitly.

If you are not curious about the technical implementation details, you just need to know that the best approach available in the current version of EF to avoid this exception and still control the lifetime of the connection is to drop down to the underlying ObjectContext instance and open the EntityConnection on it:

  1: ((IObjectContextAdapter)context).ObjectContext.Connection.Open();

If everything goes according to plan, EF 5.0 will include changes that will make this unnecessary so that simply invoking context.Database.Connection.Open() will work.

If you do want to hear what happens under the hood and how things will work in EF 5.0, here are some more details:

Similar to other classes in the EntityClient namespace, EntityConnection was designed to behave like the DbConnection of a real ADO.NET provider. But the implementation of EntityConnection wraps the underlying database connection object and takes over its state. On the other hand, any time an ObjectContext needs the connection to perform some operation against the database, it asks the EntityConnection for its current state state and if it finds that the connection is closed, it infers that the implicit on-demand open/close behavior is needed.

When you open the database connection exposed in context.Database.Connection, one of two things may happen:

1. You may open the database connection before the underlying ObjectContext gets initialized: If this is the case the operation will succeed, but you will get the exception as soon as the underlying ObjectContext instance gets initialized (e.g. as a side effect of executing a query), because initializing the ObjectContext also involves initializing the EntityConnection. As the exception message says, the reason this will fail is that the constructor of EntityConnection validates that the database connection passed to it is in the closed state. The main reason the constructor of EntityConnection only takes a closed database connection is to simplify the implementation and to mitigate the need to synchronize the state among the two connection objects.
2. You may instead open the database connection after the underlying ObjectContext get initialized, in which case you won’t get the exception but you won't get the desired effects either: EF will still close the connection after using it. The reason that happens is that EF checks the state of the EntityConnection and not the state of the real database connection. EntityConnection maintains its own state independently from the state of the underlying connection, so even if the database connection is in the opened state, the EntityConnection will appear closed. 

We considered changing the behavior of EntityConnection in .NET 4.5 so that the constructor would accept an open database connection, and to make its connection state property delegate to the corresponding property of the underlying database connection. This would have meant that an EntityConnection could be now created in the open state. After some analysis we realized that things would get very tricky and that in certain cases the proposed changes could break existing applications. .NET 4.5 is an in-place update for .NET 4.0 so we are not making deliberate changes that may break existing .NET 4.0 apps.

Instead we figured out a way (I think it was Andrew Peters who suggested it) to make the fix in the DbContext API by making the EntityConnection follow the state of the underlying database connection. DbConnection exposes an event, StateChage that is perfect for this purpose, so we just subscribe to the event in the database connection and then call Open and Close on the EntityConnection as necessary. This implies that whenever someone access the context.Database.Connection property, the underlying ObjectContext and EntityConnection have to be initialized. This is a breaking change too, but one that we are willing to take given the benefits and given that EF 5.0 (i.e. the new version of the EntityFramework.dll) is not an in-place update for EF 4.x.

We made one exception to this new behavior though: if you access the Connection property in context.Database during model creation (i.e. inside OnModelCreating) we won’t initialize the underlying ObjectContext (how could we, if we still don’t know what the final model is going to look like?).

Comments

• Anonymous
  February 28, 2012
  The comment has been removed

• Anonymous
  July 27, 2012
  Hi, Is it possible in EF 5.0 to initialize a DbContext with an existing opened ADO.NET connection? GILLES>

• Anonymous
  July 28, 2012
  @Gilles: No. Unfortunatelly, as I detailed in the update our attempts to improve this in EF5 hit a few breaking changes and we decided to postpone to EF6. Currently we have this in our backlog at entityframework.codeplex.com/.../45. I encourage you to vote for this item.