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

Solution 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...

Solution 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.

Comments

[Elad Benda]

we expect large scales of requests (even after CDN). I think open and close connection are expensive action. Let alone creating and storing many objects in the application heaps

[Jon Skeet]

You think, or you have empirical evidence? Don't forget that there's connection pooling underneath the application layer already. As for objects: don't forget that short-lived objects are effectively cheaper in terms of GC than long-lived objects.

[Elad Benda]

now I use using on new contextObject for each unit. But my BL code uses the objects and I get thie exception:System.ObjectDisposedException: The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

[Elad Benda]

now I use using on new contextObject for each unit. But my BL code uses the objects and I get thie exception:System.ObjectDisposedException: The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

[Jon Skeet]

@EladBenda: Right - so it sounds like your context is now too narrow, in that it's not encompassing the complete unit of work. You should basically have the context around for as long as you need it, but no longer.

[Elad Benda]

but then I mixes layers: BL and Dal. It not mentainble to instanciate the contextObject in the BL, as it's Dal object