Entity Framework: Bulk UpdateFromQuery in Batches

Question

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

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?

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

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" });

Goal Code:

How to update large table with millions of rows in 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;

Note: Currently refraining from using RawSql unless required

Answer 1

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

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.

---

EDIT: Answer Update

Since the v3.0.61, the BatchSize option is now supported for SQL Server.

You can now specify a BatchSize when using UpdateFromQuery