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 https://stackoverflow.com/a/44194925/2946329 — Salah Akbari, Nov 02 '17 at 10:03

score 7 · Accepted Answer · answered Nov 02 '17 at 10:04

7

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 02 '17 at 10:04

Koderzzzz

849
8
18

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. – tedebus Apr 18 '23 at 10:43

score 2 · Answer 2 · answered Nov 02 '17 at 10:05

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

Updating multiple rows at once with Entity Framework

2 Answers2