By Noldorin


2010-09-06 16:42:47 8 Comments

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?

4 comments

@Raj Rao 2016-09-09 23:20:07

Accoriding to EF6 (4,5 also) documentation: https://msdn.microsoft.com/en-us/data/hh949853#9

9.3 Context per request

Entity Framework’s contexts are meant to be used as short-lived instances in order to provide the most optimal performance experience. Contexts are expected to be short lived and discarded, and as such have been implemented to be very lightweight and reutilize metadata whenever possible. In web scenarios it’s important to keep this in mind and not have a context for more than the duration of a single request. Similarly, in non-web scenarios, context should be discarded based on your understanding of the different levels of caching in the Entity Framework. Generally speaking, one should avoid having a context instance throughout the life of the application, as well as contexts per thread and static contexts.

@Fletchius 2017-02-10 16:03:45

I know this reply has been here a while, but I have to say this saved me a ton of headache. Kept getting "Pooled Connection" error when using EF with Oracle, and couldn't figure out why. I had set the dbContext up as a class variable, instantiating it at creation. Changing it to creating the context as needed fixed all the ills of my world. Thank you!

@Ladislav Mrnka 2010-09-06 17:55:42

  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. (read more about SQL Server Connection Pooling (ADO.NET))
  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. The first query creates entity instace and stores it internally. Any subsequent query which requires entity with the same key returns this stored instance. If values in the data store changed you still receive the entity with values from the initial query. This is called Identity map pattern. You can force the object context to reload the entity but it will reload a single shared instance.

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

Combine these two patterns and you will see some interesting effects. You have only one instance of entity for the whole application. Any changes to the entity affect the whole application even if changes are not yet persisted (commited). In the most times this is not what you want. Suppose that you have an edit form in WPF application. You are working with the 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. Any request can just trigger SaveChanges and save partial data from another request because you are sharing single unit of work among all of them. This will also have another problem - context and any manipulation with entities in the context or a database connection used by the context is not thread safe.

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

@Noldorin 2010-09-06 18:09:03

Thanks for your reply. Perhaps you could elaborate on why it is bad to use a single global context? It makes parallel access harder, for sure, but what else...?

@Noldorin 2010-09-06 19:37:27

Ok, that's a lot clearer now, thank you. Just to confirm, although a global context is never really appropriate, a single context for an "edit dialog" or such may be the right way? In other situations, like web services and ASP.NET, contexts within methods only makes more sense. About correct?

@Elad Benda 2013-02-10 09:43:41

I took your advise and removed the singelton. Now I get another error: stackoverflow.com/questions/14795899/…

@Rudolf Dvoracek 2013-03-08 12:53:08

I understand that implementing Unit of work pattern and encapsulating DbContext should separate business logic and database operations. I'm not able to understand how to implement Unit of work pattern and use TransactionScope only for some operations.

@Ladislav Mrnka 2013-03-08 14:15:50

@RudolfDvoracek: Easily. TransactionScope doesn't belong to unit of work, it belongs to your business logic because the logic itself defines transaction. Unit of work only defines what should be persisted together whereas transaction scope allows you using unit of work persistence multiple times within same transaction.

@Max Toro 2014-03-13 00:18:39

How does the context per request pattern relate to connection pooling? The whole idea of pooling is that you can create as many contexts as you want and they'll use an available connection.

@Ladislav Mrnka 2014-03-13 10:27:58

@MaxToro: You can use as many context as you want if you don't share them with operation which is not part of the same unit of work. By using more contexts you can reach some problems with tracking entities.

@Shavais 2014-07-01 15:36:45

In the context of a web service, doesn't a static variable only have worker level scope? When a worker process is dropped, it's static references go away, right? And isn't a given worker single threaded? A given worker process only processes one request at a time, right? So you shouldn't have requests being processed in parallel using the same object context, and stomping on each other? But I guess if you left changes unsaved, on purpose, in the handling of one request, the next request's processing might unintentionally save them. That kind of thing could certainly happen.

@Shavais 2014-07-01 15:47:47

I think that kind of possibility is actually the main reason why the progenators of ORMs like the EF framework generally advocate minimizing the scope and lifetime of object contexts - because the ending state of a given context, after the processing of a given transaction, may not be such that another transaction can be engaged right away. The context may be in an error state or an unsaved state, a state in which results are being presented, et etc. The possibilities are complex enough that experience with them tends to make a person leary of trying to reuse them.

@Ladislav Mrnka 2014-07-01 16:18:43

@Shavais: Do you mean worker process in IIS? That is not single threaded.

@Shavais 2014-07-01 19:22:10

@Ladislav: Right, I see that you're correct. So if statics have worker scope (as opposed to worker thread scope), and there are some number of workers for an app domain, and some number of threads for a worker.. what scope exactly does a static have? It's not app domain scope, and it's not request scope, it's something in between.

@Ladislav Mrnka 2014-07-02 09:45:26

@Shavais: Web process can host multiple AppDomains (each representing separate web application). Each AppDomain has its own thread pool with threads for serving incoming requests. Static variable has AppDomain scope. Having multiple web processes hosting the same application still means that each of these AppDomains has its own static variable. That is why for example in-process Session in ASP.NET does not work for web gardens and web farms.

@HGMamaci 2014-03-13 00:09:52

Below code helped my object to be refreshed with fresh database values. The Entry(object).Reload() command forces the object to recall database values

GM_MEMBERS member = DatabaseObjectContext.GM_MEMBERS.FirstOrDefault(p => p.Username == username && p.ApplicationName == this.ApplicationName);
DatabaseObjectContext.Entry(member).Reload();

@Ivan Ferrer Villa 2015-09-25 16:01:54

as well as this for collections (VB code):CType(myContext, IObjectContextAdapter).ObjectContext.Refresh(RefreshMode.Sto‌​reWins,myCustomers)

@Dave Swersky 2010-09-06 16:53:31

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.

@Noldorin 2010-09-06 16:59:42

Thanks, that's a very informative quote there. However, I'm still wondering whether a shared (global) context would be appropiate even for a client WPF app or such. Is there any advantage even in this case?

@Dave Swersky 2010-09-06 17:05:28

There would be no advantage to a global context in a WPF app, but there would probably be no significant detriment either. If you do implement a global context, you might have to do some manual managing of database connections (explicit closing of the connection) in cases of high request rates.

@Noldorin 2010-09-06 17:52:17

Right; so essentially I can never really go wrong by using multiple temporary contexts (given I know connection pooling is happening)? ...If you were using a single global context, couldn't the connection in theory drop at a random point in time?

@Dave Swersky 2010-09-07 00:37:29

@Nolodrin: I don't think the connection would drop "randomly"... the risk is that connections could be held open too long and saturate the connection pool.

@Noldorin 2011-12-21 15:17:31

Sure they could, if the SQL server or even the transmission line suddenly went down. The Internet is a place of few absolute guarantees. ;-)

@nicodemus13 2012-05-15 14:24:39

ObjectContext/ DbContext implement IDisposable, therefore should be open for the shortest reasonable time, is my view.

Related Questions

Sponsored Content

28 Answered Questions

[SOLVED] Fastest Way of Inserting in Entity Framework

10 Answered Questions

[SOLVED] Using MySQL with Entity Framework

5 Answered Questions

11 Answered Questions

[SOLVED] How can I get Id of inserted entity in Entity framework?

18 Answered Questions

[SOLVED] How do I view the SQL generated by the Entity Framework?

16 Answered Questions

[SOLVED] Entity Framework vs LINQ to SQL

33 Answered Questions

2 Answered Questions

1 Answered Questions

[SOLVED] Entity Framework 4.0 Best Practise for Creating Object Context

5 Answered Questions

[SOLVED] Connection Pooling in .NET/SQL Server?

Sponsored Content