如何修复Entity Framework Core多对多关系中的级联删除错误

我开发的应用中存在可独立存在的Member和Address数据模型，当Member更换新地址时，我不想删除Address数据，仅需删除多对多关联表MemberAddressJoin中的关系记录。但执行update-database命令构建数据库时，出现如下错误：

Failed executing DbCommand (13ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE [AddressMember] (
[AddressesId] int NOT NULL,
[MembersId] int NOT NULL,
CONSTRAINT [PK_AddressMember] PRIMARY KEY ([AddressesId], [MembersId]),
CONSTRAINT [FK_AddressMember_Addresses_AddressesId] FOREIGN KEY ([AddressesId]) REFERENCES [Addresses] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_AddressMember_Members_MembersId] FOREIGN KEY ([MembersId]) REFERENCES [Members] ([Id]) ON DELETE CASCADE
);

请问如何配置Entity Framework Core，实现仅删除MemberAddressJoin表中的记录，而保留Addresses和Members表的数据？

以下是当前的多对多关系数据模型：

Member 模型 #

using Microsoft.AspNetCore.Identity;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{    
    public class Member
    {         
        [Key]
        public int Id { get; set; }
       
        [PersonalData]
        public string FirstName { get; set; }

        [PersonalData]
        public string MiddleName { get; set; }
        
        [PersonalData]
        public string LastName { get; set; }

        [PersonalData]
        public string PreferredName { get; set; }
        
        [PersonalData]
        public string Title { get; set; }

        [PersonalData]
        public int Age { get; set; }       

        // 多对多导航属性
        public virtual ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }                
        // 多对多跳过导航属性
        public virtual ICollection<Address> Addresses { get; set; }
    }
}

MemberAddressJoin 关联模型 #

using System.ComponentModel.DataAnnotations;

namespace Application.Data.Models
{
    public class MemberAddressJoin
    {
        [Key] 
        public int Id { get; set; }
        
        // 多对多外键
        public int? MemberId {get; set; }
        // 多对多导航属性
        public virtual Member Member { get; set; }

        // 多对多外键
        public int? AddressId { get; set; }
        // 多对多导航属性
        public virtual Address Address { get; set; }
    }
}

Address 模型 #

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Application.Data.Models
{
    [Table("Addresses")]
    public class Address
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Line1 { get; set; }
       
        public string Line2 { get; set; }
        
        public string Line3 { get; set; }

        [Required]
        public string City { get; set; }

        [Required]
        // 多对一关联，Address为子实体
        public int CountyId { get; set; }
        public virtual County County { get; set; }
        
        [Required]
        public string Zipcode { get; set; }       

        [Required]
        public bool IsMailingAddress { get; set; }

        [Required]
        public bool IsResidenceAddress { get; set; }

        // 一对一关联，Address为父实体
        public virtual Location Location { get; set; }

        // 多对多导航属性
        public ICollection<MemberAddressJoin> MemberAddressJoins { get; set; }
        // 多对多跳过导航属性
        public virtual ICollection<Member> Members { get; set; }


        public override bool Equals(object o)
        {
            var other = o as Location;
            return other?.Name == Name;
        }

        public override int GetHashCode() => Name?.GetHashCode() ?? 0;

        public override string ToString() => Name;


        [NotMapped]
        public virtual string Name
        {
            get
            {
                string name = string.Empty;

                if (!string.IsNullOrEmpty(Line1))
                    name = Line1;

                if (!string.IsNullOrEmpty(Line2))
                    name = $"{name}, {Line2}";

                if (!string.IsNullOrEmpty(Line3))
                    name = $"{name}, {Line3}";

                if (!string.IsNullOrEmpty(City))
                    name = $"{name}, {City}";

                if (County.State is not null)
                    name = $"{name}, {County.State.Name}";

                if (!string.IsNullOrEmpty(Zipcode))
                    name = $"{name}, {Zipcode}";

                return name;
            }
        }

    }
}

问题根源是EF Core自动生成的多对多关联表配置了ON DELETE CASCADE，这会导致删除Member或Address时，级联删除关联记录甚至关联实体。要实现仅删除关联表记录，需手动配置关系并修改外键的删除行为。

步骤1：修正关联实体的外键定义 #

多对多关联记录必须同时关联Member和Address，因此外键不应为可空类型，将MemberAddressJoin中的int?改为int：

public class MemberAddressJoin
{
    [Key] 
    public int Id { get; set; }
    
    // 多对多外键
    public int MemberId {get; set; }
    // 多对多导航属性
    public virtual Member Member { get; set; }

    // 多对多外键
    public int AddressId { get; set; }
    // 多对多导航属性
    public virtual Address Address { get; set; }
}

步骤2：在DbContext中配置多对多关系 #

在你的DbContext的OnModelCreating方法中，手动配置关联关系，将外键的删除行为设置为Restrict（SQL Server推荐）或NoAction，禁止级联删除：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置Member与MemberAddressJoin的关系
    modelBuilder.Entity<MemberAddressJoin>()
        .HasOne(ma => ma.Member)
        .WithMany(m => m.MemberAddressJoins)
        .HasForeignKey(ma => ma.MemberId)
        .OnDelete(DeleteBehavior.Restrict);

    // 配置Address与MemberAddressJoin的关系
    modelBuilder.Entity<MemberAddressJoin>()
        .HasOne(ma => ma.Address)
        .WithMany(a => a.MemberAddressJoins)
        .HasForeignKey(ma => ma.AddressId)
        .OnDelete(DeleteBehavior.Restrict);

    // 若使用跳过导航属性，可补充配置
    modelBuilder.Entity<Member>()
        .HasMany(m => m.Addresses)
        .WithMany(a => a.Members)
        .UsingEntity<MemberAddressJoin>(
            j => j.HasOne(ma => ma.Address).WithMany().HasForeignKey(ma => ma.AddressId).OnDelete(DeleteBehavior.Restrict),
            j => j.HasOne(ma => ma.Member).WithMany().HasForeignKey(ma => ma.MemberId).OnDelete(DeleteBehavior.Restrict)
        );
}

步骤3：更新迁移并应用数据库变更 #

1. 删除之前生成的错误迁移文件（若存在）
2. 重新生成迁移：Add-Migration FixManyToManyDeleteBehavior
3. 应用迁移：update-database

配置完成后，删除Member或Address时，数据库不会自动级联删除关联的实体或关联表记录。如需解除Member与Address的关联，只需手动删除对应的MemberAddressJoin实体即可，Member和Address数据会保留。

内容的提问来源于stack exchange，提问作者Tyson Gibby