2

With EF Core: Is there a way to tell it that the new value in the single field I update is the same for all entities and make it update the entities with one/few SQL statements?

I want to set a timestamp on a lot of records at the same time. I currently have this code:

var downloadTimeStamp = DateTime.UtcNow;

foreach (var e in entities)
{
    e.DownloadDateTime = downloadTimeStamp;
}
await db.SaveChangesAsync();

It generates SQL that executes one UPDATE statement for each entity. E.g.:

UPDATE [TableName] SET [DownloadDateTime] = @p1988
  WHERE [Id] = @p1989;
  SELECT @@ROWCOUNT;

Is that really the most optimal way to do this? Is it possible to make EF Core do this in fewer SQL statements?

In my current dataset there are 100,000 records that need to be updated. It is not the full contents of the table, only a subset. It takes about 90 seconds to do the above updates when running the code inside Visual Studio with a SQL server express on my local PC. When running in a small Azure web app with a small SQL server Azure instance, it takes several minutes. That's why I'm looking to optimize it, if possible.

RasmusW
  • 3,355
  • 3
  • 28
  • 46
  • 2
    Are you looking for Bulk Update records in Entity Framework? If so check this https://stackoverflow.com/a/44194925/2946329 – Salah Akbari Dec 19 '17 at 16:03
  • Similar: https://stackoverflow.com/questions/34107936/ef-update-multiple-rows-in-database-without-using-foreach-loop/34108011#34108011 – Steve Greene Dec 19 '17 at 16:11
  • @S.Akbari: I don't see how ``ExecuteSqlCommand`` would help. Unless you mean I should create the SQL statement myself? – RasmusW Dec 19 '17 at 19:36
  • @SteveGreene: The ``entities.ForEach(e => e.DownloadDateTime = downloadTimeStamp)`` followed by ``db.SaveChangesAsync()`` seems to do the same as my existing code: It generates and runs the above SQL statement once for each record I want to update. – RasmusW Dec 19 '17 at 19:36
  • 1
    See [this](https://stackoverflow.com/questions/29551323/how-do-i-update-multiple-entity-models-in-one-sql-statement)? – Steve Greene Dec 19 '17 at 20:06
  • Thank you @SteveGreene. That pointed me in the direction of http://entityframework-plus.net/batch-update which can do it very quickly compared to vanilla EF. – RasmusW Dec 19 '17 at 21:26
  • Except this is not available for commercial use, you will have to purchase. – Jackal May 08 '19 at 21:55
  • No, the batch update is part of the EF Plus package. That is MIT licensed (https://github.com/zzzprojects/EntityFramework-Plus/blob/master/LICENSE). There is a link to a commercial product which has a feature they call "bulk updates". I haven't checked out exactly what the difference is, but it wasn't necessary for my use case. – RasmusW May 09 '19 at 03:16
  • Does this answer your question? [How to Bulk Update records in Entity Framework?](https://stackoverflow.com/questions/44194877/how-to-bulk-update-records-in-entity-framework) –  Aug 19 '20 at 01:08
  • I'm not sure. The problem I had was the generated SQL, not change tracker performance. That doesn't seem to be discussed in the other question. – RasmusW Aug 19 '20 at 07:35

0 Answers0