Problem
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 provides userid=1,friendids=2,4,5 status=true, the user will receive a response.
How would I go about updating the above query? All friendids’ statuses are accurate. [Two, three, four at a time]?
Asked by user1237131
Solution #1
Here are several syntactic possibilities for updating a single column:
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
It might make sense to demonstrate how to change many columns, as requested in the comment. So, let’s pretend that we don’t only want to update the status at one for the sake of this experiment. Where the friendid matches, we want to change the name and status. Here are some syntactic alternatives:
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
I used LINQ to SQL in the answer, and in that case, to commit to the database, the syntax is:
db.SubmitChanges();
However, in order for Entity Framework to commit the changes, you must:
db.SaveChanges()
Answered by Arion
Solution #2
Use the ToList() function instead of the acceptable answer’s ToList() method!
I verified and discovered that the ToList() function receives all of the records from the database using SQL profiler. It’s a terrible performance!!
I would have used the following pure sql command to conduct this query:
string query = "Update YourTable Set ... Where ...";
context.Database.ExecuteSqlCommandAsync(query, new SqlParameter("@ColumnY", value1), new SqlParameter("@ColumnZ", value2));
This would perform the update in one go, without having to choose a single row.
Answered by Jacob
Solution #3
So here’s 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 => h.ShopID == 123 && h.Type == item.Type).ToList();
feature.ForEach(a => a.SortOrder = item.SortOrder);
}
context.SaveChanges();
}
Hope helps someone.
Note: 5 years later, as mentioned in the comments, this isn’t a good solution because I’m using DB calls to collect data from within foreach. It’s fine to use if you’re not doing the same.
Answered by Shaiju T
Post is based on https://stackoverflow.com/questions/10314552/how-to-update-the-multiple-rows-at-a-time-using-linq-to-sql