Connection Management in Entity Framework 6.0

Introduction

In Entity Framework 5.0 and onward, we are able to pass a connection to a DbContext object. In the earlier version, to do this we need to create a partial class that inherits the Dbcontext / ObjectContext class. This class has a constructor that allows for passing a connection string into the class.

In Entity Framework 5.0 and earlier versions we can pass the connection in the constructor argument but they have a couple of limitations.

1. Entity Framework throws an InvalidOperationException and says it cannot reopen a connection that is already open when we pass an open connection.
   
   
2. We can pass a contextOwnsConnection flag using the Constructor of DbContext. Regardless of this the stored connection should be closed or disposed of when the DbContext is disposed. If we have multiple DbContexts with the same connection then the connection should be disposed of whenever the first closes the connection. Similarly things should happen for the mixed mode of ADO.Net and Entity Framework. DbContext always closes the connection when it is disposed of.
   
   
3. The ObjectContext.Connection.State was not updated to the true state of the underlying stored connection. For example if we check ((IObjectContextAdapter)context).ObjectContext.Connection.State then this always returns Closed even if the underlying store connection is open.

The construction of DbContext that accepts the connection as arguments:

public DbContext(DbConnection existingConnection, bool contextOwnsConnection);

public DbContext(DbConnection existingConnection, DbCompiledModel model, bool contextOwnsConnection);

Work-around of the receding limitation

The work-around of the first limitation is to pass a closed connection to DbContext and execute the code to open the connection after all contexts have been created.

Example

using (var context1 = new Entities())
{
    var conn = ((EntityConnection)((IObjectContextAdapter)context1).ObjectContext.Connection).StoreConnection;

    using (var context2 = new Entities(conn, contextOwnsConnection: false))
    {
        context2.Database.ExecuteSqlCommand(
            @"UPDATE TestTable SET Number = 5" +
            " WHERE TestId = 10");

        var query = context1.TestTable.Where(p => p.TestId > 15);
        foreach (var data in query)
        {
            data.Name =data.Name + ". This is test";
        }
        context1.SaveChanges();
    }
}

The work-around of the second limitation is to stop the disposing of any of our DbContexts until we are ready to close it. There is no work-around for the thirrd limitation (it is a bug).

Behavior in Entity Framework 6.0 and later versions

Entity Framework 6.0 and later versions have the same two constructors. Now there is no need to pass a closed connection to the constructor.

Example

using (var conn = new SqlConnection("our connectionString"))
{
    conn.Open();

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;
    cmd.CommandText = @"UPDATE TestTable SET Number = 5" +
                        " WHERE TestId=10";
    cmd.ExecuteNonQuery();

    using (var context = new Entities(conn, contextOwnsConnection: false))
    {
        var query = context.TestTable.Where(p => p.TestId > 15);
        foreach (var data in query)
        {
            data.Name = data.Name + ". This is test";
        }
        context.SaveChanges();
    }

    var cmd2 = new SqlCommand();
    cmd2.Connection = conn;
    cmd2.CommandText = @"UPDATE TestTable SET Number = 5" +
                        " WHERE TestId = 11";
    cmd2.ExecuteNonQuery();
}

Now the contextOwnsConnection flag can control the connection and  dispose of the flow, in other words the connection is not closed or disposed of when the DbContext is disposed of.

Summary

All the limitations that existed in EF 5.0 and previous versions is now removed in the EF 6.0 version. Now ObjectContext.Connection.State maintains the state of the underlying connection correctly.