Updating multiple rows at once with Entity Framework

Question

I am trying to figure out if there is a way to update multiple rows at once with Entity.

For now I am using Linq2Sql SubmitChanges which will perform an update row by row:

c.PROPERTYONE = valueOne;
c.PROPERTYTWO = valueTwo;
...
dataContext.SubmitChanges();

In my case, valueOne and valueTwo will most likely change for each iteration.

I would like to know if it's possible to store for example, up to 1000 rows and update them at once.

I know how to do it with inserts (by getting the DbContext instance, the appropriate table, calling the Add function then SaveChanges) but I'm struggling in finding the equivalent for updates.

Thanks in advance!

What about Bulk Update? See this stackoverflow.com/a/44194925/2946329 — Salah Akbari
– Salah Akbari, Commented Nov 2, 2017 at 10:03

Koderzzzz · Accepted Answer · 2017-11-02 10:04:24Z

8

You should use the following concept to update multiple rows.

using (var db = new MyDbContext())
{
var myData=db.YourTableObject.Where(x=>x.Someproperty=="Something").ToList();
myData.ForEach(m => m.YouPropertye= "Set Your Value",m.SomeOther="Set Other");
db.SaveChanges()

}

answered Nov 2, 2017 at 10:04

Koderzzzz

8698 silver badges19 bronze badges

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

1 Comment

tedebus Over a year ago

The problem with this solution is that the records are first read and loaded into memory, then they are updated. It would need a way to update them without having to dump them.

duongthaiha · Accepted Answer · 2017-11-02 10:05:30Z

In Entity Framework, you can use transaction. So you can group all changes in one transaction. When you commit a transaction then all changes go together/ can be rollback together. I stole the example from here https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113).aspx

  using (var dbContextTransaction = context.Database.BeginTransaction()) 
                { 
                    try 
                    { 
                        context.Database.ExecuteSqlCommand( 
                            @"UPDATE Blogs SET Rating = 5" + 
                                " WHERE Name LIKE '%Entity Framework%'" 
                            ); 

                        var query = context.Posts.Where(p => p.Blog.Rating >= 5); 
                        foreach (var post in query) 
                        { 
                            post.Title += "[Cool Blog]"; 
                        } 

                        context.SaveChanges(); 

                        dbContextTransaction.Commit(); 
                    } 
                    catch (Exception) 
                    { 
                        dbContextTransaction.Rollback(); 
                    } 
                }

herve · Accepted Answer · 2023-12-20 09:27:48Z

0

Answer from 2023, we can now use ExecuteUpdate() and ExecuteDelete(), it requires EF Core 7.0. You don't need loop or any previous read.

https://learn.microsoft.com/en-us/ef/core/saving/execute-insert-update-delete

answered Dec 20, 2023 at 9:27

herve

6579 silver badges22 bronze badges

Collectives™ on Stack Overflow

Updating multiple rows at once with Entity Framework

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related