实体框架4 SaveChanges的内存不足 [英] Entity Framework 4 Out of Memory on SaveChanges

问题描述

我有一张包含超过五十万条记录的表格。每个记录包含大约60个字段，但我们只对其中的三个字段进行更改。

我们根据计算和查找对每个实体进行一个小的修改。 p>

显然，我无法依次更新每个实体，然后再次 SaveChanges ，因为这将需要太长时间。 >

所以在整个过程结束时，我在上下文 SaveChanges c>。

当我应用 SaveChanges

$ b $时，会导致内存不足错误b

我正在使用DataRepository模式。

  //更新代码
 DataRepository< ExportOrderSKUData> repoExportOrders = new DataRepository< ExportOrderSKUData>（）; 
 foreach（ExportOrderSKUData grpDCItem in repoExportOrders.all（））
 {
 ..make更改为enity .. 
} 
 repoExportOrders.SaveChanges（）; 
 
 
 
 //数据库snip 
 public DataRepository（）
 {
 _context = new tomEntities（）; 
 _objectSet = _context.CreateObjectSet< T>（）; 
} 
 public List< T> All（）
 {
 return _objectSet.ToList< T>（）; 
} 
 public void SaveChanges（）
 {
 _context.SaveChanges（）; 
} 
  

在这种情况下我应该寻找什么？

解决方案

在一个交易中通过EF更改50万条记录是不应该使用的情况。小批量生产是一个更好的技术解决方案。通过一些存储过程在数据库端执行它可以是更好的解决方案。

我将从稍微修改代码开始（自己将其转换为您的存储库API）：使用（var readContext = new YourContext（））{
var set = readContext.CreateObjectSet< ExportOrderSKUData>（）;

  
 
 foreach（var item in set.ToList（））{
 readContext.Detach（item）; 
 using（var updateContext = new YourContext（））{
 updateContext.Attach（item）; 
 //使您的更改
 updateContext.SaveChanges（）; 
} 
} 
} 
  

此代码使用单独的上下文保存项目=每个保存在自己的事务中。不要害怕即使您尝试在 SaveChanges 的一次呼叫中保存更多记录，EF将为每个更新的记录使用单独的往返数据库。唯一的区别是，如果你想在同一个交易中有多个更新（但是在单个交易中有50万个更新会导致问题）。

另一个选项可能是使用（var readContext = new YourContext（））{
var set = readContext.CreateObjectSet< ExportOrderSKUData>（ ）;
set.MergeOption = MergeOption.NoTracking;

foreach（var item in set）{
using（var updateContext = new YourContext（））{
updateContext.Attach（item）;
//使您的更改
updateContext.SaveChanges（）;
}
}
}

这在理论上可以消耗更少的内存，因为您不需要在执行 foreach 之前加载所有实体。第一个例子可能需要在枚举之前加载所有实体（通过调用 ToList ），以避免在调用 Detach （在枚举期间修改收藏） - 但是我不确定是否真的发生了。

修改这些示例以使用一些批次应该很容易。

I have a table that contains greater than half a million records. Each record contains about 60 fields but we only make changes to three of them.

We make a small modification to each entity based on a calculation and a look up.

Clearly I can't update each entity in turn and then SaveChanges as that would take far too long.

So at the end of the whole process I call SaveChanges on the Context.

This is causing an Out of Memory error when i apply SaveChanges

I'm using the DataRepository pattern.

//Update code
DataRepository<ExportOrderSKUData> repoExportOrders = new DataRepository<ExportOrderSKUData>();
foreach (ExportOrderSKUData grpDCItem in repoExportOrders.all())
{
  ..make changes to enity..
}
repoExportOrders.SaveChanges();



//Data repository snip
public DataRepository()
{
  _context = new tomEntities();
  _objectSet = _context.CreateObjectSet<T>();
}
public List<T> All()
{
  return _objectSet.ToList<T>();
}
public void SaveChanges()
{
  _context.SaveChanges();
}

What should I be looking for in this instance?

解决方案

Making changes to half a million record through EF within one transaction is not supposed use case. Doing it in small batches is a better technical solution. Doing it on database side through some stored procedure can be even better solution.

I would start by slightly modifying your code (translate it to your repository API yourselves):

using (var readContext = new YourContext()) {
    var set = readContext.CreateObjectSet<ExportOrderSKUData>();

    foreach (var item in set.ToList()) {
       readContext.Detach(item);
       using (var updateContext = new YourContext()) {
          updateContext.Attach(item);
          // make your changes
          updateContext.SaveChanges();
       }
    }
}

This code uses separate context for saving item = each save is in its own transaction. Don't be afraid of that. Even if you try to save more records within one call of SaveChanges EF will use separate roundtrip to database for every updated record. The only difference is if you want to have multiple updates in the same transaction (but having half a million updates in single transaction will cause issues anyway).

Another option may be:

using (var readContext = new YourContext()) {
    var set = readContext.CreateObjectSet<ExportOrderSKUData>();
    set.MergeOption = MergeOption.NoTracking;

    foreach (var item in set) {
       using (var updateContext = new YourContext()) {
          updateContext.Attach(item);
          // make your changes
          updateContext.SaveChanges();
       }
    }
}

This can in theory consume even less memory because you don't need to have all entities loaded prior to doing foreach. The first example probably needs to load all entities prior to enumeration (by calling ToList) to avoid exception when calling Detach (modifying collection during enumeration) - but I'm not sure if that really happens.

Modifying those examples to use some batches should be easy.

这篇关于实体框架4 SaveChanges的内存不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！