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?

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

UPDATE:

you can update multiple fields as below

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

Solution 2

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.

Solution 3

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

Sample code 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: https://github.com/yangzhongke/Zack.EFCore.Batch Report: https://www.reddit.com/r/dotnetcore/comments/k1esra/how_to_batch_delete_or_update_in_entity_framework/

Solution 4

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

View more solutions

Comments

[z atef]

What is your database platform Oracle mysql ..

[allencoded]

My database is Microsoft SQL

[Peter Kerr]

There are two open source projects allowing this: EntityFramework.Extended and Entity Framework Extensions.

[Gert Arnold]

The only correct answer to this is: you can't. Sure, you can pull all matching Friends from the database and update their property msgSentBy and save changes. But EF will fire UPDATE statements for each individual record. That's not at all the same as a one-statement bulk update. As said, look for a third-party library that offers bulk update.

[Peter Kerr]

@SamuelLiew why move my answer to a comment, it is an answer and a useful one at that?

[allencoded]

can i update more than one field in the foreach above you did just sent by which is exactly what i asked for. just curious if you could do more? Also doesn't look like SubmitChanges quite works anymore. I am using the latest entity framework. Maybe SaveChanges()?

[BlueRaja - Danny Pflughoeft]

ForEach is a method on List, and it's generally discouraged to use because it's not a very functional-way of programming. Just use foreach (the operator).

[RamNow]

Using this solution generates one update query for each element in the list. Is there a way to get EF to do just one query like in the question? (UPDATE SomeTable SET SomeField = SomeValue WHERE Id IN (...))

[d512]

Be aware that this is a pretty inefficient way to go about this from a database perspective. Not only does this issue a big select statement involving every row from the Friends table, but it issues a separate UPDATE command for every record that is updated. So rather than issuing one command you are issues potentially many many commands as well as streaming a bunch of data out of your database.

[Shekhar Pankaj]

@user1334007 any solution to avoid this?

[d512]

@ShekharPankaj, basically what you want to do is issue a SQL command like "UPDATE Friends SET msgSentBy = '1234' WHERE ID IN (1, 2, 3, 4)". I don't think that EF has direct support for doing that. I believe there are some 3rd party solutions to this (stackoverflow.com/questions/12751258/batch-update-delete-ef‌​5) but I haven't used them. The other options is to use raw ADO.NET instead of EF.

[Mansur]

This is also getting the data first from the DB. Is there anything else that we can use without getting the data first?

[Gert Arnold]

I don't see how this adds anything to the existing answer. It basically just repeats it.

[Raphael Pinel]

the main difference is to use foreach instead of friends.ForEach

[kiml42]

The problem with this answer is it will read all the friends out of the DB into memory, alter them and then save them again, rather than executing a single update query in SQL to the DB.

[barnacle.m]

This should NOT be the accepted answer, it's pretty inefficient and will cause the db to run out of memory pretty fast,

[Gert Arnold]

foreach instead of friends.ForEach That's totally irrelevant.