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

Changteh

Take a look at this as well https://mcmap.net/q/424923/-how-to-bulk-update-records-in-entity-framework – Gavriella

hi @SalahAkbari yeah, we're aware of rawsql and fromsql, just want to see notes using EF Extensions and Update, thanks – Changteh

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

Facies

hi, do you know the estimate feature release date? 6-12 months? thanks for all your help ! – Changteh

cc @JonathonMagnan also, can you think of any workaround syntax? similar to question reference, thanks – Changteh

@Artportraitdesign1, the delay will depends on my developer. He should try it this week, so if everything go well, a new version will be available next week, I cannot make any promise yet. There is currently no workaround syntax to get this desired behavior. – Facies