Entity Framework and Connection Pooling

Question

I've recently started to use the Entity Framework 4.0 in my .NET 4.0 application and am curious about a few things relating to pooling.

1. Connection pooling as I know is managed by the ADO.NET data provider, in my case that of MS SQL server. Does this apply when you instantiate a new entities context (ObjectContext), i.e. the parameterless new MyDatabaseModelEntities()?

2. What are the advantages and disadvantages of a) creating a global entities context for the application (i.e. one static instance) or b) creating and exposing an entities context for each given operation/method, with a using block.

3. Any other recommendations, best practices, or common approaches for certain scenarios that I should know about?

Answer 1

According to Daniel Simmons:

Create a new ObjectContext instance in a Using statement for each service method so that it is disposed of before the method returns. This step is critical for scalability of your service. It makes sure that database connections are not kept open across service calls and that temporary state used by a particular operation is garbage collected when that operation is over. The Entity Framework automatically caches metadata and other information it needs in the app domain, and ADO.NET pools database connections, so re-creating the context each time is a quick operation.

This is from his comprehensive article here:

http://msdn.microsoft.com/en-us/magazine/ee335715.aspx

I believe this advice extends to HTTP requests, so would be valid for ASP.NET. A stateful, fat-client application such as a WPF application might be the only case for a "shared" context.

Answer 2

1. Connection pooling is handled as in any other ADO.NET application. Entity connection still uses traditional database connection with traditional connection string. I believe you can turn off connnection pooling in connection string if you don't want to use it.
2. Never ever use global context. ObjectContext internally implements several patterns including Identity Map and Unit of Work. Impact of using global context is different per application type.
3. For web applications use single context per request. For web services use single context per call. In WinForms or WPF application use single context per form or per presenter. There can be some special requirements which will not allow to use this approach but in most situation this is enough.

If you want to know what impact has single object context for WPF / WinForm application check this article. It is about NHibernate Session but the idea is same.

Edit:

When you use EF it by default loads each entity only once per context. First query creates entity instace and stores it internally. Any subsequent query which requires entity with the same key returns this stored instance. If the values in data store changed you still receive entity with values from initial query. This is called Identity map pattern. You can force object context to reload entity but it will reload single shared instance.

Any changes made to entity are not persisted until you call SaveChanges on context. You can do changes in multiple entities and store them at once (but still it is not a transaction). This is called Unit of Work pattern. You can't selectively say which modified attached entity you want to save.

Combine those two patterns and you will see some interesting effects. You have only one instance of entity for whole application. Any changes to entity affect whole application even if changes are not yet persisted. In the most times this is not what you want. Suppose that you have edit form in WPF application. You are working with entity and you decice to cancel complex editation (changing values, adding related entities, removing other related entities, etc.). But the entity is already modified in shared context. What will you do? Hint: I don't know about any CancelChanges or UndoChanges on ObjectContext.

I think we don't have to discuss server scenario. Simply sharing single entity among multiple HTTP requests or Web service calls makes your application useless.

Even for readonly application global context is not a good choice because you probably want fresh data each time you query the application.