7

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

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

  • Should it be only through Entity Framework? Because it's a piece a cake via T-SQL. – Saeed Neamati Nov 16 '13 at 22:00
  • Personally I would consider that you should not have cascade delete set up for any tables. It is an extremely bad idea at all times in my opinion because you can end up deleting one record and locking up many tables and deleting millions of associated records bring your system to a screeching halt. Further, the existance of child records is often the key to why the parent record should not be dleted. You shouldn't delete a customer who has orders in the past for instance. YOu might consider a soft delte scheme instead. – HLGEM Jan 19 '14 at 18:06
  • Well, assuming the records have to be deleted, I would much prefer that to happen automatically (rather than manually, which involves human error). –  Jan 24 '14 at 17:13

2 Answers2

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.

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.

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

qujck
  • 14,388
  • 4
  • 45
  • 74
BlackjacketMack
  • 5,472
  • 28
  • 32
  • As a side note, I didn't write the query above but pulled it from someone a long time ago. If I find the person I'll credit them. Also, the query is awesome for us in integration testing...we check all primary, foreign key constraints to make sure the DB is exactly as we expect it. – BlackjacketMack Nov 17 '13 at 13:46
  • Doubtless I could use a query like that, but the question is - How? –  Nov 18 '13 at 14:53
1

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.

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

Community
  • 1
  • 1
JsAndDotNet
  • 16,260
  • 18
  • 100
  • 123