Entity Framework一对多关联中添加服务时重复创建地点问题

基于.NET 6和Entity Framework构建排队系统，终端用户可创建地点并为其添加服务。但创建地点后，无法为已添加的地点新增服务，因为添加服务的函数会尝试创建一个新地点。

QueueLocation 实体 #

public class QueueLocation
{
    public int Id { get; set; }
    public locationCity? City { get; set; }
    public string? Hospital { get; set; }
    public string? AddetionalDetails { get; set; }
    public string? ImageUrl { get; set; }
    public string? CreatedBy { get; set; }
    public DateTime CreatedAt { get; set; }
    public string? UpdatedBy { get; set; }
    public DateTime UpdatedAt { get; set; }

    public int QueueMachineId { get; set; }
    public ICollection<QueueMachine>? QueueMachines { get; set; }

    public int QueueServiceId { get; set; }
    public ICollection<QueueService>? QueueServices { get; set; }
}

QueueService 实体 #

public class QueueService
{
    public int Id { get; set; }
    public string? ServiceNameEnglish { get; set; }
    public string? ServiceNameArabic { get; set; }
    public string? ServiceDescription { get; set; }
    public string? CreatedBy { get; set; }
    public string? ModifiedBy { get; set; }
    public string? ServiceCode { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ModifiedAt { get; set; }
    public QueueLocation? QueueLocation { get; set; }
    
    public ICollection<QueueMachine>? QueueMachines  {get;set;}

    public int QueueTicketId { get; set; }
    public ICollection<QueueTicket>? queueTickets { get; set; }
}

DataContext #

public class DataContext : IdentityDbContext <Account,IdentityRole,string>
{
    public DataContext(DbContextOptions options) : base(options)
    {
    }

    public DbSet<QueueLocation> QueueLocation { get; set; }
    public DbSet<QueueService> QueueServices { get; set; }
    public DbSet<QueueMachine> QueueMachines { get; set; }
    public DbSet<QueueTicket> QueueTicket { get; set; }
    public DbSet<Account> accounts { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<QueueService>()
            .HasMany(e => e.QueueMachines)
            .WithMany(e => e.QueueServices);
    }

}

新建地点方法 #

public async Task<bool> CreateNewLocation(QueueLocation location)
{
    await _context.QueueLocation.AddAsync(location);

    return await _context.SaveChangesAsync() > 0 ? true: throw new BadRequestExceptions("Somthing Went Wrong");
}

新建服务方法 #

await _context.QueueServices.AddAsync(Service);

return await _context.SaveChangesAsync() > 0 ? true : throw new BadRequestExceptions("Somthing Went Wrong");

问题根源 #

1. 实体关系配置错误：QueueLocation中定义的int QueueServiceId会让EF误认为地点依赖单个服务，违背了"一个地点对应多个服务"的一对多关系逻辑，导致关联混乱。
2. 服务关联地点方式错误：添加服务时若传递的QueueLocation是未被上下文跟踪的新实例，EF会将其判定为新地点执行插入操作。

修复步骤 #

1. 修正实体类关系 #

修改QueueLocation，移除冗余的外键属性：

public class QueueLocation
{
    public int Id { get; set; }
    public locationCity? City { get; set; }
    public string? Hospital { get; set; }
    public string? AddetionalDetails { get; set; }
    public string? ImageUrl { get; set; }
    public string? CreatedBy { get; set; }
    public DateTime CreatedAt { get; set; }
    public string? UpdatedBy { get; set; }
    public DateTime UpdatedAt { get; set; }

    public ICollection<QueueMachine>? QueueMachines { get; set; }

    // 移除冗余的QueueServiceId
    public ICollection<QueueService>? QueueServices { get; set; }
}

修改QueueService，添加显式外键属性明确关联：

public class QueueService
{
    public int Id { get; set; }
    public string? ServiceNameEnglish { get; set; }
    public string? ServiceNameArabic { get; set; }
    public string? ServiceDescription { get; set; }
    public string? CreatedBy { get; set; }
    public string? ModifiedBy { get; set; }
    public string? ServiceCode { get; set; }
    public DateTime CreatedAt { get; set; }
    public DateTime ModifiedAt { get; set; }
    
    // 添加外键属性，关联QueueLocation的Id
    public int QueueLocationId { get; set; }
    public QueueLocation? QueueLocation { get; set; }
    
    public ICollection<QueueMachine>? QueueMachines  {get;set;}

    // 移除冗余的QueueTicketId（多对一关系应在QueueTicket中定义外键）
    public ICollection<QueueTicket>? queueTickets { get; set; }
}

2. 明确EF关系配置（可选） #

在OnModelCreating中显式配置一对多关系，增强可读性：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    
    modelBuilder.Entity<QueueService>()
        .HasMany(e => e.QueueMachines)
        .WithMany(e => e.QueueServices);
        
    // 配置地点与服务的一对多关系
    modelBuilder.Entity<QueueLocation>()
        .HasMany(l => l.QueueServices)
        .WithOne(s => s.QueueLocation)
        .HasForeignKey(s => s.QueueLocationId)
        .OnDelete(DeleteBehavior.Cascade); // 可根据业务需求调整删除行为
}

3. 修改添加服务的方法 #

确保关联现有地点而非新实例：

public async Task<bool> AddServiceToLocation(QueueService service, int locationId)
{
    // 验证目标地点存在
    var existingLocation = await _context.QueueLocation.FindAsync(locationId);
    if (existingLocation == null)
        throw new BadRequestExceptions("指定地点不存在");
        
    // 直接设置外键，无需传递完整地点实体
    service.QueueLocationId = locationId;
    
    await _context.QueueServices.AddAsync(service);
    return await _context.SaveChangesAsync() > 0;
}

若前端已传递QueueLocationId，可简化为：

public async Task<bool> AddService(QueueService service)
{
    if (!await _context.QueueLocation.AnyAsync(l => l.Id == service.QueueLocationId))
        throw new BadRequestExceptions("指定地点不存在");
        
    await _context.QueueServices.AddAsync(service);
    return await _context.SaveChangesAsync() > 0;
}

4. 更新数据库结构 #

执行EF迁移命令同步数据库：

Add-Migration FixLocationServiceRelationship
Update-Database

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