What's the best way to update ~500 entities with EF Core? [closed]

Question

Right now I loop through each entity and call ExecuteUpdateAsync then after the loop I call CommitAsync, but I'd like to know if there is a better way for updating ~500 records?

I see this bulk extensions library, but never used it and don't know if it would help?

Additionally, this code is running in a Azure Function in the Cloud, so it isn't really that time dependent. I don't care if it takes 10 seconds or 1 min to run, just as long as it doesn't bog down the DB for other users using a website that shares the DB.

I'm using EF Core with a Code First approach, so I'd like to stay within the scope of EF Core.

Lastly, my data isn't coming from my DB, it's coming from a web service that's being called to fetch the data.

// About 500 transfers, data doesn't come from DB
var transfers = new List<(string, decimal?, long, string, string)>();

using var transaction = await _dbContext.Database.BeginTransactionAsync();

try
{
    foreach (var transfer in transfers)
    {
        var stripeTransfersResult = await _dbContext.StripeTransfers
          .Where(p => p.TransferId == transfer.Item1)
          .ExecuteUpdateAsync(setters => setters
            .SetProperty(p => p.ExchangeRate, transfer.Item2)
            .SetProperty(p => p.ExchangeAmount, transfer.Item3)
            .SetProperty(p => p.ExchangeCurrency, transfer.Item4)
            .SetProperty(p => p.StripePayoutId, transfer.Item5)
          );
    }

    await transaction.CommitAsync(cancellationToken);
}
catch (DbException ex)
{
    // handle exception
}

Answer 1

ExecuteUpdate is not best fit for your need. ExecuteUpdate is useful when having a big data set where for example column(s) need to be set to same value or incremented with some number.

When need to update each row with specific values regular approach would be to load all those into memory, make the changes, and then call SaveChanges. Example:

transfers = transfers.OrderBy(a => a.Item1);
itemIds = transfers.Select(a => a.Item1).ToList();
var entities = await _dbContext.StripeTransfers.Where(a => itemIds.Contains(a.TransferId)).OrderBy(a => a.TransferId).ToListAsync();//SQL IN
foreach (int i = 0; i < entities.Count(); i++)
{
  entity = entities[i];
  transfer = transfers[i];
  entity.ExchangeRate = transfer.Item2;
  entity.ExchangeAmount = transfer.Item3;
  entity.ExchangeCurrency = transfer.Item4;
  entity.StripePayoutId = transfer.Item5;
}
await _dbContext.SaveChangesAsync();

One more way could be using Attach like:

var m = new myModel { PrimaryKey = 1, OtherProperty = 5 };
ctx.Attach(m);
ctx.entry(m).Property(nameof(m.OtherProperty)).IsModified = true;
ctx.SaveChanges();

But those can be sometimes slow for lot of data and to speed the operation up you could use BulkExtensions library the following way:

var entities = new List<StripeTransfer>();
foreach (var transfer in transfers)
{
  var entity = new StripeTransfer();
  entity.TransferId= transfer.Item1;
  entity.ExchangeRate = transfer.Item2;
  entity.ExchangeAmount = transfer.Item3;
  entity.ExchangeCurrency = transfer.Item4;
  entity.StripePayoutId = transfer.Item5;
  entities.Add(entity);
}
var bulkConfig = new BulkConfig { 
  UpdateByProperties = new List<string> { nameof(Item.TransferId) }, 
  PropertiesToInclude= new List<string> { nameof(Item.ExchangeRate), nameof(Item.ExchangeAmount), nameof(Item.ExchangeCurrency), nameof(Item.StripePayoutId) },
};
await _dbContext.BulkUpdateAsync(entities, bulkConfig);

PS I'm the author of the lib.

Answer 2

As far as I can tell, you should, in fact, use the ExecuteUpdateAsync and SetProperty pattern for batch updating, but I believe that this is actually meant for batch updates where we set the same value for all of the items included in the batch.

The ExecuteUpdateAsync & SetProperty method generates the following SQL Query:

UPDATE [StripeTransfers] SET (ExchangeRate, ExchangeAmount...) = (Value1, Value2...);

In your case, this won't really work exactly as intended. As far as I can tell, by wrapping your foreach method inside a transaction, you should be able to take advantage of the fact that you'll save yourself the overhead of the first round-trip to the database, storing the object in memory, and updating the ef change tracker, but you'll still be executing an update statement for each of your objects. Still, this seems like the most optimized solution.

You'll also find more information regarding EF's batching behavior in Microsoft's documentation here.