引入FOREIGN KEY可能会导致循环或多个级联路径 [英] Introducing FOREIGN KEY may cause cycles or multiple cascade paths

问题描述

我正在使用带有代码优先方法的Entity Framework。

i am using Entity Framework with code first approach.

在我的 onModelCreating 中，我使用键和关系（我使用的是Fluent API方法，而不是数据注释）。

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

但是当我尝试使用 Update-Database 命令我收到以下错误

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

在表'invoices'上引入FOREIGN KEY约束'FK_customers.invoices_customers.billingCenters_billingCenterId'可能导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。无法创建约束。见先前的错误。

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);

并生成以下数据库：

我说它可以正常工作，因为如果我看到 billingCenters 和发票 删除规则是无操作。

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

如何解决此问题？

谢谢您

推荐答案

我几乎确定我没有周期

I almost sure I do not have cycles

您确实有来自个客户到付款。如果删除客户，则可以通过 customers-> payments删除付款 或 customers-> billingCenters->发票->付款，因此是周期。

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!

我确定这正是我们想要的。实际上，某些数据库（肯定是Oracle）在多个级联路径上没有问题。不幸的是SqlServer不支持它们，所以EF设计人员决定不支持这种配置。

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?

您应该打破循环。您可以通过关闭以下至少一个客户客户关系的级联删除操作（在相应的关系配置中包括 WillCascadeOnDelete（false））来做到这一点- >付款或 customers->计费中心。

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.

这样做可以解决问题。我的意思是，您将能够成功运行迁移。但是请注意，您可能会遇到维护问题-根据数据库如何处理FK约束，可能无法简单地删除客户并要求手动删除相关记录（付款或 billingCenter ），然后再将其删除。

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.

这篇关于引入FOREIGN KEY可能会导致循环或多个级联路径的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！