how to update the multiple rows at a time using linq to sql?

Question

Table:

id     userid  friendid   name    status 1      1        2         venkat  false 2      1        3         sai     true 3      1        4         arun    false 4      1        5         arjun   false 

If a user sends userid=1,friendids=2,4,5 status=true

How would I write the query to update the above? All friendids status is true. [2,3,4 at a time]?

Answer 1

To update one column here are some syntax options:

Option 1

var ls=new int[]{2,3,4}; using (var db=new SomeDatabaseContext()) {     var some= db.SomeTable.Where(x=>ls.Contains(x.friendid)).ToList();     some.ForEach(a=>a.status=true);     db.SubmitChanges(); } 

Option 2

using (var db=new SomeDatabaseContext()) {      db.SomeTable        .Where(x=>ls.Contains(x.friendid))        .ToList()        .ForEach(a=>a.status=true);       db.SubmitChanges(); } 

Option 3

using (var db=new SomeDatabaseContext()) {     foreach (var some in db.SomeTable.Where(x=>ls.Contains(x.friendid)).ToList())     {         some.status=true;     }     db.SubmitChanges(); } 

Update

As requested in the comment it might make sense to show how to update multiple columns. So let's say for the purpose of this exercise that we want not just to update the status at ones. We want to update name and status where the friendid is matching. Here are some syntax options for that:

Option 1

var ls=new int[]{2,3,4}; var name="Foo"; using (var db=new SomeDatabaseContext()) {     var some= db.SomeTable.Where(x=>ls.Contains(x.friendid)).ToList();     some.ForEach(a=>                     {                         a.status=true;                         a.name=name;                     }                 );     db.SubmitChanges(); } 

Option 2

using (var db=new SomeDatabaseContext()) {     db.SomeTable         .Where(x=>ls.Contains(x.friendid))         .ToList()         .ForEach(a=>                     {                         a.status=true;                         a.name=name;                     }                 );     db.SubmitChanges(); } 

Option 3

using (var db=new SomeDatabaseContext()) {     foreach (var some in db.SomeTable.Where(x=>ls.Contains(x.friendid)).ToList())     {         some.status=true;         some.name=name;     }     db.SubmitChanges(); } 

Update 2

In the answer I was using LINQ to SQL and in that case to commit to the database the usage is:

db.SubmitChanges(); 

But for Entity Framework to commit the changes it is:

db.SaveChanges() 

Answer 2

Do not use the ToList() method as in the accepted answer !

Running SQL profiler, I verified and found that ToList() function gets all the records from the database. It is really bad performance !!

I would have run this query by pure sql command as follows:

string query = "Update YourTable Set ... Where ...";     context.Database.ExecuteSqlCommandAsync(query, new SqlParameter("@ColumnY", value1), new SqlParameter("@ColumnZ", value2)); 

This would operate the update in one-shot without selecting even one row.