EF Codefirst散装插入 [英] EF Codefirst Bulk Insert

问题描述

我的原始代码看起来像这样：

  foreach（listOfItemsToBeAdded中的var item）
 {
 // biz logic 
 context.MyStuff.Add（i）; 
} 
  

这花了很长时间。每个 DBSet.Add（）调用大约需要2.2秒，相当于大约90分钟。

I将代码重构为：

  var tempItemList = new List< MyStuff>（）; 
 foreach（listOfItemsToBeAdded中的var item）
 {
 // biz logic 
 tempItemList.Add（item）
} 
 context.MyStuff.ToList（） .AddRange（tempItemList）; 
  

这只需要4秒左右才能运行。但是， .ToList（）查询表中当前所有的项目，这是非常必要的，从长远来看可能是危险的，甚至更加耗时。一个解决方法是做一些类似 context.MyStuff.Where（x => x.ID = * empty guid *）的AddRange（tempItemList），因为那时我知道不会有任何回报。

但是，如果有其他人知道使用EF Code First的批量插入方法，我很好奇吗？

解决方案

验证通常是EF的一个非常昂贵的部分，通过禁用以下功能，性能得到了很好的提升：

  context.Configuration.AutoDetectChangesEnabled = false; 
 context.Configuration.ValidateOnSaveEnabled = false; 
  

我相信我发现在类似的SO问题 - 也许这是这个答案

另一个答案正确地指出，如果你真的需要批量插入性能，你应该使用 System.Data.SqlClient.SqlBulkCopy 。对于这个问题，EF和ADO.NET的选择真的在于您的优先级。

I need to insert around 2500 rows using EF Code First.

My original code looked something like this:

foreach(var item in listOfItemsToBeAdded)
{
    //biz logic
    context.MyStuff.Add(i);
}

This took a very long time. It was around 2.2 seconds for each DBSet.Add() call, which equates to around 90 minutes.

I refactored the code to this:

var tempItemList = new List<MyStuff>();
foreach(var item in listOfItemsToBeAdded)
{
    //biz logic
    tempItemList.Add(item)
}
context.MyStuff.ToList().AddRange(tempItemList);

This only takes around 4 seconds to run. However, the .ToList() queries all the items currently in the table, which is extremely necessary and could be dangerous or even more time consuming in the long run. One workaround would be to do something like context.MyStuff.Where(x=>x.ID = *empty guid*).AddRange(tempItemList) because then I know there will never be anything returned.

But I'm curious if anyone else knows of an efficient way to to a bulk insert using EF Code First?

解决方案

Validation is normally a very expensive portion of EF, I had great performance improvements by disabling it with:

context.Configuration.AutoDetectChangesEnabled = false;
context.Configuration.ValidateOnSaveEnabled = false;

I believe I found that in a similar SO question--perhaps it was this answer

Another answer on that question rightly points out that if you really need bulk insert performance you should look at using System.Data.SqlClient.SqlBulkCopy. The choice between EF and ADO.NET for this issue really revolves around your priorities.

这篇关于EF Codefirst散装插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！