Entity Framework 4 and “New transaction is not allowed because there are other threads running in the session” Exception

Friday, July 9, 2010

Entity Framework 4 Entity Framework Exceptions

I’ve been working through a server-side process with Entity Framework wherein an uploaded file must be updated after initial entry. I have a multi-stage process that goes out to multiple other affinity web services to get complimentary data. Here is code phase 1:

using (EntityConn context = new EntityConn())
{
   var pts = from t in context.Points
             where t.Track.File.FileID == input
             select t;
   foreach (var pt in pts)
   {
      pt.ValueAdded =
         VendorMethodCall(pt.Lat, pt.Lon);
      pt.Loaded = true;
   }
   context.SaveChanges();
}

This works well except that VendorMethodCall() can take quite a bit of time so I want to show progress. I do this with another web service call that checks the database for records with the Loaded bit field to true. Oops. Nothing happens till the end and it’s all transactional so I go from 0% to 100% after a very long wait. That won’t do. Simple, just change the code to update the context after every method call. Like so:

using (EntityConn context = new EntityConn())
{
   var pts = from t in context.Points
             where t.Track.File.FileID == input
             select t;
   foreach (var pt in pts)
   {
     pt.ValueAdded =
           VendorMethodCall(pt.Lat, pt.Lon);
      pt.Loaded = true;
      context.SaveChanges();
   }
}

Turns out this does not work. The first time the context.SaveChanges() method is called I get an exception whose inner exception is: “New transaction is not allowed because there are other threads running in the session.” Turns out that the READ happens via a transaction in EF 4! Not sure why that is and it may be an editable setting (optimistic v/s pessimistic locking?) but I cannot find the setting so I’m moving on to the work around.

The first thing I tried was to batch up 25 records at a time under the premise that previous saves had not completed. That didn’t work but in my final solution I did end up incorporating that method just so I could issue fewer individual statements. Most of the files I’m working with have upwards of 1,500 data points so I’ll probably bump the batch size to 100 or so. At any rate, the secret here is to load your data into an array and iterate the array rather than the actual result set from your Entity Linq statement.

using (EntityConn context = new EntityConn())
{
   var pts = from t in context.Points
             where t.Track.File.FileID == input
             select t;
   int iBatch = 0;
   var ptsList = pts.ToArray<Point>();
   foreach (var pt in ptsList)
   {
      pt.ValueAdded =
           VendorMethodCall(pt.Lat, pt.Lon);
      pt.Loaded = true;
      iBatch++;
      if (iBatch % 25 == 0)
      {
         context.SaveChanges();
         iBatch = 0;
      }
   }
   context.SaveChanges();
}

This works and does not throw an exception. I have not reviewed the SQL statements generated since my dev server for this project is hosted and I don’t have appropriate permissions. I will now have to try it locally to see if the traffic is at a permissible level.

On a side note, notice the Linq to Entities statement that allows me to cycle through 3 (!!) tables to get to the records I want using a single statement. In normal T-SQL, I would have to either use inner joins or use IN clauses to get the values I was looking for. The SQL will turn out to be similar but this dramatically simplifies the code.

1 Comment