Execution of the command requires an open and available connection. The connection's current state is broken.

Question

After some use in qa we get the following error

Execution of the command requires an open and available connection. The connection's current state is broken.

We’re using a singleton instance of EntityFramework

SOF suggests :

EF recovery from invalidoperationexception caused by server being down

1) creating a new instance of ContectObject once in a while

2) configure the number of pool connections to be higher

What is the best practice to solve this?

I think it's wastefull to create a new contectObject for each Dal operation

Answer 1

I think it's wastefull to create a new contectObject for each Dal operation

Do you have any evidence for this? I believe that Entity Framework and indeed most data access frameworks are designed for lots of short-lived and independent contexts. Implementing your own pooling / caching here is usually an anti-pattern, possibly resulting in stale results, concurrency issues, and poor failure recovery (as is the case here).

What specific resources do you think will be wasted, and have you validated this experimentally?

Basically, I would suggest creating a fresh context for each unit of work (may well correspond roughly to a request) - measure any performance differences, and see whether the problem goes away (as I expect it will). As part of your testing, occasionally disconnect the database server from the network to check that you can actually recover, too...

Answer 2

Please, read about connection pooling.

When you're disposing EF context, you're disposing underlying store provider connection. But, when you're disposing store connection, you're not closing any transport level connection by default, unless you haven't turned off connection pooling explicitly.

Moreover, EF caches metadata views per application domain.
Hence, EF context creation is cheap, really.

Also, keep in mind, that there's change tracker in every context instance. When you have single context, its change tracker tracks everything, that was materialized or attached to context. "Tracks everything" means, that every entity instance you retrieved through context, will not be disposed - tracker keeps a reference to it.

Don't make long-living context instances.