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 user sends userid 1 friendids 2 4 5 status true  please tell me the query for how to update the above  all friendids status is true   2 3 4 at a time     thanks

User · Answer

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

User · Answer

This is what I did   EF   using  var context   new SomeDBContext          foreach  var item in model ShopItems      ShopItems is a posted list with values                    var feature   context Shop                               Where h   gt  h ShopID    123  amp  amp  h Type    item Type  ToList             feature ForEach a   gt  a SortOrder   item SortOrder              context SaveChanges        Hope helps someone

User · Answer

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  gt ls Contains x friendid   ToList        some ForEach a  gt a status true       db SubmitChanges        Option 2  using  var db new SomeDatabaseContext           db SomeTable         Where x  gt ls Contains x friendid           ToList           ForEach a  gt a status true         db SubmitChanges        Option 3  using  var db new SomeDatabaseContext          foreach  var some in db SomeTable Where x  gt 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  gt ls Contains x friendid   ToList        some ForEach a  gt                                                a status true                          a name name                                               db SubmitChanges        Option 2  using  var db new SomeDatabaseContext          db SomeTable          Where x  gt ls Contains x friendid            ToList            ForEach a  gt                                                a status true                          a name name                                               db SubmitChanges        Option 3  using  var db new SomeDatabaseContext          foreach  var some in db SomeTable Where x  gt 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

[c#] how to update the multiple rows at a time using linq to sql?

Examples related to c#

Examples related to linq

Examples related to entity-framework

Examples related to linq-to-sql