Bulk-deleting in LINQ to Entities

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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

Answer 6

RemoveRange was introduced in EF6, it can remove a list of objects. Super easy.

var origins= (from po in db.PermitOrigins where po.PermitID == thisPermit.PermitID select po).ToList();
db.PermitOrigins.RemoveRange(origins);
db.SaveChanges();

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

In this example I get the records to delete, and one by one attach them to the results set then request to have them removed. Then I have 1 save changes.

    using (BillingDB db = new BillingDB())
    {
      var recordsToDelete = (from i in db.sales_order_item
                  where i.sales_order_id == shoppingCartId
                  select i).ToList<sales_order_item>();

      if(recordsToDelete.Count > 0)
      {
        foreach (var deleteSalesOrderItem in recordsToDelete)
        {                  
            db.sales_order_item.Attach(deleteSalesOrderItem);
            db.sales_order_item.Remove(deleteSalesOrderItem);                  
        }
        db.SaveChanges();
      } 
    }

Answer 11

 context.Entity.Where(p => p.col== id)
               .ToList().ForEach(p => db.Entity.DeleteObject(p));

these is fastest method to delete record from DB using EF

Answer 12

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.

Answer 13

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.

Answer 14

There is not bulk operation implemented in the current EF.

It is just the way it was designed by the entity framework team. The decompiler shows clearly what EF is doing internally:

public void DeleteAllOnSubmit<TSubEntity>(IEnumerable<TSubEntity> entities) 
where TSubEntity : TEntity
{
    if (entities == null)
    {
        throw Error.ArgumentNull("entities");
    }
    CheckReadOnly();
    context.CheckNotInSubmitChanges();
    context.VerifyTrackingEnabled();
    foreach (TSubEntity item in entities.ToList())
    {
        TEntity entity = (TEntity)(object)item;
        DeleteOnSubmit(entity);
    }
}

As you can see, internally the EF loops through all elements of the table - materialized in memory by calling .ToList().

If you still want to do it with the possibilities coming with EF out of the box, and not submit a SQL command, you can still make your life easier with a little helper method. The syntax

ctx.Table1.DeleteAllOnSubmit(ctx.Table1);
ctx.Table2.DeleteAllOnSubmit(ctx.Table2);
ctx.Table3.DeleteAllOnSubmit(ctx.Table3);
ctx.SubmitChanges();

doesn't look very nice in my opinion.

Here's an example I wrote in LinqPad, that simplifies it a bit:

void Main()
{
    var ctx = this;

    void DeleteTable<T>(System.Data.Linq.Table<T> tbl, bool submitChanges = false)
    where T : class
    {
        tbl.DeleteAllOnSubmit(tbl);
        if (submitChanges) ctx.SubmitChanges();
    }
    
    DeleteTable(ctx.Table1);
    DeleteTable(ctx.Table2);
    DeleteTable(ctx.Table3);
        
    ctx.SubmitChanges();
}

If you're doing testing and need to delete a lot of tables, then this syntax is much easier to handle. In other words, this is some syntactic sugar for your convenience. But keep in mind that EF still loops through all objects internally and in memory, which can be very inefficient if it is much data.