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.

This alternative helped me better stackoverflow.com/questions/19373310/… — Mzn

Pauly Pauly · Accepted Answer · 2011-11-23T03:23:03

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.

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

2 Answers