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:
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.