.NET Entity Framework and transactions

Solution 1:

Creating one global Entity Framework DbContext in a web application is very bad. The DbContext class is not thread-safe (and same holds for Entity Framework v1's ObjectContext class). It is built around the concept of the unit of work and this means you use it to operate a single use case: thus for a business transaction. It is meant to handle one single request.

The exception you get happens because for each request you create a new transaction, but try to use that same DbContext. You are lucky that the DbContext detects this and throws an exception, because now you found out that this won't work.

The DbContext contains a local cache of entities in your database. It allows you to make a bunch of changes and finally submit those changes to the database. When using a single static DbContext, with multiple users calling SaveChanges on that object, how is it supposed to know what exactly should be committed and what shouldn't?

Because it doesn't know, it will save all changes, but at that point another request might still be making changes. When you're lucky, either EF or your database will fail, because the entities are in an invalid state. If you're unlucky, entities that are in an invalid state are successfully saved to the database and you might find out weeks later that your data got corrupted.

The solution to your problem is to create at least one DbContext per request. While in theory you could cache an object context in the user session, this also is a bad idea, because in that case the DbContext will typically live too long and will contain stale data (because its internal cache will not automatically be refreshed).

Also note that having one DbContext per thread is about as bad as having one single instance for the complete web application. ASP.NET uses a thread pool which means that a limited amount of threads will be created during the lifetime of a web application. This basically means that those DbContext instances will in that case still live for the lifetime of the application, causing the same problems with staleness of data.

You might think that having one DbContext per thread is actually thread-safe, but this is usually not the case, since ASP.NET has an asynchronous model that allows finishing requests on a different thread than where it was started (and the latest versions of MVC and Web API even allow an arbitrary number of threads handle one single request in sequential order). This means that the thread that started a request and created the ObjectContext can become available to process another request long before that initial request finished. The objects used in that request however (such as a web page, controller, or any business class), might still reference that DbContext. Since the new web request runs in that same thread, it will get the same DbContext instance as what the old request is using. This again causes race conditions in your application and cause the same thread-safety issues as what one global DbContext instance causes.

Solution 2:

As you refer to the "site" in you question i assume this is a web application. Static members exist only once for the entire application, if you are using a singleton type pattern with a single context instance across the entire application, all sorts of requests are going to be in all sorts of states accross the entire application.

A single static context instance won't work, but multiple context instances per thread will be troublesome as well as you can't mix and match contexts. What you need is a single context per thread. We have done this in our application using a dependency injection type pattern. Our BLL and DAL classes take a context as a parameter in the methods, that way you can do something like below:

using (TransactionScope ts = new TransactionScope())
{
    using (ObjectContext oContext = new ObjectContext("MyConnection"))
    {
        oBLLClass.Update(oEntity, oContext);
    }
}

If you need to call other BLL/DAL methods within your update (or whatever method you chose) you simply pass the same context around. That way updates/inserts/deletes are atomic, eveything within a single method is using the same context instance, but that instance isn't being used by other threads.