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();
        }
    }
}

Thermos

If you put an Assert after Open() but before the first query, what's the result then? I'm wondering if the problem here is actually just that it's returning a wrong value rather then actually closing and reopening connections. – Communize

@Tridus, the Assert you suggest passes with an expected result of Open. – Thermos

I realize this is an old post but I was just reading Julia Lerman's book and the syntax she uses is context.Connection.Open() (i.e. no Database between context and Connection). Just a thought. – Transcurrent

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))
    {
        ...
    }
}

Shutz

Is this a documented change between EF 4.0 and 4.1? – Thermos

I think it is documented because second parameter of the constructor tells if context owns the connection. – Shutz

I tried your solution but get the same result. The context still changes the connection state as it pleases. – Thermos

Create the connection like this: using (var connection = new EntityConnection("name=MyContainer")) - I tested by creating 2 subsequent DbContexts and passing in the connection and only 1 connection is ever created, as expected. – Orthochromatic

@Lee: That will work only if you have mapping defined in EDMX, won't it? I just tested myself playing with connection and at this point I think it is a bug because it really doesn't behave as it should have. – Shutz

Ultimately this answer does work. I'm not sure why it didn't work for me before. Accepted and upvoted accordingly. – Thermos

learn.microsoft.com/en-us/ef/ef6/fundamentals/… – Geosyncline