.Net Entity Framework and Transactions

Question

Hi,

Being new to the EF, I'm really rather stuck on how to proceed with this set of issues. On the project I am currently working on, the entire site is heavily integrated with the EF model. At first, the access to the EF context was being controlled using an IOC bootstrapper. For operational reasons we were not able to use IOC. I removed this and used a model of individual instances of the context object where required. I started getting the following exception:

The type 'XXX' has been mapped more than once.

We came to the conclusion that the different instances of the context were causing this issue. I then abstracted the context object into a single static instance which was being accessed by each thread/page. I'm now getting one of several exceptions about transactions:

New transaction is not allowed because there are other threads running in the session.

The transaction operation cannot be performed because there are pending requests working on this transaction.

ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized.

The last of these exceptions occurred on a load operation. I wasn't trying to save the context state back to the Db on the thread that failed. There was another thread performing such an operation however.

These exceptions are intermittent at best, but I have managed to get the site to go into a state where new connections were refused due to a transaction lock. Unfortunately I cannot find the exception details.

I guess my first question is, should the EF model be used from a static single instance? Also, is it possible to remove the need for transactions in EF? I've tried using a TransactionScope object without success...

To be honest I'm a lot stuck here, and cannot understand why (what should be) fairly simple operations are causing such an issue...

Cheers, sicknote

Answer 1

Creating a global ObjectContext in a web application is very bad. The ObjectContext class is not thread-safe. 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 single user.

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

Please think about this why this can't work. The ObjectContext contains a local cache of entities in your database. It allows you to make a bunch of changes and finally sumbit those changes to the database. When using a single static ObjectContext, 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 user might still be making changes. When you're lucky you EF or your database will fail, because the entities are in an invalid state. If you're unlucky objects that are in an invalid state are successfully saved to the database and you might find out weeks later that your database if full of crap. The solution to your problem is to create at least one ObjectContext per request. While in theory you could cache an object context in the user session, this also is a bad idea, because the ObjectContext will typically live to long and will contain stale data (because its internal cache will not automatically be refreshed).

Good luck.

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