Update multiple rows in Entity Framework from a list of ids

Question

I am trying to create a query for Entity Framework that will allow me to take a list of ids and update a field associated with them.

Example in SQL:

UPDATE Friends
SET msgSentBy = '1234'
WHERE id IN (1, 2, 3, 4)

How do I convert the above into Entity Framework?

Answer 1

Something like below:

var idList = new int[]{1, 2, 3, 4};
using (var db = new SomeDatabaseContext())
{
    var friends = db.Friends.Where(f => idList.Contains(f.ID)).ToList();
    friends.ForEach(a => a.msgSentBy='1234');
    db.SaveChanges();
}

You can update multiple fields as below:

friends.ForEach(a =>
                      {
                         a.property1 = value1;
                         a.property2 = value2;
                      });

Answer 2

The best way to do a massive update with Entity Framework 7 is like this:

var idList = new int[]{1, 2, 3, 4};
context.Friends
       .Where(f => idList.Contains(f.ID))
       .ExecuteUpdate(f => f.SetProperty(x => x.Name, x => $"Updated {x.Name}"));

The advantage of this is that it doesn't retrieve all the records. It just sends an update query.

Reference: ExecuteUpdate and ExecuteDelete (Bulk updates)

Answer 3

The IQueryable.ToQueryString method introduced in Entity Framework Core 5.0 may help with this scenario, if you are willing to have some raw SQL appearing in your code. This method will generate SQL that can be included in a raw SQL query to perform a bulk update of records identified by that query.

For example:

using var context = new DbContext();

var ids = new List<int>() { 1, 2, 3, 4 };

var query = context.Friends.Where(_ => ids.Contains(_.id)).Select(_ => _.id);

var sql = $"UPDATE Friends SET msgSentBy = {{0}} WHERE id IN ({query.ToQueryString()})";

context.Database.ExecuteSqlRaw(sql, "1234");

The major drawback of this approach is the use of raw SQL. However I don't know of any reasonable way to avoid that with current Entity Framework Core capabilities - you're stuck with this caveat, or the caveats of other answers posted here such as:

• Introducing a dependency on another library like https://github.com/yangzhongke/Zack.EFCore.Batch.
• Using DbContext.SaveChanges() which will update records one at a time rather than doing a bulk update.

If (when) the following issue is addressed in the future then we are likely to get a better answer here: Bulk (i.e. set-based) CUD operations (without loading data into memory) #795

Answer 4

var idList=new int[]{1, 2, 3, 4};
var friendsToUpdate = await Context.Friends.Where(f => 
    idList.Contains(f.Id).ToListAsync();

foreach(var item in previousEReceipts)
{
  item.msgSentBy = "1234";
}

You can use foreach to update each element that meets your condition.

Here is an example in a more generic way:

var itemsToUpdate = await Context.friends.Where(f => f.Id == <someCondition>).ToListAsync();

foreach(var item in itemsToUpdate)
{
   item.property = updatedValue;
}
Context.SaveChanges()

In general you will most probably use async methods with await for db queries.

Answer 5

I have created a library to batch delete or update records with a round trip on EF Core 5.

Sample code is as follows:

await ctx.DeleteRangeAsync(b => b.Price > n || b.AuthorName == "zack yang");

await ctx.BatchUpdate()
.Set(b => b.Price, b => b.Price + 3)
.Set(b=>b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper())
.Set(b => b.PubTime, b => DateTime.Now)
.Where(b => b.Id > n || b.AuthorName.StartsWith("Zack"))
.ExecuteAsync();

GitHub repository: Zack.EFCore.Batch Report: How to batch delete or update in Entity Framework Core 5

Answer 6

For those who are searching for a bulk update (like me):

Based on Microsoft EF documents, in the Performance section (Efficient Update), if you want to do bulk update, you better use executing raw SQL to improve the performance.

context.Database.ExecuteSqlRaw("UPDATE [Employees] SET [Salary] = [Salary] + 1000");

Efficient Updating