EF Core relationship throwing error when creating the database

Question

I have an Address class, that I use in a Customer class and in an Order class:

public class Address
{
    public Customer Customer { get; set }
    ...
}

public class Customer
{
    ...
    public List<Address> Addresses { get; set;}
}

public class Order
{
    ...
    public Customer Curstomer { get; set; }
    public Address BillingAddress { get; set;}
    public Address ShippingAddress { get; set;}
}

I created the migrations succesfully but when I try to update-database I get the following error:

Introducing FOREIGN KEY constraint 'FK_Order_Address_ShippingAddressId' on table 'Order' 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.

What is the correct way of setting up such relationship? Is this modelling even correct? Because it seems strange to me that Address is a property related to Customer, but I'm also using it on Order, but duplicating the addresses in an OrderAddresses table seems wrong too.

score 3 · Accepted Answer · answered Apr 18 '18 at 17:05

To fix this, change the ReferentialAction for the onDelete of your foreign key in the migration to something other than Cascade. Restrict is probably a good option. It will look something like this:

constraints: table =>
            {
                table.PrimaryKey("PK_Order", x => x.Id);
                table.ForeignKey(
                    name: "FK_Orders_BillingAddress_BillingAddressId",
                    column: x => x.BillingAddressId,
                    principalTable: "Addresses",
                    principalColumn: "Id",
                    onDelete: ReferentialAction.Cascade);
            }

Just change that to Restrict or another choice.

Why?

If you have Cascade, suppose you were to delete the BillingAddress of your Order. That would try to cascade the delete to the Order which would then cascade its delete to the ShippingAddress which would then try to cascade that delete back to the Order and so on and so forth, hence why SQL Server correctly errors out on cyclical cascading deletes.

See also this question: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths - why?

Manually editing the migration file worked. But, do you know how could I to this using fluent configuration? To use the `.OnDelete()` method in the `OnModelCreatingMethod` method, I need to do something like this `builder.Entity().HasOne(x => x.ShippingAddress).WithOne(x => x.??????).OnDelete(xxxx)`. But I don't have a reference to the `Order` class int the `Address` class. The `Address` class references the `Customer` class. — rbasniak, Apr 18 '18 at 19:31
Hum, just discovered that I don't have to inform the parameters of the second part. So this worked: `modelBuilder.Entity().HasOne(x => x.ShippingAddress).WithMany().OnDelete(DeleteBehavior.Restrict);`. Thanks — rbasniak, Apr 18 '18 at 19:42

EF Core relationship throwing error when creating the database

1 Answers1