How to update list without using for loop in Entity Framework?

Question

Original Oracle SQL statement:

UPDATE Table1 
SET col1 = '1', col2 = '2' 
WHERE col3 = '3';

Entity Framework:

var updateData = DbContext.Table1.Where(x => x.col3 == "3");

foreach (var item in updateData)
{
    item.col1 = "1";
    item.col2 = "2";
}

DbContext.SaveChanges();

But I found that it would take a lot time if there are many records in updateData.

I tried output the SQL as below and found that entity framework will update each data in updateData, if there are 100 records in updateData, then the script as below will show up 100 times and it will need take 175 ms * 100 to update data.

update "Table1"
set "col1" = :p0,"col2" = :p1
where (((("Col3" = :p2)) and ("Col4" = :p3))
-- :p0: '1' 
-- :p1: '2' 
-- :p2: '3' 
-- :p3: 'otherData..'
-- Executing at 2021/4/14 08:20:07 +08:00
-- Completed in 175 ms with result: 1

Is there any better way to update list?

Does this answer your question? https://stackoverflow.com/questions/44194877/how-to-bulk-update-records-in-entity-framework — Alexey Rumyantsev, Apr 14 '21 at 12:41

score 0 · Answer 1 · answered Apr 14 '21 at 12:42

It is not possible with EF, only by pure SQL or Stored Procedure. But there are third party extensions which can do that for you. For example https://github.com/linq2db/linq2db.EntityFrameworkCore (disclaimer, I'm one of the creators)

Then you can run the following:

DbContext.Table1.Where(x => x.col3 == "3")
  .Set(x => x.col1, "1")
  .Set(x => x.col2, "2")
  .Update();

How to update list without using for loop in Entity Framework?

1 Answers1