在1到0..1关系中强制执行对象删除的引用完整性 [英] Enforcing referential integrity on object delete in 1-to-0..1 relationship

问题描述

我已经创建了一个使用EF Code First的两个模型的asp.NET MVC Web应用程序，它们具有1到0..1的关系。

  public class ClassA 
 {
 public int Id {get; set;} 
 //其他属性
 public virtual ClassB ClassB {get; set;} 
} 
 
 public class ClassB 
 {
 public int Id {get; set;} 
 //其他属性
} $ b $在我的数据库中，这个成功地创建了两个表，ClassA具有Nullable FK for ClassB的两个表。除了在ClassA记录被删除的情况下，这是非常好的。在这种情况下，任何关联的ClassB记录都留在数据库中。我知道我可以在删除POST方法中手动删除它们：
  [HttpPost，ActionName（Delete）] 
 [ValidateAntiForgeryToken] 
 public ActionResult DeleteConfirmed（int id）
 {
 ClassA classA = context.ClassA.Include（c => c.ClassB）.First（c => c .Id == id）; 
 context.ClassB.Remove（classA.ClassB）; 
 context.ClassA.Remove（classA）; 
 context.SaveChanges（）; 
} 
  
我不太喜欢这种方法，因为它依赖于我不做错误，并假定此方法是记录可能被删除的唯一方法（在某些时候也可能会直接对数据库运行DELETE SQL语句）。我在这里创建的示例很简单，但我的实际应用程序涉及到很多模型和协会，已经相当复杂了。虽然我喜欢认为我是绝对的，但我从经验中学到，我不是，宁愿不依靠自己来确保记录不会成为孤儿=）
 
 
 
如何强制数据库强制引用完整性，以便在ClassA被删除时，ClassA中有ClassA的外键也会被删除？
 
 
 
  SOLVED（kinda） 
 
 
 
根据Gert的建议，我使用Fluent API来确保将正确的实体设置为原则并设置所有关系逐级删除。我确实遇到了一些问题，主要是因为我的数据库中已经有数据。幸运的是，我正处于开发阶段，我可以简单地删除所有的数据;否则，我不知道我会如何修复这个。
 
 
 
首先我试图添加Fluent API和更新数据库。我收到一个错误，部分内容：参数@objname是不明确的，或声明的@objtype（COLUMN）错误，这似乎是由于EF试图更改现有的名称FK列。在这种情况下，我决定使用2个迁移：一个删除现有关系，另一个用于添加新重新配置的关系。我必须在一个相当具体的一系列事件中做到这一点。
 
 
在我的控制器中注释了所有对受影响的关系的引用，以避免错误更新。
 
 
 
在模型中注释出关系。
  public class ClassA 
 {
 public int Id {get; set;} 
 //其他属性
 // public virtual ClassB ClassB {get; set;} 
} 
  
 
 
 
添加迁移和更新 - 数据库以删除现有关系。
 
  
 
 
根据Gert的建议配置OnModelCreating中的新关系，以保存新的关系。
  modelBuilder.Entity< ClassA>（）
 .HasOptional（b => b.ClassB）
 .WithRequired（）
 .Map（m => m.MapKey（ClassB_Id））
 .WillCascadeOnDelete（）; 
  
 
 
 
 
添加迁移和更新数据库以创建新的关系。当我的数据库中存在现有数据时，此步骤失败。如果我没有选择简单地清除所有数据的数据库，我不知道我会如何实现这一点。
 
 
 
 
 
解决方案
在一对一的关联中，您必须考虑哪个实体是原则实体，哪个是依赖于一。如果这个词意味着，依赖的一个，如果没有另一个，就不可能存在。
 
 
 
在你的设计中，原则和依赖是错误的方式： ClassA 是依赖的， ClassB 可以独立生存。这是EF如何解释类​​模型。如果你想要这样做，否则你必须添加一些映射指令。保持类模型不变，这只能通过流畅的API来完成，例如在上下文的 OnModelCreating 中覆盖：
  modelBuilder.Entity< ClassA>（）。HasOptional（a => a.ClassB）
 .WithRequired（）。Map（m => m.MapKey ClassAId））
 .WillCascadeOnDelete（）; 
  
现在将有一个外键 ClassAId ，在 ClassB 表中。级联删除确保当 ClassA 被删除时，其依赖的 ClassB 将自动删除。
 
I have created an asp.NET MVC web app with two models using EF Code First which have a 1-to-0..1 relationship.
public class ClassA
{
    public int Id {get;set;}
    //other properties
    public virtual ClassB ClassB {get;set;}
}

public class ClassB
{
    public int Id {get;set;}
    //other properties
}
In my database, this successfully creates the two tables with ClassA having a Nullable FK for ClassB. This works great, except for the scenario in which a ClassA record is deleted. In that case, any associated ClassB record is left in the database. I know that I can manually remove them in the Delete POST method:
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
    ClassA classA = context.ClassA.Include(c => c.ClassB).First(c => c.Id == id);
    context.ClassB.Remove(classA.ClassB);
    context.ClassA.Remove(classA);
    context.SaveChanges();
}
I don't really like this approach because it relies on me not making a mistake and assumes that this method is the only way that a record might be deleted (there might also be direct DELETE SQL statements run against the database at some point).The example I've created here is simple, but my actual application involves a lot of models and associations and it has gotten quite complicated. While I like to think that I am infallible, I have learned from experience that I am not and would rather not rely on myself to ensure that records don't become orphaned =)

How can I force the database to enforce referential integrity so that when a ClassA is deleted, any ClassB's that have foreign keys in that ClassA are also deleted?

SOLVED (kinda)

As suggested by Gert, I used Fluent API to ensure that the right entity was set as the Principle and to set all of the relationships to cascade on delete. I did run into a few issues though, mostly due to there already being data in my database. Fortunately, I am at a point in development where I can simply delete all of the data; otherwise, I'm not sure how I would have fixed this.

First I tried to just add the Fluent API and update-database. I got an error that read in part: "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong" which seemed to be due to EF trying to change the name of an existing FK column. That being the case, I resolved to use 2 migrations: One to remove existing relationships and another to add the newly reconfigured relationships. I had to do this in a rather specific series of events.

Commented out all references to the affected relationships in my controller to avoid errors while updating.
Commented out the relationship in the Model.
public class ClassA
{
    public int Id {get;set;}
    //other properties
    //public virtual ClassB ClassB {get;set;}
}

Add-Migration and Update-Database to remove the existing relationships.
Undid all changes to the model and controller by uncommenting everything that I commented out in step 1 and 2.
Configured the new relationship in OnModelCreating as suggested by Gert to save the new relationships.
modelBuilder.Entity<ClassA>()
    .HasOptional(b => b.ClassB)
    .WithRequired()
    .Map(m => m.MapKey("ClassB_Id"))
    .WillCascadeOnDelete();

Add-Migration and Update-Database to create the new relationships. This step failed when there was existing data in my database. If I didn't have the option to simple clear the database of all data, I'm not sure how I would have accomplished this.

解决方案
In one-to-one associations you always have to think about which entity is the principle entity and which is the dependent one. The dependent one, as the word implies, can't exist without the other.

In your design the principle and dependent are the wrong way round: ClassA is the dependent, ClassB can live on its own. This is how EF happens to interpret the class model. If you want it to do it otherwise, you'll have to add some mapping instructions. Keeping the class model unaltered, this can only be done by the fluent API, for instance in the context's OnModelCreating override:
modelBuilder.Entity<ClassA>().HasOptional(a => a.ClassB)
    .WithRequired().Map(m => m.MapKey("ClassAId"))
    .WillCascadeOnDelete();
Now there will be a foreign key, ClassAId, in the ClassB table. The cascaded delete makes sure that when a ClassA is deleted, its dependent ClassB is deleted automatically.

                        
这篇关于在1到0..1关系中强制执行对象删除的引用完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！