C# Entity Framework: Bulk Extensions Input Memory Issue

Question

I am currently using EF Extensions. One thing I don't understand, "its supposed to help with performance"

however placing a million+ records into List variable, is a Memory Issue itself. So If wanting to update million records, without holding everything in memory, how can this be done efficiently?

Should we use a for loop, and update in batches say 10,000? Does EFExtensions BulkUpdate have any native functionality to support this?

Example:

var productUpdate = _dbContext.Set<Product>()
    .Where(x => x.ProductType == 'Electronics');  // this creates IQueryable

await productUpdate.ForEachAsync(c => c.ProductBrand = 'ABC Company');

_dbContext.BulkUpdateAsync(productUpdate.ToList());

Resource:

https://entityframework-extensions.net/bulk-update

Answer 1

I found the "proper" EF Extensions way to do a bulk update with a query-like condition:

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

This should result in a proper SQL UPDATE ... SET ... WHERE, without the need to load entities first, as per the documentation:

Why UpdateFromQuery is faster than SaveChanges, BulkSaveChanges, and BulkUpdate?

UpdateFromQuery executes a statement directly in SQL such as UPDATE [TableName] SET [SetColumnsAndValues] WHERE [Key].

Other operations normally require one or multiple database round-trips which makes the performance slower.

You can check the working syntax on this dotnet fiddle example, adapted from their example of BulkUpdate.

Other considerations

• No mention of batch operations for this, unfortunately.

• Before doing a big update like this, it might be worth considering deactivating indexes you may have on this column, and rebuild them afterward. This is especially useful if you have many of them.

• Careful about the condition in the Where, if it can't be translated as SQL by EF, then it will be done client side, meaning the "usual" terrible roundtrip "Load - change in memory - update"