如何使用 linq to sql 一次更新多行?

问题描述

表格:

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

如果用户发送userid=1,friendids=2,4,5 status=true

我将如何编写查询来更新上述内容?所有 friendids 状态为真.[一次 2,3,4]?

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

推荐答案

要更新一列，这里有一些语法选项:

To update one column here are some syntax options:

选项 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();
}

选项 2

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

     db.SubmitChanges();
}

选项 3

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

更新

根据评论中的要求，展示如何更新多个列可能是有意义的.因此，出于本练习的目的，让我们说我们不仅要更新 status .我们要更新 friendid 匹配的 name 和 status .以下是一些语法选项:

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:

选项 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();
}

选项 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();
}

选项 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();
}

更新 2

在答案中，我使用的是 LINQ to SQL，在这种情况下，要提交到数据库，用法是:

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()