CODEWITHSUNDEEP
c#sqlasp.net-mvcentity-frameworksql-update
allencoded
allencoded

Reputation: 7285

Update multiple rows in Entity Framework from a list of ids

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?

Upvotes: 140

Views: 232070

Answers (6)

Ahmad Tadi
Ahmad Tadi

Reputation: 54

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

Upvotes: -1

Santiago Semhan
Santiago Semhan

Reputation: 452

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)

Upvotes: 23

zack yang
zack yang

Reputation: 99

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

Upvotes: 3

Damith
Damith

Reputation: 63105

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

Upvotes: 219

chrisg
chrisg

Reputation: 1117

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:

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

Upvotes: 5

Raphael Pinel
Raphael Pinel

Reputation: 2830

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.

Upvotes: 4

Related Questions