实体框架核心，DELETE CASCADE和[Required]

问题描述：

我在Entity Framework Core中遇到了一个DELETE CASCADE问题，我似乎找不到很好的解决方案.

I am running into an issue DELETE CASCADE in Entity Framework Core that I can't seem to find a good solution to.

这是我的模型的超级简化版:

Here's a super simplified version of my model:

User {UserID, Name}
Recipe {RecipeID, UserID}
Ingredient {IngredientID, UserID}
RecipeIngredient {RecipeID, IngredientID} *RecipeIngredient is the many-to-many table.

Recipe和Ingredient都将UserID标记为[Required]，并且RecipeIngredient将RecipeID和IngredientID标记为[Required].

Recipe and Ingredient both have UserID marked as [Required], and RecipeIngredient has RecipeID and IngredientID marked as [Required].

问题在于SQL将不会创建数据库，因为存在多个到RecipeIngredient的级联删除路径(引入FOREIGN KEY约束...可能会导致循环或多个级联路径".)

The issue is that SQL will not create the database because there are multiple cascade delete paths to RecipeIngredient ("Introducing FOREIGN KEY constraint... may cause cycles or multiple cascade paths").

所以我被困住了……我已经解决了一些想法，但没有任何效果.

So I'm stuck... I've worked through a few ideas, but nothing has quite worked.

这里有设计解决方案吗?我想保留我的外键，因为强制执行它很有意义，但是如果有设计解决方案，我就可以接受.

Is there a design solution here? I'd like to keep my Foreign Keys since it makes sense to enforce it, but if there is a design solution, I'm open to it.

我的下一个想法是删除所有指向用户的FK-我必须在DELETE期间通过C#代码强制执行参照完整性，并且可以在CREATE期间使用[Required]强制输入数据.出现的问题-[必需]创建一个FK，并添加"ON DELETE CASCADE"，这使我们重新回到多级联删除路径问题中.我真的很想保留[Required]，因为它与Razor页面的集成十分流畅，客户端验证和错误等.

My next idea was to remove all FKs pointing back to User - I'd have to enforce the referential integrity during DELETE via my C# code and I could enforce the entry of the data during CREATE using [Required]. The problem with that - [Required] creates a FK, and adds "ON DELETE CASCADE," which puts us right back into the multiple cascade delete path problem. I'd really like to keep [Required] because of the slick integration with Razor pages, client side validation and errors, etc.

下一个想法，在OnModelCreating(...)中将级联行为设置为SetNull:

Next idea, set the cascade behavior to SetNull in OnModelCreating(...):

modelBuilder.Entity(). HasOne(i => i.User) .WithMany(u => u.Ingredients) .OnDelete(DeleteBehavior.SetNull);

modelBuilder.Entity() .HasOne(r => r.Source) .WithMany(s => s.Recipes) .OnDelete(DeleteBehavior.SetNull);

但这会引发异常，因为即使我在成分和配方"中的属性设置为Nullable:

But this throws an exception, because even though my property in Ingredient and Recipe is set to Nullable:

[Required(ErrorMessage = "User ID is required.")] 
public Nullable<int> UserID { get; set; }

...由于[Required]属性，EF仍将其创建为NOT NULL数据库列.

... EF still creates it as a NOT NULL database column due to the [Required] attribute.

对此有什么解决方案?据我所知，我应该将所有FK删除给用户，并尝试在CREATE上将其作为必填字段来实施，但是我看不到要使用数据注释来做到这一点的方法.将这种逻辑保留在我的代码优先模型中.

What's the solution for this? As far as I can tell, I should just remove all FKs to User, and try to enforce it as a required field on CREATE, but I don't see a way to do that with data annotations, which I'd like to do to keep this logic in my code first model.

答

我建议禁用级联删除，因为通常开发人员希望非常小心删除哪些数据，并且禁用将对您的数据进行更精细的控制关于删除.

I'd recommend to disable cascade deletes as normally developers want to be very careful with what data is deleted and disabling will give you more fine grain control over your data in regards to deletion.

您可以在Context.cs类中以以下方式在OnModelCreation(DbModelBuilder modelBuilder)替代中进行操作:

You can do so in a OnModelCreation(DbModelBuilder modelBuilder) override as follows in your Context.cs class:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();
  modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();
}

在EF Core中，Conventions类不可用，因此您需要遍历实体类型并限制删除以实现所需的效果:

In EF Core, the Conventions class is not available, so you'll need to iterate through your entity types and restrict deletion to achieve the desired affect:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);

  foreach (var relationship in builder.Model.GetEntityTypes().SelectMany(e => e.GetForeignKeys()))
  {
        relationship.DeleteBehavior = DeleteBehavior.Restrict;
  }
}

实体框架核心，DELETE CASCADE和[Required]

相关推荐