multiple cascade delete path in many-many relationship (EF 4.1)

Question

THE TABLES:

Shop
Product
Category

THE RELATIONSHIPS:

      (Shop) 1 <---> n (Categories)
      (Shop) 1 <---> n (Products)
(Categories) n <---> n (Products)

THE CASCADE DELETES:

      Shop  ---> Categories ... I defined this using fluent API
      Shop  ---> Products   ... I defined this using fluent API
Categories <---> Products   ... EF 4.1 automatically defines cascade for "Category_Product" join table

THE PROBLEM: Above results in a "multiple" cascade deletion path exception.

POTENTIAL FIXES:

1. Remove the ManyToManyConvention, but that means I must manually perform deletes for every join table in the system, which is impractical.
2. I can remove the cascade delete from Shop->Category or Shop->Products. But then I'll probably have lots of orphaned records.

How are you folks dealing with this problem?

THANKS

Answer 1

This is not a problem of entity framework but the problem of SQL server. I don't think that exception actually means circular cascade delete. It more probably means multiple cascade delete paths because join table records can be deleted from both categories and products side because of cascading from shop. SQL server doesn't allow this because it requires some more complex (and slow) algorithms to correctly compute which records and when have to be deleted when cascading.

Simply you must break this and it will really mean that you will have to manually delete all related records (either categories or products) before you delete shop. This will require stored procedure (or direct SQL DELETE command) otherwise you will have to load all of them first and delete them one by one.

Edit:

As you pointed in the comment this can be also solved by adding BEFORE DELETE trigger which will delete related records if exists as replacement of one cascade path.