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

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. — Tridus, Jul 19 '11 at 19:01
@Tridus, the Assert you suggest passes with an expected result of `Open`. — GWB, Jul 19 '11 at 19:09
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. — Tod, Jan 26 '12 at 01:26

score 15 · Accepted Answer · answered Jul 19 '11 at 19:21

15

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

answered Jul 19 '11 at 19:21

Ladislav Mrnka

360,892
59
660
670

Is this a documented change between EF 4.0 and 4.1? – GWB Jul 19 '11 at 20:00
I think it is documented because second parameter of the constructor tells if context owns the connection. – Ladislav Mrnka Jul 19 '11 at 21:35
I tried your solution but get the same result. The context still changes the connection state as it pleases. – GWB Jul 20 '11 at 15:58
1

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. – Lee Harold Jul 26 '11 at 20:35
@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. – Ladislav Mrnka Jul 26 '11 at 22:05
Ultimately this answer does work. I'm not sure why it didn't work for me before. Accepted and upvoted accordingly. – GWB Dec 13 '11 at 21:40
https://learn.microsoft.com/en-us/ef/ef6/fundamentals/connection-management – deathrace Jun 09 '20 at 07:26

DbContext won't keep connection open for re-use

1 Answers1

Linked