.NET EntityFramework: "An error occurred while starting a transaction on the provider connection. See the inner exception for details"

Question

Using Entity Framework in .NET I want to loop through a list of items returned from the database and make updates.

var qry = (from c in DBEntities.Customer select c);
foreach (Object item in qry)
{
  item.FirstName = .... 
  ... etc, other code here
  DBEntities.SaveChanges();
}

According to : http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/8a337036-d288-48d4-80d4-89e5a51eddd9?ppud=4 S Hargroves suggests converting to a IList and that is the solution.

Haven't tried that, I'm sure it will work, but even it works, I want to know why I can't update the item during the loop? This is occuring on my local development environment with no other users hitting the database.

Thanks ...

Answer 1

When you update the database using SaveChanges on a query, the query is invalidated. The result set may have been changed by the update you performed.

By using ToList, you are triggering the execution of the query and bringing all the results from the database into memory. Your in-memory list is now concrete and not concerned with being a query anymore.

Because Object Queries use IEnumerable, its not ok to do something that modifies the list in a foreach.

I also believe this code would fail for the same basic reasons:

List<int> numbers = new List<int>() { 1,2,3,4,5,6};
foreach(var num in numbers)
  numbers.Remove(num); //Invalidates the Enumerator being used in the foreach

Answer 2

I would agree with making the var a list. Then in your foreach,instead of using Object item in qry, use Customer customer in qry. In that scenario you are really working with customer objects not just objects. In most cases you wouldn't want to call SaveChanges() in a foreach because it is going to execute an update command on the server each time that is executed. If you do it after the foreach it will make one batch of calls to the database and perform a lot nicer.

My suggested pseudo code looks something like

var customers = (from c in DBEntities.Customer select c).ToList<Customer>();
foreach (Customer customer item in customers)
{
  customer.FirstName = .... 
  ... etc, other code here

}
DBEntities.SaveChanges();

Answer 3

Ok I have ran into the same issue. Right now I do not have a million records only about 20K, but the table I want to processes stores images so when processing the table to a List takes way too long, Even on a desktop app.

I have been using LinqToSql since it came out and it works fine in LinqToSql, so I was kinda pissed when I seen it's not working in Entity. And it's stupid Microsoft didn't make it work in Entity, but here is the work around. Make 2 Context objects. One for the list and one for the updates as follows.

entityList _imgList = new entityList();
entityList _imgSave = new entityList();

// Now the 1st time I did this I got the whole record like follows.

    var _imgList = _imgList.Images.where( i=> i.NotProcessed == false);
    foreach(Images _img_p in imgList)
    {
        if(something)
        {
            Images _img = _imgSave.Single(i=> i.ID == _img_p.ID);
            _img.NotProcessed == true;
            imgSave.SaveChanges();
        }
    }
imgList.dispose();
imgSave.dispose();


// After i verified this worked I figured why do I need to whole record to loop though so I changed it to just get the ID then process my loop as follows, and it works great.

    var _imgIds = _imgList.Images.where( i=> i.NotProcessed == false ).select(i=>i.ID);
    foreach(long _imgID in imgList)
    {
        Images _img = _imgSave.Single(i=> i.ID == _imgID);
        if(something)
        {
            _img.NotProcessed == true;
             imgSave.SaveChanges();
        }
    }

imgList.dispose();
imgSave.dispose();

You can see more at my Blog Post (ASP.Net Help Blog)