不完整的EF代码级联删除多对多关系 [英] Incomplete EF code-first cascade delete on many to many relationship

问题描述

我有一个 PhoneNumber 实体，我想在多个实体中引用。例如，联系人实体具有许多 PhoneNumbers，以及业务实体与 one PhoneNumber。

I have a PhoneNumber entity which I'd like to reference across multiple entities. For example, a Contact entity that has many PhoneNumbers, and a Business entity with one PhoneNumber.

public class PhoneNumber
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Number { get; set; }
}

public class Contact
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    public virtual ICollection<PhoneNumber> PhoneNumbers { get; set; }
}

public class Business
{
    [Key, Column(Order = 0), DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [ForeignKey("PhoneNumber")]
    public int PhoneNumberId { get; set; }
    public virtual PhoneNumber PhoneNumber { get; set; }        
}

我已设置联系人和业务，以便它们具有单向导航属性。此外，电话号码是可选的。我还为联系人设置了许多关系，以防止EF在添加迁移时添加 Contact_Id 列。映射如下（注意 WithMany（）被使用，因为 PhoneNumber 没有nav属性回到联系人）

I've setup Contact and Business so that they have one way navigation properties. Also, the phone numbers are optional. I've also setup the many relationship for Contact to prevent EF from adding a Contact_Id column when I add a migration. The mapping is as follows (note WithMany() is used since PhoneNumber doesn't have a nav property back to Contact):

modelBuilder.Entity<Contact>().HasMany(r => r.PhoneNumbers).WithMany()
            .Map(x => x.MapLeftKey("ContactId").MapRightKey("PhoneId"));

当我添加一个联系人与多个手机数字增加了。有联系人， PhoneNumbers 表和 ContactPhoneNumbers 链接表。

When I add a Contact with multiple phone numbers it gets added fine. There are records for the Contact, the PhoneNumbers table, and the ContactPhoneNumbers link table.

然而，我正在努力的问题是删除联系人时。 EF正确删除 ContactPhoneNumbers 链接表和联系人条目中的条目，但不会删除条目从 PhoneNumbers 表。我看到使用 modelBuilder 的映射示例，其中使用了 WillCascadeOnDelete（true），但该选项不可用当使用 WithMany（）。

However, the issue I'm struggling with is when I delete a contact. EF correctly deletes the entry in the ContactPhoneNumbers link table, and the Contact entry, but it doesn't delete the entries from the PhoneNumbers table. I've seen examples of mapping with modelBuilder where WillCascadeOnDelete(true) is used, but that option isn't available when using WithMany().

我需要做什么才能使这种级联删除工作正常？这个设置有可能吗？或者，我需要为每个实体（联系人和业务）分别设置一个单独的 PhoneNumbers 表，以设置相应PhoneNumber表使用FK的关系（例如， Contact_Id ）？

What do I need to do to get that type of cascade delete working correctly? Is it possible with this setup? Or will I need to have a separate PhoneNumbers table for each entity (Contact and Business) to setup a relationship where the respective PhoneNumber table uses a FK (eg., Contact_Id)?

我对EF很新，所以欢迎任何建议。我可能会完全错误。

I'm fairly new to EF so any suggestions are welcome. I might be going about this entirely wrong.

编辑：这里是相关的迁移代码...

here's the related migration code...

CreateTable(
    "dbo.PhoneNumbers",
    c => new
        {
            Id = c.Int(nullable: false, identity: true),
            Number = c.String()
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.Contacts",
    c => new
        {
            Id = c.Int(nullable: false, identity: true)
        })
    .PrimaryKey(t => t.Id);

CreateTable(
    "dbo.ContactPhoneNumbers",
    c => new
        {
            ContactId = c.Int(nullable: false),
            PhoneId = c.Int(nullable: false),
        })
    .PrimaryKey(t => new { t.ContactId, t.PhoneId })
    .ForeignKey("dbo.Contacts", t => t.ContactId, cascadeDelete: true)
    .ForeignKey("dbo.PhoneNumbers", t => t.PhoneId, cascadeDelete: true)
    .Index(t => t.ContactId)
    .Index(t => t.PhoneId);

CreateTable(
    "dbo.Business",
    c => new
        {
            Id = c.Int(nullable: false),
            PhoneNumberId = c.Int(nullable: false)
        })
    .PrimaryKey(t => t.Id)
    .ForeignKey("dbo.PhoneNumbers", t => t.PhoneNumberId, cascadeDelete: true)
    .Index(t => t.Id)
    .Index(t => t.PhoneNumberId);

推荐答案

为了级联删除，要进行级联必须有一个外键返回到被删除的记录。所以在你的例子中，你删除一个联系人记录。因为他们是从ContactPhoneNumber到Contact的外键，所以级联起作用。由于PhoneNumber中没有外键到ContactPhoneNumber（外键按其他方式）级联不会继续。

In order for the cascading delete to work records that are going to be cascaded must have a foreign key back to the record being deleted. So in your example, You delete a Contact record. Because their is a foreign key from ContactPhoneNumber to Contact, the cascade works. Since there is no foreign key from PhoneNumber to ContactPhoneNumber, (the foreign key goes the other way) the cascade does not continue.

这是因为您将关系定义为多对多关系。如果您想要尝试在您的模型上执行级联删除，如果您希望删除一个ContactPhoneNumber，然后删除其关联的PhoneNumbers，那么现在可能会有其他没有有效PhoneNumber的ContactPhoneNumbers（因为可以许多ContactPhoneNumbers到一个PhoneNumber）。现在这些将需要删除，这个过程将继续下去。数据库不喜欢这个循环级联。

This is because you defined the relationship as a many to many. If you think about trying to perform a cascading delete on your model as you would like, If a ContactPhoneNumber is deleted and then its associated PhoneNumbers are deleted, there could now be other ContactPhoneNumbers that that don't have a valid PhoneNumber (Because there can be many ContactPhoneNumbers to one PhoneNumber). Now these would need to be deleted and this process would continue. Databases don't like this cyclical cascading.

我不清楚为什么你需要多对多的关系，如果你真的需要它，你将无法执行删除级联。如果您可以建立关系：

It is not entirely clear to me why you need the many to many relationship, If you truly need it you will not be able to perform a cascade on delete. If you can make your relationship:

1联系人 - * ContactPhoneNumber 1- * PhoneNumber，那么您可以将级联配置为您想要的工作。

1 Contact - * ContactPhoneNumber 1- * PhoneNumber, then you could configure the cascade to work like you want it too.

这篇关于不完整的EF代码级联删除多对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！