在PostgreSQL上进行EF Core批量删除

Question

我正在尝试在单个表上进行大规模删除操作（类似于在100万行的表格中删除100,000行）。

我使用的是PostgreSQL和EntityFrameworkCore。

具体细节：应用程序代码有一个用于匹配的谓词，并且对可能匹配谓词的行数一无所知。它可能是0行或非常大量的行。

研究表明，EF Core无法有效处理此操作。（例如，以下代码会为每行生成一个删除语句！）

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();

以下是我倾向于在批处理操作中运行的SQL:

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

虽然有一些扩展库，但我还没有找到一个支持Postgres的活跃的扩展库。目前我通过EF Core执行上述原始sql。

这引出了几个问题：

1. 是否有任何方法可以使用LINQ等在Postgres上更有效地删除这些行？ （我认为将可查询的查询传递给上下文应该能够提供所有必要的信息以做出正确的决策）
2. 如果不行，您对分批删除与仅向数据库提供谓词的做法有何看法？

- msauce4

---

批量操作是支持 EFCore.BulkExtensions 的。免责声明：我是该项目的作者。 - borisdj

Answer 1

我认为你正在尝试使用EntityFrameworkCore做一些不应该使用它的事情。EntityFrameworkCore的目标是在.Net-Core应用程序和数据库之间移动数据的好方法。典型的用法是单个或少量对象。对于大批量操作，有一些NuGet包可供使用。这里有一个链接，可以使用它来插入和更新Postgres。本文作者解释了它如何使用临时表和Postgres COPY命令进行批量操作。这向我们展示了一种通过id批量删除行的方法：

var toDelete = GetIdsToDelete();
        using (var conn = new NpgsqlConnection(connectionString))
        {
            conn.Open();
            using ( var cmd = conn.CreateCommand())
            {
                cmd.CommandText =("CREATE TEMP TABLE temp_ids_to_delete (id int NOT NULL) ON COMMIT DROP ");
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            using (var writer  = conn.BeginBinaryImport($"COPY temp_ids_to_delete (id) FROM STDIN (FORMAT BINARY)"))
            {
                foreach (var id in toDelete)
                {
                    writer .StartRow();
                    writer .Write(id);
                }
                writer .Complete();
            }
            using (var cmd = conn.CreateCommand())
            {
                cmd.CommandText = "delete from myTable where id in(select id from temp_ids_to_delete)";
                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            conn.Close();

通过一些小的改动，这个可以更加通用。

但是你想做一些不同的事情。你不想在应用程序和数据库之间移动数据或信息。你想使用efcore动态创建一个slq过程并在服务器上运行。问题是ef core并不是真正为此而构建的。但也许有绕过它的方法。我能想到的一种方式是使用ef-core构建查询，获取查询字符串，然后将该字符串插入到另一个sql字符串中以在服务器上运行。 目前获取查询字符串并不容易，但显然在EF Core 5.0中将会很容易。然后你可以这样做：

var queryable = db.Table.AsQueryable()
   .Where(o => o.ForeignKey == fKey)
   .Where(o => o.OtherColumn == false);
var queryString=queryable.ToQueryString();
db.Database.ExecuteSqlRaw("delete from Table where PK in("+queryString+")" )

是的，那很不专业，我不建议这样做。我建议在数据库服务器上编写存储过程和函数，因为这不是 ef-core 应该用于的事情。然后你仍然可以从 ef-core 运行这些函数并传递参数。

- HrkBrkkl

Answer 2

我建议使用临时表来执行这样的操作。您可以创建一个镜像临时表，将要保留或删除的记录批量添加到临时表中，然后执行一个查找在/不在该临时表中的记录的删除操作。尝试使用像PgPartner这样的库来轻松完成批量添加和临时表创建。

请查看PgPartner: https://www.nuget.org/packages/PgPartner/

https://github.com/SourceKor/PgPartner

- oleg3790

Answer 3

免责声明：我是项目Entity Framework Plus的所有者。

您的情况似乎是我们的批量删除功能可以处理的：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();

应用程序中未加载实体，仅按您指定的方式执行 SQL。

- Jonathan Magnan

---

我尝试了这个，使用PostgreSQL时它抛出了一个异常。 - msauce4