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
– David Tansey

2015-12-05 16:43:09 +00:00
Commented Dec 5, 2015 at 16:43 — David Tansey
– David Tansey, Commented Dec 5, 2015 at 16:43

Steve Greene · Accepted Answer · 2015-12-05 16:43:01Z

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 5, 2015 at 16:43

Steve Greene

12.4k2 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ivan Stoev Over a year ago

Creating a list and lambda just to call a method which internally is a loop. Crazy people.

Steve Greene Over a year ago

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.

Ivan Stoev Over a year ago

@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<T>.ForEach is a mistake - blogs.msdn.com/b/ericlippert/archive/2009/05/18/…

Steve Greene Over a year ago

Definitely debatable. geekswithblogs.net/BlackRabbitCoder/archive/2010/04/23/…

Rey Norbert Besmonte Over a year ago

Simple and Direct approached and saved alot of process time. Thanks

Scott R. Frost · Accepted Answer · 2020-04-17 19:29:36Z

8

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);

answered Apr 17, 2020 at 19:29

Scott R. Frost

2,0611 gold badge22 silver badges25 bronze badges

Collectives™ on Stack Overflow

EF - Update multiple rows in database without using foreach loop

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related