Introducing FOREIGN KEY may cause cycles or multip

Question

i am using Entity Framework with code first approach.

In my onModelCreating I am creating my tables with keys and relationships (I am using Fluent API approach, not data annotations).

But when I try to generate my model using Update-Database command I receive following error

Introducing FOREIGN KEY constraint 'FK_customers.invoices_customers.billingCenters_billingCenterId' on table 'invoices' 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. See previous errors.

I almost sure I do not have cycles... and I would not have problem if I had cascade path. It's what I would like to have instead!

Following the model I am creating:

modelBuilder.Entity<Customer>()
    .ToTable("customers", schemaName)
    .HasKey(c => new { c.Code });

modelBuilder.Entity<BillingCenter>()
    .ToTable("billingCenters", schemaName)
    .HasKey(bc => new { bc.Id });

//1 Customer -> N BillingCenters
modelBuilder.Entity<BillingCenter>()
    .HasRequired(bc => bc.Customer)
    .WithMany(c => c.BillingCenters)
    .HasForeignKey(bc => bc.CustomerId);

modelBuilder.Entity<Invoice>()
    .ToTable("invoices", schemaName)
    .HasKey(i => new { i.Id });

//Here the code gives me problems
//1 BillingCenter -> N Invoices
modelBuilder.Entity<Invoice>()
    .HasRequired(i => i.BillingCenter)
    .WithMany(bc => bc.Invoices)
    .HasForeignKey(i => i.BillingCenterId);

modelBuilder.Entity<Payment>()
    .ToTable("payments", schemaName)
    .HasKey(ep => new { ep.Id })
    .Property(ep => ep.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);

//1 Customer -> N Payments
modelBuilder.Entity<Payment>()
    .HasRequired(ep => ep.customer)
    .WithMany(c => c.Payments)
    .HasForeignKey(ep => ep.customerCode);

//1 Invoice -> N Payments (Failed, Ok, ...)
modelBuilder.Entity<Payment>()
    .HasRequired(p => p.Invoice)
    .WithMany(i => i.Payments)
    .HasForeignKey(p => p.InvoiceId);

If I remove this code everything seems to work

modelBuilder.Entity<Invoice>()
    .HasRequired(i => i.BillingCenter)
    .WithMany(bc => bc.Invoices)
    .HasForeignKey(i => i.BillingCenterId);

And the following database is generated:

I say that it SEEMS to work because if I see the relation between billingCenters and invoices the delete rule is no action.

How Can I solve the problem?

Thank you in advance

Answer 1

I almost sure I do not have cycles

You do have a cycle from customers to payments. If you delete a customer, then the payment can be deleted via customers->payments or customers->billingCenters->invoices->payments, hence the cycle.

and I would not have problem if I had cascade path. It's what I would like to have instead!

I'm sure that's exactly what we want. In fact some databases (Oracle for sure) have no problems with multiple cascade paths. Unfortunately SqlServer does not support them, so EF designers decided to not support such configuration.

I say that it SEEMS to work because if I see the relation between billingCenters and invoices the delete rule is no action.

This is because your migration has failed and has been rolled back. It really tries to set delete cascade.

How Can I solve the problem?

You are expected to break the cycle. You can do that by turning off cascade delete (by including WillCascadeOnDelete(false) in the respective relationship configuration) for at least one of the relationships customers->payments or customers->billingCenters.

Doing that will "solve" the issue. I mean you'll be able to run successfully the migration. But note that you might have a maintenance problems - depending of how database processes the FK constraints, it might be impossible to simply delete a customer and require manually delete the related records (payments or billingCenters) before deleting it.