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
This question is related to
c#
linq
entity-framework
linq-to-sql
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()
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 => h.ShopID == 123 && h.Type == item.Type).ToList();
feature.ForEach(a => a.SortOrder = item.SortOrder);
}
context.SaveChanges();
}
Hope helps someone.
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.
Source: Stackoverflow.com