C# 如何使用 linq to sql 一次更新多行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10314552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to update the multiple rows at a time using linq to sql?
提问by user1237131
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
如果用户发送 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].?
请告诉我有关如何更新上述所有好友 ID 状态的查询为 true。[一次 2,3,4]。？
thanks
谢谢
采纳答案by Arion
To update one column here are some syntax options:
要更新一列，这里有一些语法选项：
Option 1
选项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
选项 2
using (var db=new SomeDatabaseContext())
{
db.SomeTable
.Where(x=>ls.Contains(x.friendid))
.ToList()
.ForEach(a=>a.status=true);
db.SubmitChanges();
}
Option 3
选项 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 statusat ones. We want to update nameand statuswhere the friendidis matching. Here are some syntax options for that:
根据评论中的要求，展示如何更新多个列可能是有意义的。因此，出于本练习的目的，让我们说我们不仅要更新statusat。我们要更新name，并status在friendid为匹配。以下是一些语法选项：
Option 1
选项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
选项 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
选项 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
更新 2
In the answer I was using LINQ to SQL and in that case to commit to the database the usage is:
在我使用 LINQ to SQL 的答案中，在这种情况下，提交到数据库的用法是：
db.SubmitChanges();
But for Entity Framework to commit the changes it is:
但是对于要提交更改的实体框架，它是：
db.SaveChanges()
回答by shaijut
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.
希望能帮助某人。
回答by Jacob
Do notuse the ToList()method as in the accepted answer !
不要使用ToList()已接受答案中的方法！
Running SQL profiler, I verified and found that ToList()function gets all the records from the database. It is really bad performance !!
运行 SQL 探查器，我验证并发现该ToList()函数从数据库中获取所有记录。性能真的很差！！
I would have run this query by pure sql command as follows:
我会通过纯 sql 命令运行此查询，如下所示：
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.
这将在不选择一行的情况下一次性操作更新。
