EF Core中如何为多对多关系中间表配置软删除及全局查询过滤器

实现前提 #

你当前使用的隐式多对多中间表配置无法直接添加软删除字段和全局过滤器，必须先改为显式中间实体的配置方式，具体实现步骤如下：

步骤1：定义显式中间表实体 #

public class ABRelationship
{
    public int AId { get; set; }
    public int BId { get; set; }
    // 新增软删除标记
    public bool IsDeleted { get; set; }
    
    // 导航属性
    public EntityA A { get; set; }
    public EntityB B { get; set; }
}

步骤2：修改原有实体的导航属性 #

public class EntityA
{
    public int Id {get; set;}
    // 原直接关联EntityB的导航改为关联中间实体
    public ICollection<ABRelationship> ABRelationships { get; set; }
    public bool IsDeleted {get; set;}
}

public class EntityB
{
    public int Id {get; set;}
    // 原直接关联EntityA的导航改为关联中间实体
    public ICollection<ABRelationship> ABRelationships { get; set; }
    public bool IsDeleted {get; set;}
}

步骤3：调整Fluent API配置，添加中间表全局过滤器 #

注意：你原有全局过滤器的逻辑大概率写反了，软删除需要默认过滤掉已标记删除的记录，过滤器规则应为!x.IsDeleted

// 配置中间表复合主键
modelBuilder.Entity<ABRelationship>()
    .HasKey(t => new { t.AId, t.BId });

// 配置中间表和EntityA的关联
modelBuilder.Entity<ABRelationship>()
    .HasOne(t => t.A)
    .WithMany(a => a.ABRelationships)
    .HasForeignKey(t => t.AId);

// 配置中间表和EntityB的关联
modelBuilder.Entity<ABRelationship>()
    .HasOne(t => t.B)
    .WithMany(b => b.ABRelationships)
    .HasForeignKey(t => t.BId);

// 给中间表添加软删除全局过滤器
modelBuilder.Entity<ABRelationship>()
    .HasQueryFilter(t => !t.IsDeleted);

// 指定中间表表名
modelBuilder.Entity<ABRelationship>()
    .ToTable("ABRelationships");

// 原有实体的过滤器修正后如下
modelBuilder.Entity<EntityA>().HasQueryFilter(x => !x.IsDeleted);
modelBuilder.Entity<EntityB>().HasQueryFilter(x => !x.IsDeleted);

步骤4：调整关联删除和查询逻辑 #

关联软删除实现 #

删除关联时不需要直接移除中间表记录，只需将对应中间实体的IsDeleted标记设为true即可：

// 示例：删除Id为1的EntityA和Id为2的EntityB的关联
var rel = await dbContext.ABRelationships
    .FirstOrDefaultAsync(r => r.AId == 1 && r.BId == 2);
if (rel != null)
{
    rel.IsDeleted = true;
    await dbContext.SaveChangesAsync();
}

关联查询实现 #

查询时全局过滤器会自动过滤已软删除的关联，直接通过导航属性查询即可：

// 示例：查询Id为1的EntityA关联的所有未删除EntityB
var entityA = await dbContext.EntityAs
    .Include(a => a.ABRelationships)
        .ThenInclude(r => r.B)
    .FirstOrDefaultAsync(a => a.Id == 1);

var relatedBList = entityA.ABRelationships.Select(r => r.B).ToList();

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