Entity Framework Code First: FOREIGN KEY constraint may cause cycles or multiple cascade paths

Question

Entity Framework Code First can generate the DB for the following POCOs.

public class Item {
    public int Id { get; set; }
    public string Name { get; set; }
}

public class ItemPair {
    public int Id { get; set; }

    public virtual Item FirstItem { get; set; }
    public virtual Item SecondItem { get; set; }
}

I would like to establish the relationship with First and Second item via ID fields rather than the an entire "Item" class. So:

public class ItemPair {
    public int Id { get; set; }

    public virtual Item FirstItem { get; set; }
    public int FirstItem_Id { get; set; }

    public virtual Item SecondItem { get; set; }
    public int SecondItem_Id { get; set; }
}

also works. Edit: This didn't actually work. Just generates additional FirstItem_Id1 and SecontItem_Id2 columns.

But just changing the foreign key properties to FirstItemId, SecondItemId, (without the underscore) like so:

public class ItemPair {
    public int Id { get; set; }

    public virtual Item FirstItem { get; set; }
    public int FirstItemId { get; set; }

    public virtual Item SecondItem { get; set; }
    public int SecondItemId { get; set; }
}

results in the following exception.

{"Introducing FOREIGN KEY constraint 'ItemPair_SecondItem' on table 'ItemPair' may cause
cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints.\r\nCould not create constraint. 
See previous errors."}

Why? And what can I do to avoid this exception.

Solution 1

My expectation is that in the first case your Id properties are not used in the database as FKs and EF will create another two columns (you can validate this by forcing pairing of navigation property with FK property using ForeignKeyAttribute). In the second case EF will correctly recognize your properties but it will also use cascade delete convention which will cause error in SQL server. You have two properties from the table pointing to the same parent. Actually in the database you can createItemPair from the same Item (both FKs set to the same Id). If both relations have cascade delete enabled it will result in multiple cascade paths => not allowed in SQL server.

The solution here is fluent mapping to manually define how the relations are mapped. Here is the example.

Solution 2

I decided to just remove the cascade delete convention.

 protected override void OnModelCreating(DbModelBuilder modelBuilder) {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
        modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();

    }

The reasons are:

• I prefer to mark records deleted or deactivated for audit purposes.
• At most I delete just the junction / mapping tables.
• With an ORM It is relatively trivial to loop through and delete child records in the rare case I need to.

Thank you Ladislav Mrnka pointing me in the right direction.

Comments

[Pauly]

Thanks so much for this. Spent a few hours trying to figure it out. It didn't occur to me to read up on cascade delete conventions.

[Josh]

Thank God, I had read another answer to a similar question and figured that I would need to do all of my attribute-based mapping as fluent. IMHO, EF tries to do too much by default.

[Pauly]

I felt the same way, and so I started with attribute based mapping as well. However I now use Fluent API mappings because it appears to give me more control as well as keeping my POCO free of cruft. Try installing the "Entity Framework Power Tools" and reverse engineering you current database. I think once you see the mappings it generates, it wont feel so alien.

[Dan Vanderboom]

This looks great! But unfortunately didn't work in my case. What I don't get is how I can call WillCascadeOnDelete(false) on every single relationship, and remove this convention, and still in my SQL scripts (verbose listing) I can see it creating the offending constraint. At the of this command is "ON DELETE CASCADE", despite all my work in fluent mappings to eliminate this behavior.

[Pauly]

Possibly you did not explicitly specify one or more relationship in your fluent mapping? Experiment by adding the remove cascade delete conventions to see if that works.