.NET EntityFramework：“在提供程序连接上启动事务时出错。有关详细信息，请参阅内部异常”

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

提问by CWinKY

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

在 .NET 中使用实体框架我想遍历从数据库返回的项目列表并进行更新。

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=4S Hargroves suggests converting to a IList and that is the solution.

根据：http: //social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/8a337036-d288-48d4-80d4-89e5a51eddd9?ppud =4S Hargroves 建议转换为 IList，这就是解决方案。

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

谢谢 ...

回答by Tilendor

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.

当您对查询使用 SaveChanges 更新数据库时，该查询将失效。结果集可能已被您执行的更新更改。

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.

通过使用ToList，您将触发查询的执行并将数据库中的所有结果放入内存中。你的内存列表现在是具体的，不再关心是一个查询。

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

因为对象查询使用IEnumerable，所以不能在 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

回答by awright18

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.

我同意将 var 列为列表。然后在你的 foreach 中，而不是在 qry 中使用 Object 项，在 qry 中使用 Customer 客户。在这种情况下，您实际上是在处理客户对象，而不仅仅是对象。在大多数情况下，您不希望在 foreach 中调用 SaveChanges()，因为每次执行时它都会在服务器上执行更新命令。如果您在 foreach 之后执行它，它将对数据库进行一批调用并执行得更好。

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();

回答by Ron

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.

好的，我遇到了同样的问题。现在我没有一百万条记录，只有大约 20K，但是我要处理的表存储图像，因此在将表处理为 List 时花费的时间太长，即使在桌面应用程序上也是如此。

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.

自从 LinqToSql 出现以来，我一直在使用它，它在 LinqToSql 中运行良好，所以当我看到它在 Entity 中不起作用时，我有点生气。微软没有让它在实体中工作是愚蠢的，但这是解决方法。制作 2 个上下文对象。一份用于列表，一份用于更新，如下所示。

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)

您可以在我的博客文章（ASP.Net 帮助博客）中查看更多信息

回答by CWinKY

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.

感谢您在循环后对 SavingChanges 的建议，我没有想到这一点。然而，我必须处理超过 100 万条记录，这并不是那么多，但是在每个循环中都会执行一些处理。所以我有点担心循环更新信息的 100 万条记录会花费相当多的时间，然后需要一段时间来发布更改。

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

我也知道 EntityFramework 可能不是最好的方法，但我对学习 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.

还要注意的是，var 列表实际上返回的是 Customer 类型的 ObjectQuery。因此，即使不转换为列表，foreach 循环实际上也可以使用 Customer 变量数据类型编写为上述内容。

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?

同样，我想知道的问题是为什么我不能在循环期间在同一个上下文对象 (DBEntities) 中发布更改？