Is there any way to bulk-delete a bunch of objects matching a given query in LINQ or LINQ-to-Entities? The only references that I can find are outdated, and it seems silly to iterate over and manually delete all objects I wish to remove.
+3
A:
I know of DeleteAllOnSubmit method of any data context which will delete all the records in query. There must be some optimization underlying since a lot of objects are being deleted. I am not sure though.
Hemant
2009-05-15 15:07:59
There actually is not any optimization being performed. The generated SQL enumerates all objects that match your query, then manually iterates over them to delete them. Granted, the iteration happens in the DB, rather than in your code, but you're still needlessly building a result set merely to delete its contents--still far worse than a simple "DELETE FROM table WHERE foo = bar", which builds no result set and covers the table only once.
Benjamin Pollack
2009-05-15 15:28:44
A:
I'm not sure how efficient it would be, but you could try something like this:
// deletes all "People" with the name "Joe"
var mypeople = from p in myDataContext.People
where p.Name == "Joe";
select p;
myDataContext.People.DeleteAllOnSubmit(mypeople);
myDataContext.SubmitChanges();
Scott Anderson
2009-05-15 15:14:34
That still ends up iterating over all elements that match the query; it merely does so on the DB, rather than in your code. More efficient, but still far from an ideal solution.
Benjamin Pollack
2009-05-15 15:26:20
The only other way I could think to do it, then, would be to do myDataContext.ExecuteCommand("DELETE ...");. Far from ideal, also, but it would work.
Scott Anderson
2009-05-15 15:31:11
+1
A:
YOu could write a stored proc that does the delete and call it from LINQ. A set-based delete is likely faster overall but if it affects too many records you could cause locking issues and you might need a hybrid of looping through sets of records (maybe 2000 at a time, size depends on your database design but 2000 is a starting place if you find the set-based delte takes so long it is affecting other use of the table) to do the delete.
HLGEM
2009-05-15 17:28:08
+11
A:
A while back I wrote a 4 part blog series (Parts 1, 2, 3 and 4) covering doing bulk updates (with one command) in the Entity Framework.
While the focus of that series was update, you could definitely use the principles involved to do delete.
So you should be able to write something like this:
var query = from c in ctx.Customers
where c.SalesPerson.Email == "..."
select c;
query.Delete();
All you need to do is implement the Delete() extension method. See the post series for hints on how...
Hope this helps
Alex James
2009-05-15 17:59:07
Excellent series. Writing a Delete() extension method based on this will be trivial.
Benjamin Pollack
2009-05-15 18:11:17
+1
A:
The Answers I'm seeing here are Linq to Sql
DeleteAllOnSubmit is part of System.Data.Linq and ITable which is Linq to Sql
This can't be done with Entity Framework.
Having said all of that I don't have a solution yet but will post back when I do
Phil Strong
2010-03-30 18:57:50
A:
Thanks Alex, I worked out with your method. Next I m gonna try from scott's
awsedr
2010-04-16 05:23:31