OnDelete(DeleteBehavior.CCascade)可能会在上导致循环或多个级联路径

Question

我有一个Products表和一个Categories表。我试图在产品和类别之间建立一种多对多的关系。所以我有一个名为：ProductCategories的表-我遵循了官方文档：

https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2流畅的api复合密钥%2简单密钥

public class ProductCategory
{
public Guid ProductId { get; set; }
public Product  Product { get; set; }

public Guid CategoryId { get; set; }
public Category  Category { get; set; }
}
public class Product
{
public Product()
{
ProductFiles = new Collection<ProductFiles>();
}

public Company Company { get; set; }
public Guid CompanyId { get; set; }

public string Name { get; set; }
public string Description { get; set; }
public decimal SalePrice { get; set; }
public decimal? CostPrice { get; set; }
public int VatPercentage { get; set; } = 25;
public ICollection<ProductFiles> ProductFiles { get; set; }

public ICollection<ProductCategory> Categories { get; set; }
}
public class Category
{
public string Title { get; set; }

public Guid CompanyId { get; set; }
public Company Company { get; set; }
public Guid? ParentCategoryId { get; set; }
public virtual ICollection<Category> SubCategories { get; set; }
public virtual Category ParentCategory { get; set; }

public bool Visible { get; set; } = true;
public int SortOrder { get; set; } = 1;

public ICollection<ProductCategory> Products { get; set; }
}

在modelbuilder中，我指定了关系

builder.Entity<Company>()
.HasMany(c => c.Products)
.WithOne(e => e.Company);

builder.Entity<Product>()
.Property(p => p.CostPrice)
.HasColumnType("decimal(18,2)"); 

builder.Entity<Product>()
.Property(p => p.SalePrice)
.HasColumnType("decimal(18,2)");
builder.Entity<Category>()
.HasMany<Category>(c => c.SubCategories)
.WithOne(c => c.ParentCategory)
.HasForeignKey(c => c.ParentCategoryId)
.OnDelete(DeleteBehavior.Restrict);

builder.Entity<Company>()
.HasMany<Category>(p => p.Categories)
.WithOne(p => p.Company)
.HasForeignKey(p => p.CompanyId).OnDelete(DeleteBehavior.Cascade);

builder.Entity((.HasKey(x=>new｛x.ProductId，x.CategoryId｝(；

builder.Entity<ProductCategory>()
.HasOne<Product>(pc => pc.Product)
.WithMany(p => p.Categories)
.HasForeignKey(p => p.ProductId);
builder.Entity<ProductCategory>()
.HasOne<Category>(p => p.Category)
.WithMany(p => p.Products)
.HasForeignKey(p => p.CategoryId);

问题是当我尝试更新数据库时，我得到了一个错误"；可能导致循环或多个级联路径。指定ON DELETE NO ACTION或ON UPDATE NO ACTION，或修改其他FOREIGN KEY约束。无法创建约束或索引">

我想要的是，当系统中有人删除一个类别/产品时，它应该删除ProductCategories中的记录-当我尝试添加onDelete时，如果没有任何操作，那么我就无法删除没有ProductsCategory表中它们关系的类别/产品。

关于如何以最佳方式解决这个问题，有什么建议吗？

Answer 1

公司配置为级联删除到类别和产品。这就是"多级联路径"；并且是不允许的。将所有FK配置放在一起，可以更轻松地查看哪些关系可以级联。例如

builder.Entity<Company>()
.HasMany(c => c.Products)
.WithOne(e => e.Company)
.OnDelete(DeleteBehavior.Cascade);
builder.Entity<Company>()
.HasMany(p => p.Categories)
.WithOne(p => p.Company)
.HasForeignKey(p => p.CompanyId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<Category>()
.HasMany(c => c.SubCategories)
.WithOne(c => c.ParentCategory)
.HasForeignKey(c => c.ParentCategoryId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<ProductCategory>()
.HasOne(pc => pc.Product)
.WithMany(p => p.Categories)
.HasForeignKey(p => p.ProductId)
.OnDelete(DeleteBehavior.Cascade);
builder.Entity<ProductCategory>()
.HasOne(p => p.Category)
.WithMany(p => p.Products)
.HasForeignKey(p => p.CategoryId)
.OnDelete(DeleteBehavior.Cascade);