c# - FK 约束可能会导致循环或多个级联路径

Question

为什么我的初始 update-database 失败了，我需要在我的数据库表类中更改什么才能使其正常工作？

当然，我可以将迁移脚本中的onDelete: ReferentialAction.Cascade 更改为onDelete: ReferentialAction.NoAction，但这样我的应用程序就会面临其他问题。我正在寻求一种无需编辑 add-migration 生成的迁移脚本的解决方案。换句话说，我愿意更改我的数据库架构。

我想要的行为是当我删除一个 Product 时，关联的 ProductPropertyOptionForProducts 也被删除，但不是相反，而不是 ProductPropertyOption 与 ProductPropertyOptionForProducts 关联。

这是迁移输出错误信息:

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

导致错误的生成的SQL命令:

CREATE TABLE[PropertyOptionsForProducts] (
[Id] int NOT NULL IDENTITY,
[CustomNumberValue] decimal (18, 2) NOT NULL,
[CustomRangeFrom] decimal (18, 2) NOT NULL,
[CustomRangeTo] decimal (18, 2) NOT NULL,
[CustomStringValue] nvarchar(max) NULL,
[ProductId] int NOT NULL,
[ProductPropertyId] int NOT NULL,
[ProductPropertyOptionId] int NOT NULL,
CONSTRAINT[PK_PropertyOptionsForProducts] PRIMARY KEY([Id]),
CONSTRAINT[FK_PropertyOptionsForProducts_Products_ProductId]
    FOREIGN KEY([ProductId])
    REFERENCES[Products] ([Id]) ON DELETE CASCADE,
CONSTRAINT[FK_PropertyOptionsForProducts_ProductPropertyOptions_ProductPropertyOptionId]
    FOREIGN KEY([ProductPropertyOptionId])
    REFERENCES[ProductPropertyOptions] ([Id]) ON DELETE CASCADE
);

类:

public class ProductPropertyOption
{
    public int Id { get; set; }
    public int ProductPropertyId { get; set; }
    // some more properties
    public ProductProperty Property { get; set; }
    public ICollection<PropertyOptionForProduct> PropertyOptionForProducts { get; set; }
}


public class PropertyOptionForProduct
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int ProductPropertyId { get; set; }
    public int ProductPropertyOptionId { get; set; }
    // some more properties
    public Product Product { get; set; }
    public ProductPropertyOption ProductPropertyOption { get; set; }
}


public class Product
{
    public int Id { get; set; }
    public bool Published { get; set; }
    public int ProductGroupId { get; set; }
    public int ProductGroupSortOrder { get; set; }
    // some more properties
    public int ProductTypeId { get; set; }

    public ICollection<ProductImage> Images { get; set; }
    public ICollection<PropertyOptionForProduct> ProductPropertyOptionForProducts { get; set; }
    public ICollection<IdentifierForProduct> IdentifierForProducts { get; set; }
    public ProductType Type { get; set; }
    public ICollection<FrontPageProduct> InFrontPages { get; set; }
    public ICollection<ProductInCategory> InCategories { get; set; }
}


public class ProductType
{
    public int Id { get; set; }
    public string Title { get; set; }
    public List<ProductIdentifierInType> Identifiers { get; set; }
    public List<ProductProperty> Properties { get; set; }
    public ICollection<Product> Products { get; set; }
}


public class ProductProperty
{
    public int Id { get; set; }
    public int ProductTypeId { get; set; }
    // some more properties
    public List<ProductPropertyOption> Options { get; set; }
    public ProductType ProductType { get; set; }
}

说明的数据库(产品和类别部分):

Answer 1

关系图清楚地显示了从 ProductType 到 PropertyOptionForProduct 的多重级联路径:

(1) ProductType -> Product -> PropertyOptionForProduct

(2) ProductType -> ProductProperty -> ProductPropertyOption -> PropertyOptionForProduct

唯一的解决方案是通过关闭至少一个关系的级联删除来打破级联路径，然后手动处理主体实体删除。

可能最简单的方法是破坏一些根路径，例如 ProductType -> ProductProperty:

modelBuilder.Entity<ProductType>()
    .HasMany(e => e.Properties)
    .WithOne(e => e.ProductType)
    .OnDelete(DeleteBehavior.Restrict);

然后当您需要删除 ProductType 而不是“正常”时:

db.Remove(db.Set<ProductType>().Single(e => e.Id == id));
db.SaveChanges();

你必须先删除相关的Properties:

var productType = db.Set<ProductType>().Include(e => e.Properties).Single(e => e.Id == id);
db.RemoveRange(productType.Properties);
db.Remove(productType);
db.SaveChanges();