EF Core Bulk Delete on PostgreSQL

entity-framework-core postgresql

Question

I’m trying to do a, potentially, large scale delete operation on a single table. (think 100,000 rows on a 1m row table)

I’m using PostgreSQL and EntityFrameworkCore.

Details: The application code has a predicate to match and knows nothing about how many rows potentially match the predicate. It could be 0 row/s or a very large amount.

Research indicates EF Core is incapable of handling this efficiently. (i.e. the following code produces a Delete statement for each row!)

Using (var db = new DbContext)
 var queryable = db.Table.AsQueryable()
       .Where(o => o.ForeignKey == fKey)
       .Where(o => o.OtherColumn == false);

 db.Table.RemoveRange(queryable);
 await db.SaveChangesAsync();

So here is the SQL I would prefer to run in a sort of batched operation:

delete from Table
where ForeignKey = 1234
and OtherColumn = false
and PK in (
    select PK
    from Table
    where ForeignKey = 1234
    and OtherColumn = false
    limit 500
)

There are extension libraries out there, but I’ve yet to find an active one that supports Postgres. I’m currently executing the raw sql above through EF Core.

This leads to a couple questions:

  1. Is there anyway to get EF Core to delete these rows more efficiently on Postgres using LINQ, etc? (Seems to me like handing the context a queryable should give it everything it needs to make the proper decision here)
  2. If not, what are your opinions on deleting in batches vs handing the DB just the predicate?
1
1
1/27/2019 11:33:31 PM

Expert Answer

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

Your scenario look to be something that our Batch Delete features could handle: https://entityframework-plus.net/batch-delete

Using (var db = new DbContext)
 var queryable = db.Table.AsQueryable()
       .Where(o => o.ForeignKey == fKey)
       .Where(o => o.OtherColumn == false);

queryable.Delete();

Entities are not loaded in the application, and only a SQL is executed as you specified.

0
1/28/2019 2:41:41 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