Prevent CASCADE DELETE that causes "cycles or multiple cascade paths"

Question

I have a relationship like

public class Widget
{
    public virtual int Id { get; set; }
    public virtual string Text { get; set; }
}

public class WidgetPair
{
    public virtual int Id { get; set; }

    public virtual Widget WidgetA { get; set; }
    public virtual int WidgetAId { get; set; }

    public virtual Widget WidgetB { get; set; }
    public virtual int WidgetBId { get; set; }
}

Note that Widget has no concept of a WidgetPair in the object model.

When I try to update my database with EF Code First migrations, I get the error

Introducing FOREIGN KEY constraint 'FK_dbo.WidgetPairs_dbo.Widget_WidgetAId' on table 'WidgetPairs' may cause cycles or multiple cascade paths

Makes sense, because SQL Server does not like two properties from the table pointing to the same parent.

The desired behavior is that, if a given WidgetPair.WidgetA (or .WidgetB) points to a particular Widget that is subsequently deleted, that WidgetA (or WidgetB) becomes null.

I do not want a delete in either the Widget table or the WidgetPairs table to delete a row in the other table.

It seems like I can configure this using the fluent interface, but I cannot see exactly how to do that. If I wanted to add a reference from Widget to WidgetPair (which I do not), the following seems like it would work:

modelBuilder.Entity<WidgetPair>().HasOptional(p => p.WidgetA)
  .WithMany(q => q.TheChildWidget)
  .WillCascadeOnDelete(false);

This will not work because a single Widget could participate in many WidgetPairs. Also, there is no domain requirement for a Widget to directly know what WidgetPairs it participates in.

Is it possible to configure this behavior? How?

score 0 · Accepted Answer · answered Jan 28 '14 at 04:03

It turns out I made the relationship from WidgetPair to Widget non-nullable by using the type int for the foreign key. That in turn causes the cascade delete.

Changing the type to int? resolves the issue:

public virtual Widget WidgetA { get; set; }
public virtual int? WidgetAId { get; set; }

Prevent CASCADE DELETE that causes "cycles or multiple cascade paths"

1 Answers1