是否为DbSet启用了删除级联？

Using Entity Framework, how do you tell if Delete Cascade is enabled for a table, before removing any records from it?

使用实体框架,在从表中删除任何记录之前,如何判断是否为表启用了删除级联?

public partial class DataContext : DbContext
{
    public DbSet<Building> Buildings { get; set; }
    public DbSet<Room>     Rooms     { get; set; }
}

DataContext context;
Building    building;

// Will this start a DELETE CASCADE, removing Rooms within the Building?
context.Buildings.Remove(building);

This is for a generic function, so I can use DbSet<T> or DbContext but not T.
Need to do the test at runtime, just before the call to Remove().

这是一个通用函数,所以我可以使用DbSet 或DbContext而不是T.需要在运行时进行测试,就在调用Remove()之前。

Can you detect a DELETE CASCADE before doing the delete, and if so, how?

你可以在删除之前检测DELETE CASCADE,如果是,怎么做?

2 个解决方案

#1

You could pretty easily use a t-sql function or query that gives you the information you're after. Try wrapping the following with whatever predicates you want (e.g. WHERE PK.TABLE_NAME = 'My Table' AND C.DELETE_RULE = 'CASCADE'. If a record a exists, then you've got the information you need.

您可以非常轻松地使用t-sql函数或查询来为您提供所需的信息。尝试使用您想要的任何谓词包装以下内容(例如,WHERE PK.TABLE_NAME ='我的表'和C.DELETE_RULE ='CASCADE'。如果记录存在,那么您已获得所需的信息。

SELECT
    FK_TableName = FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME,
    FK_ColumnName = CU.COLUMN_NAME,
    PK_TableName = PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME,
    PK_ColumnName = PT.COLUMN_NAME,
    ConstraintName = C.CONSTRAINT_NAME,
    DeleteRule = C.DELETE_RULE
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
    SELECT
        i1.TABLE_NAME,
        i2.COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    WHERE
        i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
   ) PT ON PT.TABLE_NAME = PK.TABLE_NAME;

You could then wrap that into a DbSet extension and call it like context.Set<MyEntity>().UsesCascadeDelete() which fires the above query with your predicates and whatever else you want.

然后,您可以将其包装到DbSet扩展中,并像上下文一样调用它.Set ()。UsesCascadeDelete()使用您的谓词和您想要的任何其他内容触发上述查询。

Because EF can run TSQL queries very easily I would still consider them a 'part' of EF.

因为EF可以非常容易地运行TSQL查询,所以我仍然认为它们是EF的“部分”。

#2

Would 'Read FK Metadata' or 'Check an entity for FK usage' be helpful? It seems there are a couple of ways to try, though I have not tried either.

“阅读FK元数据”或“检查实体是否使用FK”会有所帮助吗?似乎有几种方法可以尝试,但我还没有尝试过。

I usually check manually in the mappings files and code repositories with that knowledge in mind. Maybe the previous * answers will enable you to create something more generic if you need.

我通常在考虑到这些知识的情况下手动检查映射文件和代码存储库。也许之前的*答案将使您能够在需要时创建更通用的东西。

#1

SELECT
    FK_TableName = FK.TABLE_SCHEMA + '.' + FK.TABLE_NAME,
    FK_ColumnName = CU.COLUMN_NAME,
    PK_TableName = PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME,
    PK_ColumnName = PT.COLUMN_NAME,
    ConstraintName = C.CONSTRAINT_NAME,
    DeleteRule = C.DELETE_RULE
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
    SELECT
        i1.TABLE_NAME,
        i2.COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    WHERE
        i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
   ) PT ON PT.TABLE_NAME = PK.TABLE_NAME;