0

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

  • Take a look at this as well https://stackoverflow.com/a/44194925/2946329 – Salah Akbari Aug 18 '20 at 05:09
  • hi @SalahAkbari yeah, we're aware of rawsql and fromsql, just want to see notes using EF Extensions and Update, thanks –  Aug 18 '20 at 05:12

1 Answers1

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

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
  • hi, do you know the estimate feature release date? 6-12 months? thanks for all your help ! –  Aug 18 '20 at 16:25
  • cc @JonathonMagnan also, can you think of any workaround syntax? similar to question reference, thanks –  Aug 18 '20 at 22:29
  • @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. – Jonathan Magnan Aug 19 '20 at 12:29