Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

EntityFramework 6.0 generating one DELETE per row

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.

like image 496
FBryant87 Avatar asked Dec 24 '22 07:12

FBryant87


1 Answers

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

like image 199
octavioccl Avatar answered Dec 26 '22 22:12

octavioccl