Bulk-deleting in LINQ to Entities

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.

I know of [DeleteAllOnSubmit][1] 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.


[1]:

[To see links please register here]

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();

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.

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

using (var context = new DatabaseEntities())
{
// delete existing records
context.ExecuteStoreCommand("DELETE FROM YOURTABLE WHERE CustomerID = {0}", customerId);
}

Deleting data via the Entity Framework relies on using the DeleteObject method. You can call this method on the EntityCollection for the entity class you want to delete or on the derived ObjectContext. Here is a simple example:

NorthwindEntities db = new NorthwindEntities();

IEnumerable<Order_Detail> ods = from o in db.Order_Details
where o.OrderID == 12345
select o;

foreach (Order_Detail od in ods)
db.Order_Details.DeleteObject(od);

db.SaveChanges();

The question is an old one (from before EF5 existed). For anyone who's using EF5, [EntityFramework.Extended][1] does this in a snap.


[1]:

[To see links please register here]

For those who use EF6 and want to execute row SQL query for deletion:

using (var context = new DatabaseEntities())
{
// delete existing records
context.Database.ExecuteSqlCommand("DELETE FROM YOURTABLE WHERE CustomerID = @id", idParameter);
}

I'd do something like:

var recordsToDelete = (from c in db.Candidates_T where c.MyField == null select c).ToList<Candidates_T>();
if(recordsToDelete.Count > 0)
{
foreach(var record in recordsToDelete)
{
db.Candidate_T.DeleteObject(record);
db.SaveChanges();
}
}

I don't think there is a way to do it without a loop since Entity Framework works with Entities and most of the time, these means collection of objects.