C# Entity Framework: Update Only First Batch Records and Stop

Question

Is there way with EF Core Bulk extensions to only update first few 10000 rows? write message, update next batch, write message in loop until complete?

I know there is Batch Size, however if there is only 5 million to update, and I want only update a certain amount, write a message, in continual loop until complete, how can this be done?

Should I use Top or Take?

I want to write "Hello", every few batches.

while {

await _dbContext.Set<Product>()
    .Where(x => x.Manufacturer == "ABC Company" &&
                x.StartYear == 2019 &&
                x.ProductType.ProductTypeDescription == "Electronics")
    .BatchUpdateAsync(x => new Product(){
                Manufacturer = "XYZ Company",
                StartYear = 2020 });

Console.WriteLine("hello"):

https://github.com/borisdj/EFCore.BulkExtensions

Using EF Core 3.1.

Company does not want to use SignalR

With sql like https://stackoverflow.com/questions/35903375/how-to-update-large-table-with-millions-of-rows-in-sql-server/35931214? BatchUpdateAsync returns the rowcount, so you could surround with `$"SET rowcount {n}"` raw sql. — Jeremy Lakeman, Aug 21 '20 at 00:18

Asherguru · Answer 1 · 2020-08-21T01:04:46.070

Try this.

if loop until first few 10,000

int iEnd = 5;

for (var i = 0; i <= iEnd; i++)
{
    var products = await _dbContext.Set<Product>().Where(x => x.Manufacturer == "ABC Company" &&
                    x.StartYear == 2019 &&
                    x.ProductType.ProductTypeDescription == "Electronics").Skip(i * 10000).Take(10000).ToList();

    products.ForEach(x =>
    {
         x.Manufacturer = "XYZ Company";
         x.StartYear = 2020;
    });
    _dbContext.Set<Product>().UpdateRange(products); // or you can use BulkUpdate here.
}

_dbContext.SaveChanges();

If loop until 5 millions.

int i = 0;
Boolean IsContinue = true;
while (IsContinue)
{
     var products = await _dbContext.Set<Product>().Where(x => x.Manufacturer == "ABC Company" &&
                x.StartYear == 2019 &&
                x.ProductType.ProductTypeDescription == "Electronics").Skip(i * 10000).Take(10000).ToList();

     if (products.Count() == 0)
          IsContinue = false;
     else
     {
          products.ForEach(x =>
          {
                x.Manufacturer = "XYZ Company";
                x.StartYear = 2020;
          });
          _dbContext.Set<Product>().UpdateRange(products); // or you can use BulkUpdate here.
      }
      i++;
}

_dbContext.SaveChanges();

score 0 · Accepted Answer · answered Aug 20 '20 at 23:52

0

It looks like BatchSize is only used for bulk insert. For update the expression is translated to a single SQL UPDATE statement, which doesn't operate by "batches".

answered Aug 20 '20 at 23:52

David Browne - Microsoft

80,331
6
39
67

hi David, I have a question here, thanks , currently using AzureSql https://dba.stackexchange.com/questions/274411/update-large-table-with-millions-of-rows-in-sql-server-in-parallel – Aug 26 '20 at 16:22

C# Entity Framework: Update Only First Batch Records and Stop

2 Answers2

if loop until first few 10,000

If loop until 5 millions.

Linked