C#中VirtualEntityDeleted事件需泛型删子实体避免外键约束错误

我在C#应用里实现了VirtualEntityDeleted()事件，删除虚拟行时调用line.MarkAsDeleted()能成功删掉对应的物理行，但该物理行关联的子记录（通过外键引用）没法自动删除，保存时SQL抛出外键约束错误：

The DELETE statement conflicted with the REFERENCE constraint.
The conflict occurred in table 'PurchasePostingRelation'.

需要一个不用硬编码表名（比如PurchasePostingRelation）或外键列名的通用方案，在调用MarkAsDeleted()前自动检测并删除依赖的子实体。

现有实现代码 #

public void VirtualEntityDeleted(object sender, VirtualEntityChangedEventArgs e)
{
    if ((_DocumentEntity.ID > 0 && _AddedRows != null && _AddedRows.Any(a => a.Key == e.VirtualEntityGuid))
            || (_DocumentEntity.ID == 0 && _DocumentEntity.VirtualData.Tables.Contains(CustomEntity1) && _DocumentEntity.VirtualData.Tables[CustomEntity1].Rows.Count > 0)
            )
    {
        if (_DeletedRows == null)
            _DeletedRows = new Dictionary<Guid, long>();

        var row = _DocumentEntity.VirtualData.Tables[CustomEntity1].Select("GUID = '" + e.VirtualEntityGuid + "'").FirstOrDefault();

        if (row == null) 
                    return;

        if (_DocumentEntity.VirtualData.Tables[CustomEntity1].Columns.Contains("LineGuid"))
        {
            var _line = _DocumentEntity.Lines.Where(r => Convert.ToString(r.Guid) == Convert.ToString(row["LineGuid"])).FirstOrDefault();

            if (_line != null)
                _line.MarkAsDeleted();
        }
        else
        {
            var _row = _DocumentEntity.Lines.Where(r => Convert.ToString(r.ID) == Convert.ToString(row["TransactionLIneID"])).FirstOrDefault();

            if (_row != null)
                _row.MarkAsDeleted();
        }

        _DeletedRows.Add(e.VirtualEntityGuid, row["ID"] != DBNull.Value ? Convert.ToInt64(row["ID"]) : 0);
    }
}

方案1：通过数据库系统视图自动获取依赖关系 #

直接从数据库的系统表中查询目标实体的所有依赖子表及外键信息，批量删除子记录，全程无需硬编码表名或列名。

核心实现代码 #

// 获取指定主表的所有依赖子表信息（子表名、外键列、主表主键列）
private List<(string ChildTableName, string ChildForeignKeyColumn, string ParentPrimaryKeyColumn)> GetDependentTables(string parentTableName, string parentPrimaryKeyName)
{
    var dependentTables = new List<(string, string, string)>();
    // SQL Server系统视图查询语句，其他数据库可调整系统表名称
    string query = @"
        SELECT 
            OBJECT_NAME(fk.parent_object_id) AS ChildTableName,
            c.name AS ChildForeignKeyColumn,
            pc.name AS ParentPrimaryKeyColumn
        FROM 
            sys.foreign_keys fk
        INNER JOIN 
            sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
        INNER JOIN 
            sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
        INNER JOIN 
            sys.columns pc ON fkc.referenced_object_id = pc.object_id AND fkc.referenced_column_id = pc.column_id
        WHERE 
            OBJECT_NAME(fk.referenced_object_id) = @ParentTableName
            AND pc.name = @ParentPrimaryKeyName";

    using (var conn = new SqlConnection(_connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(query, conn))
        {
            cmd.Parameters.AddWithValue("@ParentTableName", parentTableName);
            cmd.Parameters.AddWithValue("@ParentPrimaryKeyName", parentPrimaryKeyName);
            
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    dependentTables.Add((
                        reader.GetString(0),
                        reader.GetString(1),
                        reader.GetString(2)
                    ));
                }
            }
        }
    }
    return dependentTables;
}

// 删除指定主实体的所有依赖子记录
private void DeleteDependentEntities<T>(T parentEntity, string parentTableName, string primaryKeyName)
{
    // 反射获取主实体的主键值
    var primaryKeyValue = parentEntity.GetType().GetProperty(primaryKeyName).GetValue(parentEntity);
    var dependentTables = GetDependentTables(parentTableName, primaryKeyName);

    foreach (var (childTable, fkColumn, _) in dependentTables)
    {
        // 参数化查询避免SQL注入
        string deleteQuery = $"DELETE FROM {childTable} WHERE {fkColumn} = @PrimaryKeyValue";
        using (var conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            using (var cmd = new SqlCommand(deleteQuery, conn))
            {
                cmd.Parameters.AddWithValue("@PrimaryKeyValue", primaryKeyValue);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

修改原事件方法 #

在调用MarkAsDeleted()前先执行依赖删除：

public void VirtualEntityDeleted(object sender, VirtualEntityChangedEventArgs e)
{
    if ((_DocumentEntity.ID > 0 && _AddedRows != null && _AddedRows.Any(a => a.Key == e.VirtualEntityGuid))
            || (_DocumentEntity.ID == 0 && _DocumentEntity.VirtualData.Tables.Contains(CustomEntity1) && _DocumentEntity.VirtualData.Tables[CustomEntity1].Rows.Count > 0)
            )
    {
        if (_DeletedRows == null)
            _DeletedRows = new Dictionary<Guid, long>();

        var row = _DocumentEntity.VirtualData.Tables[CustomEntity1].Select("GUID = '" + e.VirtualEntityGuid + "'").FirstOrDefault();

        if (row == null) 
            return;

        object targetLine = null;
        // 这里可通过实体元数据动态获取主表名和主键名，比如从实体的特性、配置类读取
        string parentTableName = "你的主表名称";
        string primaryKeyName = "ID";

        if (_DocumentEntity.VirtualData.Tables[CustomEntity1].Columns.Contains("LineGuid"))
        {
            targetLine = _DocumentEntity.Lines.Where(r => Convert.ToString(r.Guid) == Convert.ToString(row["LineGuid"])).FirstOrDefault();
        }
        else
        {
            targetLine = _DocumentEntity.Lines.Where(r => Convert.ToString(r.ID) == Convert.ToString(row["TransactionLIneID"])).FirstOrDefault();
        }

        if (targetLine != null)
        {
            // 先删所有依赖子记录
            DeleteDependentEntities(targetLine, parentTableName, primaryKeyName);
            // 再标记主行删除
            ((dynamic)targetLine).MarkAsDeleted();
        }

        _DeletedRows.Add(e.VirtualEntityGuid, row["ID"] != DBNull.Value ? Convert.ToInt64(row["ID"]) : 0);
    }
}

方案2：利用ORM级联删除配置 #

如果项目使用Entity Framework等ORM框架，直接在实体关系配置中开启级联删除，ORM会自动处理依赖删除，完全不用写额外代码：

// EF Core配置示例
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<你的主实体类>()
        .HasMany(e => e.子实体集合属性)
        .WithOne(e => e.主实体导航属性)
        .OnDelete(DeleteBehavior.Cascade);
}

注意事项 #

• 方案1需要确保应用账号有读取数据库系统视图的权限。
• 动态SQL必须用参数化查询，避免注入风险。
• 多层级依赖需要递归处理，否则深层子记录会导致约束错误。

内容的提问来源于stack exchange，提问作者Roshan Kadel