.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

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 2

Thanks for the recommendation on SavingChanges after loop, I hadn't thought of that. However I've got to process over 1 Million records, this is not all that much, but there is some processing that will be performed during each loop. So I'm a little concerned that it will take quite a lot of time to loop the 1 Million records updating the information and then take a while to post the changes.

I also know that the EntityFramework probably isn't the best approach, but I'm interested in learning features and limitations of Entity Framework.

Also just to note the var list actually returns a ObjectQuery of Customer type. So the foreach loop can actually be written as the above using Customer variable data type even without converting to a list.

Again my question that I'd like to know is why can't I post a change in the same context object (DBEntities) during the loop?