LINQ to SQL update all records

Question

I have a table like this:

I want to update Seasonal_Voucher column for all users with something

like : UPDATE Users SET Seasonal_Voucher = 'Something'

I tried this but it wont work:

using (DataClassesDataContext context = new DataClassesDataContext())
{
    Users use = context.Users;
    {
        use.Seasonal_Voucher =DropDownList1.SelectedItem.ToString();
    };
    context.Refresh(0);
    context.SubmitChanges();
}

Define "it won't work." Compile errors? Runtime errors? Nothing happens? — Sami Kuhmonen, Jun 14 '17 at 03:47
Are you sure this is LINQ to SQL? Looks like entity framework. — Rob, Jun 14 '17 at 03:48

Keyur PATEL · Accepted Answer · 2017-06-15T01:33:22.553

As Sami suggested in the comments, "it won't work" is too broad. However, here is a basic modification of your code: (since you tagged linq also, I assume you are familiar with lambda syntax)

using (DataClassesDataContext context = new DataClassesDataContext())
{
    foreach (var use in context.Users)
    {
        use.Seasonal_Voucher = DropDownList1.SelectedItem.ToString();
    };
    //context.Refresh(0);
    //context.SubmitChanges();
    context.SaveChanges();
}

I realize (as Rob mentions) that loading the entire table into memory for updating is not wise. As such, you could simply try the ExecuteSqlCommand method (since it looks like you're using Entity Framework anyways). MSDN: Database.ExecuteSqlCommand Method

It is useful for bulk updates.

string sqlQuery = "UPDATE Users SET Seasonal_Voucher = '" + DropDownList1.SelectedItem.ToString() + "'";
using (DataClassesDataContext context = new DataClassesDataContext())
{
    context.Database.ExecuteSqlCommand(sqlQuery);
}

The question title explicitly says they want to update *all* records. Thus, the where is unneeded. And loading the entire table into memory is not ideal when what you're *really* trying to do is a bulk update — Rob, Jun 14 '17 at 03:53
@Rob You're right, I've edited my answer with a better way as alternative. — Keyur PATEL, Jun 14 '17 at 03:59

score 2 · Answer 2 · answered Jun 14 '17 at 04:35

Alternatively with reference to how to update the multiple rows at a time using linq to sql? Below code does the same. However as its mentioned by Keyur, entire table will be loaded for the update operation which would be a performance bottleneck.

using (DataClassesDataContext context = new DataClassesDataContext())
{
   var users= context.Users.ToList();
    users.ForEach(user=>user.Seasonal_Voucher=DropDownList1.SelectedItem.ToString());
   context.SaveChanges();

}

or

using (DataClassesDataContext context = new DataClassesDataContext())
{
   context.Users.ToList().ForEach(user=>user.Seasonal_Voucher=DropDownList1.SelectedItem.ToString());
   context.SaveChanges();   
}

LINQ to SQL update all records

2 Answers2