title: EF Core Bulk Delete on PostgreSQL
body: <p>I’m trying to do a, potentially, large scale delete operation on a single table. (think 100,000 rows on a 1m row table)</p>
  
  <p>I’m using PostgreSQL and EntityFrameworkCore.</p>
  
  <p>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.</p>
  
  <p>Research indicates EF Core is incapable of handling this efficiently. (i.e. the following code produces a Delete statement for each row!)</p>
  
  <pre><code>Using (var db = new DbContext)
   var queryable = db.Table.AsQueryable()
         .Where(o =&gt; o.ForeignKey == fKey)
         .Where(o =&gt; o.OtherColumn == false);
  
   db.Table.RemoveRange(queryable);
   await db.SaveChangesAsync();
  </code></pre>
  
  <p>So here is the SQL I would prefer to run in a sort of batched operation:</p>
  
  <pre><code>delete from Table
  where ForeignKey = 1234
  and OtherColumn = false
  and PK in (
      select PK
      from Table
      where ForeignKey = 1234
      and OtherColumn = false
      limit 500
  )
  </code></pre>
  
  <p>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.</p>
  
  <p>This leads to a couple questions:</p>
  
  <ol>
  <li>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)</li>
  <li>If not, what are your opinions on deleting in batches vs handing the DB just the predicate?</li>
  </ol>