c# - DbContext won't keep connection open for re-use

Question

I'm trying to reuse an existing database connection so that I can do multiple database operations using a TransactionScope without invoking MSDTC.

Entity Framework (using the new DbContext API in the 4.1 release) doesn't seem to want to keep an explicitly-opened connection open. The old ObjectContext API keeps the connection open as expected and documented.

Since the DbContext API just uses ObjectContext under the hood, I'd have expected the same behaviour. Does anyone know if this change is intended or a known issue? I can't find it documented anywhere.

public void ConnectionRemainsOpen()
{
    using (var context = new TestDataContext())
    {
        try
        {
            Assert.AreEqual(ConnectionState.Closed, context.Database.Connection.State);

            context.Database.Connection.Open();

            var firstRecord = context.Table3.FirstOrDefault();

            // this Assert fails as State == ConnectionState.Closed
            Assert.AreEqual(ConnectionState.Open, context.Database.Connection.State);

            var newRecord = new Table3
            {
                Name = "test",
                CreatedTime = DateTime.UtcNow,
                ModifiedTime = DateTime.UtcNow
            };

            context.Table3.Add(newRecord);

            context.SaveChanges();

            // this Assert would also fail
            Assert.AreEqual(ConnectionState.Open, context.Database.Connection.State);
        }
        finally
        {
            if (context.Database.Connection.State == ConnectionState.Open)
                context.Database.Connection.Close();
        }
    }
}

Answer 1

If you want to control the connection you must create it prior to context and pass it to context otherwise the connection is not under your control. Try something like:

using (var connection = ...)
{
    using (var context = new TestDataContext(connection, false))
    {
        ...
    }
}