Multiple cascade paths Entity Framework

Question

My code-first Entity Framework model is as follows:

In my database I have customers, purchases and items.

• A customer may have several purchases
• Each purchase may consist of several purchased_item.
• Each purchased_item makes reference to an item.

So, when configuring my entities with the FluentAPI I have the following configurations:

public class PurchaseConfiguration : EntityTypeConfiguration<Purchase>
{
    public PurchaseConfiguration (string schema = "dbo")
    {
        ToTable(schema + ".Purchase");
        HasKey(p => p.PurchaseId);
        Property(p => p.Name);

        HasMany(p => p.PurchasedItems) //This is an ICollection of PurchaseItem in the Purchase class
            .WithRequired(pi => pi.Purchase)
            .HasForeignKey(pi => pi.PurchaseId)
            .WillCascadeOnDelete(true);
    }
}

public class ItemConfiguration : EntityTypeConfiguration<Item>
{
    public ItemConfiguration (string schema = "dbo")
    {
        ToTable(schema + ".Item");
        HasKey(i => i.ItemId);
        Property(i => i.Name);

        HasMany(i => i.PurchasedItems)
            .WithRequired(pi => pi.Item)
            .HasForeignKey(pi => pi.ItemId)
            .WillCascadeOnDelete(true);
    }
}

public class PurchasedItemConfiguration : EntityTypeConfiguration<PurchasedItem>
{
    public PurchasedItemConfiguration (string schema = "dbo")
    {
        ToTable(schema + ".PurchasedItem");
        HasKey(rp => rp.PurchasedItemId);
    }
}

How I want this to behave is that when you erase a purchased item on the DB no information related to either a purchase or an item is lost. However, if an item or a purchase is deleted from the db, then the purchaseditem is erased too.

However, when I try to update my database I get the following exception:

Introducing FOREIGN KEY constraint 'FK_dbo.PurchasedItem_dbo.Item_ItemId' on table 'PurchasedItem' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint or index. See previous errors.

Answer 1

You're getting a cycle because a Customer has many Purchases and many Items, and each of those entities have cascade paths to PurchasedItem. If a customer has many PurchasedItems instead of Item -- is that what you mean? -- that would create a cycle as well.

Cascade direction:

          +----------+      
          | Customer |      
          +--+----+--+      
             |    |         
             |    |         
+--------+   |    |   +----+
|Purchase| <-+    +-> |Item|
+----+---+            +--+-+
     |                   |  
     |                   |  
     |  +-------------+  |  
     +> |PurchasedItem| <+  
        +-------------+