EF - Update multiple rows in database without using foreach loop

Question

My test code:

using (var db = new MyDbContext())
{
  string fromUser = ""; //sender
  string toUser = ""; //receiver

  var messages = db.Message.Where(x => x.FromUser == fromUser && x.ToUser == toUser)
                 .ToList();

  foreach (var message in messages)
  {
    message.IsRead = true;
    db.SaveChanges();
  }
}

My question: is there another way to update database without using foreach loop in that case?

p/s: I'd been reference these questions but all of them didn't solve my problem:

Move the `SaveChanges()` outside of the `for` loop – David Tansey Dec 05 '15 at 16:43 — David Tansey, Dec 05 '15 at 16:43

score 27 · Accepted Answer · answered Dec 05 '15 at 16:43

27

You don't need to SaveChanges() on each iteration. That will hit the database multiple times. Try:

using (var db = new MyDbContext())
{
  string fromUser = ""; //sender
  string toUser = ""; //receiver
  var messages = db.Message.Where(x => x.FromUser == fromUser && x.ToUser == toUser)
                 .ToList();
  messages.ForEach(m => m.IsRead = true);
  db.SaveChanges();
}

answered Dec 05 '15 at 16:43

Steve Greene

12,029
1
33
54

7

Creating a list and lambda just to call a method which internally is a loop. Crazy people. – Ivan Stoev Dec 05 '15 at 16:58
Well, the bigger point is certainly the single database call. Some would argue the lambda looks cleaner (it's a resharper recommended refactoring). Crazy is a little harsh IMO. – Steve Greene Dec 05 '15 at 17:05
@SteveGreene The comment was not for your or your solution, I understand that was the OP request. Indeed a single database call is the point, but not from the post title, which moves the focus on the unimportant thing. `List.ForEach` is a mistake - http://blogs.msdn.com/b/ericlippert/archive/2009/05/18/foreach-vs-foreach.aspx – Ivan Stoev Dec 05 '15 at 17:29
Definitely debatable. http://geekswithblogs.net/BlackRabbitCoder/archive/2010/04/23/c-linq-vs-foreach---round-1.aspx – Steve Greene Dec 05 '15 at 17:51
Simple and Direct approached and saved alot of process time. Thanks – Rey Norbert Besmonte Dec 07 '16 at 12:50

score 8 · Answer 2 · answered Apr 17 '20 at 19:29

Just to update Steve's answer, in newer EF (current EF Core 3.1.3) you can do it all as one statement.

await db.Message.Where(m => m.FromUser == fromUser && m.ToUser == toUser).ForEachAsync(m => m.IsRead = true);
await db.SaveChangesAsync().ConfigureAwait(false);

EF - Update multiple rows in database without using foreach loop

2 Answers2

Linked