LINQ-SQL Updating Multiple Rows in a single transaction

Question

Hi guys,

I need help re-factoring this legacy LINQ-SQL code which is generating around 100 update statements.

I'll keep playing around with the best solution, but would appreciate some ideas/past experience with this issue.

Here's my code:

List<Foo> foos;
int userId = 123;

using (DataClassesDataContext db = new FooDatabase())
{
     foos = (from f in db.FooBars
             where f.UserId = userId
             select f).ToList();

     foreach (FooBar fooBar in foos)
     {
         fooBar.IsFoo = false;
     }

     db.SubmitChanges()
}

Essentially i want to update the IsFoo field to false for all records that have a particular UserId value.

Whats happening is the .ToList() is firing off a query to get all the FooBars for a particular user, then for each Foo object, its executing an UPDATE statement updating the IsFoo property.

Can the above code be re-factored to one single UPDATE statement?

Ideally, the only SQL i want fired is the below:

UPDATE FooBars
SET    IsFoo = FALSE
WHERE  UserId = 123

---

EDIT

Ok so looks like it cant be done without using db.ExecuteCommand.

Grr...!

What i'll probably end up doing is creating another extension method for the DLINQ namespace. Still require some hardcoding (ie writing "WHERE" and "UPDATE"), but at least it hides most of the implementation details away from the actual LINQ query syntax.

Answer 1

Check DataClassesDataContext.ExecuteCommand...