EntityFramework 6.0 generating one DELETE per row

Question

I'm deleting about 5000 rows of my table on a clustered index, and EF takes almost 30 seconds to do it.

Logging the generated SQL shows it's doing one delete for every row:

DELETE [dbo].[Table]
WHERE ([ColumnID] = @0)

So there are 5000 transactions in total.

I've tried calling SaveChanges() in batches, but this doesn't appear to improve performance much, or change the above behaviour.

//loop
{
    //get batch
    db.Table.RemoveRange(batch);
    db.SaveChanges();
}

I have looked at some other questions which point out this flaw of delete commands executed per row, but no suggestion is made afterwards: How do I delete multiple rows in Entity Framework (without foreach)

Is there any way of stopping EF executing one command per row? I'd like to avoid inline SQL if possible.

score 10 · Accepted Answer · edited May 23 '17 at 11:50

Unfourtunately that is the normal behavior of RemoveRange method. You can see the answer in this post for more details (check quoted text).

One option could be using EntityFramework Extended Library nuget package, which allows you create a batch to delete the rows in one round trip:

 //delete all rows where FirstName matches
 db.Table
.Where(u => u.FirstName == "firstname")// just an example
.Delete();

If you don't want to use another 3rd party library, always you have the option of execute a raw sql stamement:

db.ExecuteStoreCommand("DELETE FROM Table WHERE FirstName = {0}", firstname);

But I think you are pursuing the first solution ;).

I'd heard some negative feedback regarding EntityFramework Extended Library, but I gave this a go and it was SUBSTANTIALLY faster. In my case at least - it's a great shout! — FBryant87, Mar 29 '16 at 15:10

1 Answers1