How to run a mass update/delete query in Linq?

Question

I have 2 Linq2Sql classes: Parent and Child. I want to do things like removing all children for a parent, or updating all child records. In SQL I would have written:

delete Child where ParentID = @p

or

update Child set Val = Val+1 where ParentID = @p

I can do this in Linq the brute force way inside the Parent class:

Children.ToList().ForEach(c => c.DeleteOnSubmit()); // DeleteOnSubmit is my own method

and

Children.ToList().ForEach(c => c.Val++);

But given Linq's inherent performance penalty on ForEach loops, this seems like a very inefficient way to do things. Is there some way of achieving the desired end that will fire off exactly one query?

Answer 1

For these cases you can use the DataContext.ExecuteCommand method to directly execute SQL on your database. For example:

dataContext.ExecuteCommand("delete Child where ParentID = {0}", parentId);

Answer 2

look at this link : It's using ExpressionTree : http://www.aneyfamily.com/terryandann/post/2008/04/Batch-Updates-and-Deletes-with-LINQ-to-SQL.aspx

Answer 3

Take a look to http://magiq.codeplex.com mass operation with linq.