How can I use linq to sql to update numerous rows at once?

c# entity-framework linq 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

1
90
6/15/2015 6:36:09 AM

Accepted 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=>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()
228
11/26/2015 3:40:42 PM

Popular 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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow