5

I am using EF 6 in my project, when i want to delete a row from a table it trow exception because that row is referenced in another table. I want to know which table preventing me from deleting with c# code and EF.

enter image description here

We have a record in table A and that row is referenced in table C not in table B. Is it possible with EF to know that table C prevent me from deleting that row?

I am also using Sql-Server 2012.

Mashtani
  • 621
  • 11
  • 24
  • 3
    Three thoughts: parse the exception; read the entity first and examine the child entities; switch on cascade delete – Peter Smith Aug 20 '16 at 04:29
  • Database server (not EF) prevent deletion of parent table if there is a relation created in the server. run that script to know the relationship: http://stackoverflow.com/a/8095137/3142139. – M.Hassan Aug 20 '16 at 19:59

1 Answers1

0

If you are trying to dynamically sort this at runtime to show the user or determine before delete attempt and you are unsure of the possible conflict you could use the sys tables and some dynamic sql to sort this out.

  1. Use sys.objects to find your table name and get an object_id.
  2. Use sys.foreign_keys to find tables that reference your table.
  3. Use sys.foreign_key_columns to get the exact column numbers referenced.
  4. Use sys.columns to get appropriate column names.
  5. Build dynamic SQL to search the table and column names and find the offending rows using the values from the source row that cannot be deleted.
  6. List the tables returned looking up in sys.objects. Optionally list the number of rows.
  7. Optionally use dynamic SQL to build other statements (e.g. delete offending records - this may need to be recursive - take care with this - you could lose a lot of data!)
SMM
  • 2,225
  • 1
  • 19
  • 30