postgresql - PostgreSQL 上的 EF Core 批量删除

Question

我正在尝试对单个表执行可能的大规模删除操作。 (想想 100 万行表上的 100,000 行)

我正在使用 PostgreSQL 和 EntityFrameworkCore。

详细信息:应用程序代码有一个要匹配的谓词，但不知道有多少行可能与该谓词匹配。它可能是 0 行/秒或非常大的数量。

研究表明 EF Core 无法有效处理此问题。 (即以下代码为每一行生成一个 Delete 语句!)

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. 有没有办法让 EF Core 使用 LINQ 等在 Postgres 上更有效地删除这些行？(在我看来，将上下文交给一个可查询的应该给它在这里做出正确决定所需的一切)
2. 如果不是，您对批量删除与仅将谓词交给数据库有何看法？

Answer 1

我认为您正在尝试做一些您不应该使用 EntityFrameworkCore 的事情。 EntityFrameworkCore 的目标是提供一种在 .Net-Core 应用程序和数据库之间移动数据的好方法。典型的使用方式是单个或少量对象。对于批量操作，有一些 nuget-packages。有 this用于使用 postgres 插入和更新的包。 This article by the creator解释它如何使用临时表和 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+")" )

是的，这非常骇人听闻，我不推荐这样做。我建议在 databaseServer 上编写过程和函数，因为这不是 ef-core 应该用于的东西。然后您仍然可以从 ef-core 运行这些函数并传递参数。