LINQ-SQL Updating Multiple Rows in a single transaction
- by RPM1984
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.
