实体框架:成批批量更新UpdateFromQuery [英] Entity Framework: Bulk UpdateFromQuery in Batches

问题描述

我想对一百万+行执行批量更新.

I want to perform a bulk update for a Million+ rows.

但是，我不想更新整个表，而是要以较小的批次进行更新(以防止锁定整个表).每10,000行说一次.

However, I don't want to update the whole table, but rather in smaller batches (to prevent locking the whole table). Say every 10,000 rows.

例如，类似于以下答案:

Eg, similar to this answer: How to update large table with millions of rows in SQL Server?

当前使用UpdateFromQuery来不加载整个上下文，而是直接更新数据库.

Currently using UpdateFromQuery to not load the whole context, and directly update the database.

现在如何批量更新?我应该使用.Take函数吗?

Now how can I update in batches? Should I use the .Take Function?

var productUpdate = _dbContext.Set<Product>()
    .Where(x => x.ProductType == 'Electronics')
    .UpdateFromQuery( x => new Product { ProductBrand = "ABC Company" });

目标代码:

如何更新在SQL Server中有数百万行的大型表?

SET @BatchSize = 10000;

SET @Rows = @BatchSize; -- initialize just to enter the loop

BEGIN TRY    
  WHILE (@Rows = @BatchSize)
  BEGIN
      UPDATE TOP (@BatchSize) prod
      SET Value = 'ABC Company'
      FROM dbo.Products prod
      WHERE prod.ProductType = 'Electronics'
   SET @Rows = @@ROWCOUNT;
 END;

注意:当前除非必要，否则不使用RawSql

Note: Currently refraining from using RawSql unless required

推荐答案

免责声明:我是该项目的所有者实体框架增强版

Disclaimer: I'm the owner of the project Entity Framework Plus

目前无法使用BatchSize进行更新.

That is currently impossible to update using a BatchSize.

但是，我们将对其进行研究并提供此功能.

However, we will look at it and provide this feature.

功能发布后，我将更新此答案.

I will update this answer once the feature is released.

这篇关于实体框架:成批批量更新UpdateFromQuery的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！