实体框架核心代码优先：多对多关系的级联删除 [英] Entity Framework Core Code-First: Cascade delete on a many-to-many relationship

问题描述

我正在使用Entity-Framework Core（版本 EntityFramework.Core： 7.0.0-rc1-final ）进行ASP.NET MVC 6项目），并以SQL Server 2012 Express数据库为后盾。

I'm working on an ASP.NET MVC 6 project with Entity-Framework Core (version "EntityFramework.Core": "7.0.0-rc1-final") backed by a SQL Server 2012 express DB.

我需要为人实体和 Address 实体。
根据此指南，我使用 PersonAddress join-table实体对其建模，因为这样我可以存储一些额外的信息。

I need to model a many-to-many relationship between a Person entity and an Address entity. As per this guide I modeled it with a PersonAddressjoin-table entity, because this way I can store some extra info.

我的目标目标是通过以下方式设置我的系统：

My goal is to set-up my system this way:

• 如果删除了 Person 实例，则必须删除所有相关的 PersonAddress 实例。仅当它们引用的所有 Address 实例与其他 PersonAddress 实例无关时，也必须删除它们。 / li>
  
• 如果删除了 PersonAddress 实例，则必须关联与其相关的 Address 实例仅当它与其他 PersonAddress 实例无关时才被删除。所有 Person 实例都必须存在。


• 如果删除 Address 实例，必须删除所有相关的 PersonAddress 实例。所有 Person 实例都必须存在。

• If a Person instance is deleted, all the related PersonAddress instances must be deleted. All the Address instances they reference to must be deleted too, only if they are not related to other PersonAddress instances.
• If a PersonAddress instance is deleted, the Address instance it relates to must be deleted only if it is not related to other PersonAddress instances. All Person instances must live.
• If an Address instance is deleted, all the related PersonAddress instances must be deleted. All Person instances must live.

我认为大部分工作必须在 Person 和 Address 之间的多对多关系中完成的，但是我希望也能写一些逻辑。我将把这一部分排除在这个问题之外。我感兴趣的是如何配置我的多对多关系。

I think most of the work must be done in the many-to-many relationship between Person and Address, but I expect to write some logic too. I will leave this part out of this question. What I'm interested in is how to configure my many-to-many relationship.

这是当前的情况。

这是 Person 实体。请注意，该实体与其他二级实体有一对多的关系。

This is the Person entity. Please note that this entity has got one-to-many relationships with other secondary entities.

public class Person
{
    public int Id {get; set; } //PK
    public virtual ICollection<Telephone> Telephones { get; set; } //navigation property
    public virtual ICollection<PersonAddress> Addresses { get; set; } //navigation property for the many-to-many relationship
}

地址实体。

public class Address
{
    public int Id { get; set; } //PK
    public int CityId { get; set; } //FK
    public City City { get; set; } //navigation property
    public virtual ICollection<PersonAddress> People { get; set; } //navigation property
}

这是 PersonAddress 实体。

public class PersonAddress
{
    //PK: PersonId + AddressId
    public int PersonId { get; set; } //FK
    public Person Person {get; set; } //navigation property
    public int AddressId { get; set; } //FK
    public Address Address {get; set; } //navigation property
    //other info removed for simplicity
}

这是 DatabaseContext 实体，其中描述了所有关系。

This is the DatabaseContext entity, where all the relationships are described.

public class DataBaseContext : DbContext
{
    public DbSet<Person> People { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnModelCreating(ModelBuilder builder)
    {            
        //All the telephones must be deleteded alongside a Person.
        //Deleting a telephone must not delete the person it refers to.
        builder.Entity<Person>()
            .HasMany(p => p.Telephones)
            .WithOne(p => p.Person);

        //I don't want to delete the City when I delete an Address
        builder.Entity<Address>()
            .HasOne(p => p.City)
            .WithMany(p => p.Addresses)
            .IsRequired().OnDelete(Microsoft.Data.Entity.Metadata.DeleteBehavior.Restrict);

        //PK for the join entity
        builder.Entity<PersonAddress>()
            .HasKey(x => new { x.AddressId, x.PersonId });

        builder.Entity<PersonAddress>()
            .HasOne(p => p.Person)
            .WithMany(p => p.Addresses)
            .IsRequired();

        builder.Entity<PersonAddress>()
            .HasOne(p => p.Address)
            .WithMany(p => p.People)
            .IsRequired();
    }
}

两者电话和 City 实体。

这是删除代码一个 Person 。

Person person = await _context.People.SingleAsync(m => m.Id == id);
try
{
    _context.People.Remove(person);
    await _context.SaveChangesAsync();
}
catch (Exception ex)
{

}

我的阅读避免使用 .Include（）将让数据库处理最终的 CASCADE 删除。很抱歉，但是我不记得这个概念在何处得到澄清。

As for my readings avoiding .Include() will let the DB take care of the eventual CASCADE deletes. I'm sorry but I don't remember the SO question where this concept was clarified.

如果运行此代码，则可以使用此解决方法。当我想使用上述代码测试删除 Person 实体时，出现此异常：

If I run this code I can seed the DB using this workaround. When I want to test-deleting a Person entity with the above code, I get this exception:

The DELETE statement conflicted with the REFERENCE constraint "FK_PersonAddress_Person_PersonId". The conflict occurred in database "<dbName>", table "<dbo>.PersonAddress", column 'PersonId'.
The statement has been terminated.

我在 DatabaseContext.OnModelCreating 方法没有任何运气。

I tested several relationship setups in the DatabaseContext.OnModelCreating method without any luck.

最后，这是我的问题。 目标，我应该如何配置我的多对多关系，以便从应用程序中正确删除人及其相关实体

Finally, here's my question. How should I configure my many-to-many relationship in order to correctly delete a Person and its related entities from my application, according to the goal described before?

谢谢大家。

推荐答案

首先我明白了您已通过 DeleteBehavior.Restrict 设置了城市和地址关系，并说：
' //我不想在删除地址时删除城市'。

但是您不需要在此限制，因为即使使用 DeleteBehavior.Cascade，也是如此。 城市不会被删除。
您从错误的方向看。
此处 Cascade 的作用是删除城市后，属于该城市的所有地址也会被删除。
而且这种行为是合乎逻辑的。

First I see you have set City and Address relationship with DeleteBehavior.Restrict and you say: '//I don't want to delete the City when I delete an Address'.
But you don't need Restrict here, because even with DeleteBehavior.Cascade City will not be deleted. You are looking it from the wrong side. What Cascade here does is when a City is deleted all addresses belonging to it are also deleted. And that behavour is logical.

第二，您的多对多关系很好。
删除Person时，由于级联，其来自PersonAddress Table的链接将被自动删除。
并且，如果您还想删除仅与该人相关的地址，则必须手动进行。
实际上，您必须先删除那些地址，然后才能删除Person以了解要删除的内容。

因此，逻辑应为：

1.查询PersonAddress的所有记录其中 PersonId = person.Id ;

2.其中仅接受在PersonAddress表中仅出现一次AddressId的地址，并将其从Person表中删除。

3.现在删除Person。

Secondly your many-to-many relationship is fine. When deleting Person its links from PersonAddress Table will automatically be deleted because of Cascade. And if you want also to delete Addresses that were connected only to that Person you will have to do it manually. You actually have to delete those Addresses before deleting Person is order to know what to delete.
So logic should be following:
1. Query through all record of PersonAddress where PersonId = person.Id;
2. Of those take only ones that have single occurance of AddressId in PersonAddress table, and delete them from Person table.
3. Now delete the Person.

您可以直接在代码中执行此操作，或者如果您希望数据库为此执行操作您可以使用以下功能为步骤2创建触发器：
要删除PersonAddress中的行时，请检查该PersonAddress表中是否不再有具有相同AddressId的行，在这种情况下，请将其从Address表中删除。

You could do this in code directly, or if you want database to do it for you, trigger could be created for step 2 with function: When row from PersonAddress is about to be deleted check if there are no more rows with same AddressId in that PersonAddress table in which case delete it from Address table.

此处提供更多信息：

如何级联删除多对多表

如何在SQL Server中使用INNER JOIN从多个表中删除

More info here:
How to cascade delete over many to many table
How do I delete from multiple tables using INNER JOIN in SQL server

这篇关于实体框架核心代码优先：多对多关系的级联删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！