EF Core 7如何无主键批量删除实体？基于其他列实现

我写了下面的BulkDeleteIssueProvision方法，尝试通过AuthorityProvisionId、IssueId和TenantId这三个属性批量删除IssueProvision实体（没设置主键Id，因为需要从数据库获取这个值）：

public async Task BulkDeleteIssueProvision(List<IssueProvision> issueProvision)
{
        if (issueProvision is {Count: > 0})
        {
            var dbContext = GetDbContext();
            EntityEntry<IssueProvision> issueProvisionDbContext = null; 

            foreach (var issueProvisionList in issueProvision)
            {
                issueProvisionDbContext = dbContext.Attach(new IssueProvision
                {
                    AuthorityProvisionId = issueProvisionList.AuthorityProvisionId,
                    IssueId = issueProvisionList.IssueId,
                    TenantId = issueProvisionList.TenantId
                });
            }

            issueProvisionDbContext.State = EntityState.Deleted;
            await dbContext.SaveChangesAsync(); 
        }
}

执行时抛出异常：

The property 'IssueProvision.Id' has a temporary value while attempting to change the entity's state to 'Deleted'. Either set a permanent value explicitly, or ensure that the database is configured to generate values for this property.

已知问题和表主键有关，想请教怎么不用实体主键实现批量删除？

方法1：使用EF Core 7+的ExecuteDelete批量删除（推荐） #

EF Core 7及以上版本支持直接对查询结果执行批量删除，无需先加载实体，也不依赖主键。可根据三个属性构建查询条件，一次性删除符合要求的记录：

public async Task BulkDeleteIssueProvision(List<IssueProvision> issueProvision)
{
    if (issueProvision is { Count: > 0 })
    {
        var dbContext = GetDbContext();
        // 提取需要删除的三元组集合
        var deleteConditions = issueProvision.Select(ip => new 
        { 
            ip.AuthorityProvisionId, 
            ip.IssueId, 
            ip.TenantId 
        }).ToList();

        // 构建批量删除查询
        await dbContext.IssueProvisions
            .Where(ip => deleteConditions.Any(dc => 
                dc.AuthorityProvisionId == ip.AuthorityProvisionId &&
                dc.IssueId == ip.IssueId &&
                dc.TenantId == ip.TenantId))
            .ExecuteDeleteAsync();
    }
}

方法2：直接执行原生SQL语句 #

如果你的EF Core版本低于7，可直接执行原生SQL实现批量删除，效率同样很高：

public async Task BulkDeleteIssueProvision(List<IssueProvision> issueProvision)
{
    if (issueProvision is { Count: > 0 })
    {
        var dbContext = GetDbContext();
        // 构造参数化SQL，避免SQL注入
        var parameters = new List<SqlParameter>();
        var conditions = new List<string>();

        for (int i = 0; i < issueProvision.Count; i++)
        {
            var ip = issueProvision[i];
            conditions.Add($"(@AuthId{i}, @IssueId{i}, @TenantId{i})");
            parameters.Add(new SqlParameter($"@AuthId{i}", ip.AuthorityProvisionId));
            parameters.Add(new SqlParameter($"@IssueId{i}", ip.IssueId));
            parameters.Add(new SqlParameter($"@TenantId{i}", ip.TenantId));
        }

        var sql = $"DELETE FROM IssueProvisions WHERE (AuthorityProvisionId, IssueId, TenantId) IN ({string.Join(", ", conditions)})";
        await dbContext.Database.ExecuteSqlRawAsync(sql, parameters.ToArray());
    }
}

方法3：将三个字段配置为复合主键（业务允许时） #

如果AuthorityProvisionId、IssueId和TenantId的组合在表中是唯一的，可在EF Core模型配置里将它们设为复合主键，之后就能通过这三个字段直接标记实体删除：

// 在DbContext的OnModelCreating方法中配置
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<IssueProvision>()
        .HasKey(ip => new { ip.AuthorityProvisionId, ip.IssueId, ip.TenantId });
}

配置后，原方法可修改为正确处理批量删除（注意要给每个实体都标记Deleted状态，而非仅最后一个）：

public async Task BulkDeleteIssueProvision(List<IssueProvision> issueProvision)
{
    if (issueProvision is { Count: > 0 })
    {
        var dbContext = GetDbContext();
        foreach (var ip in issueProvision)
        {
            var entity = new IssueProvision
            {
                AuthorityProvisionId = ip.AuthorityProvisionId,
                IssueId = ip.IssueId,
                TenantId = ip.TenantId
            };
            dbContext.Attach(entity).State = EntityState.Deleted;
        }
        await dbContext.SaveChangesAsync();
    }
}

内容的提问来源于stack exchange，提问作者San Jaisy