Bulk deleting rows with RemoveRange()

c# entity-framework entity-framework-6

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')
);
2
24
11/10/2016 10:09:38 PM

Accepted Answer

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

3
1/27/2016 9:23:15 AM

Expert Answer

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

5
8/22/2018 10:31:06 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow