Bulk deleting rows with RemoveRange()

Question

I am trying to delete multiple rows from a table.

In regular SQL Server, this would be simple as this:

DELETE FROM Table
WHERE
    Table.Column = 'SomeRandomValue'
    AND Table.Column2 = 'AnotherRandomValue'

In Entity Framework 6, they have introduced RemoveRange() method.
However, when I use it, rather than deleting rows using the where clauses that I provided, Entity Framework queries the database to get all rows that match the where clauses and delete them one by one using their primary keys.

Is this the current limitation of EntityFramework? Or am I using RemoveRange() wrong?

Following is how I am using RemoveRange():

db.Tables.RemoveRange(
    db.Tables
        .Where(_ => _.Column == 'SomeRandomValue'
            && _.Column2 == 'AnotherRandomValue')
);

Answer 1

I think we reached here a limitation of EF. Sometimes you just have to use ExecuteSqlCommand to stay performant.

Answer 2

What you are looking for is a Batch Delete Library which deletes multiple records in a database from a LINQ Query without loading entities.

Multiple libraries supporting this feature exist.

You can find the list here: Entity Framework Batch Delete Library

Disclaimer: I'm the owner of the project Entity Framework Plus

// using Z.EntityFramework.Plus; // Don't forget to include this.

// DELETE directly in SQL (without loading entities)
db.Tables.Where(_ => _.Column == 'SomeRandomValue'
                     && _.Column2 == 'AnotherRandomValue')
         .Delete();

// DELETE using a BatchSize      
db.Tables.Where(_ => _.Column == 'SomeRandomValue'
                     && _.Column2 == 'AnotherRandomValue')
         .Delete(x => x.BatchSize = 1000);

Wiki: EF+ Batch Delete

Answer 3

It's a bit broken, try

db.Tables.RemoveRange(
    db.Tables
        .Where(_ => _.Column == 'SomeRandomValue'
            && _.Column2 == 'AnotherRandomeValue').AsEnumerable().ToList()
);
db.SaveChanges();

Answer 4

var db1 =  db.Tables
        .Where(_ => _.Column == 'SomeRandomValue'
            && _.Column2 == 'AnotherRandomeValue').AsEnumerable().ToList();
db.Tables.RemoveRange(db1);
db.SaveChanges();

Use an Variable to Store Removable list and pass it to RemoveRange().

I Usually do like this, and That's Work. Hope This also work in your case.

Answer 5

Step back and think. Do you really want to download the records from the database in order to delete them? Just because you can doesn't make it a good idea.

Perhaps you could consider deleting the items from the database with a stored procedure? EF also allows to do that...

Answer 6

I'm dealing with this myself, and agree with Adi - just use sql.

I'm cleaning up old rows in a log table, and EF RemoveRange took 3 minutes to do the same thing this did in 3 seconds:

DELETE FROM LogEntries WHERE DATEDIFF(day, GETDATE(), Date) < -6

Date is the name of the column containing the date. To make it correct, use a parameter, of course, like this:

  context.Database.ExecuteSqlCommand
      ("DELETE FROM  LogEntries WHERE DATEDIFF(day, GETDATE(), Date) < @DaysOld", new System.Data.SqlClient.SqlParameter(
                        "DaysOld", - Settings.DaysToKeepDBLogEntries));

Note that there are a lot of rows involved in my case. When I started the project and didn't have a lot of data, RemoveRange worked fine.

Answer 7

I have met this problem as well, this is my workaround with a library called entityframework.extended by LoreSoft (which you can get it from nuget package manager):

1.You first query them your list. 2.Then use .Delete(), which is a function from .extended library.

var removeList = db.table.Where(_ => _.Column == 'SomeRandomValue'&& _.Column2 == 'AnotherRandomValue')        
removeList .Delete();

Note: According to Entity Framework EF extended, as of the time writing, this entityframework.extended is deprecated. Therefore one might need to consider entityframework plus library. While i have not tested on the plus extension, but i can confirmed working it works using .extended library.