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 the user sends userid=1, friendids=2,4,5, and the status is true

Please confirm that my question on how to upgrade the above-mentioned friendids' status is accurate. [2, 3, 4 at once]. ?

thanks

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

Accepted Answer

Here are various syntactic choices for updating a single column:

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

Choice 2

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

     db.SubmitChanges();
}

Choice 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 could make sense to demonstrate how to change many columns, as asked in the remark. Let's assume for the sake of this exercise that we wish to do more than just update thestatus a single. In order to updatename and status in whichfriendid is compatible. For that, here are some syntactic choices:

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

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

Choice 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. Update

I used LINQ to SQL in the response, and in that case, the use is as follows to commit to the database:

db.SubmitChanges();

However, in order for Entity Framework to make the modifications, it must:

db.SaveChanges()
228
11/26/2015 3:40:42 PM

Popular Answer

3 zzzz, utilize theToList() the same approach as the correct response!

I checked this using SQL profiler and discovered thatToList() function retrieves all database records. It is a very poor 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));

By doing so, the update would be performed all at once without choosing any rows.



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