Bulk-deleting in LINQ to Entities

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

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

A while back I wrote a 4 part blog series (Parts [1][1], [2][2], [3][3] and [4][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


[1]:

[To see links please register here]

[2]:

[To see links please register here]

[3]:

[To see links please register here]

[4]:

[To see links please register here]

[5]:

[To see links please register here]

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

**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](

[To see links please register here]

), 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.