EF Core中如何映射SPA项目的预订实体关系？

问题描述 #

我正在用ASP.NET Core API开发一个SPA项目，用户可在网站上预订健康项目，无需付款。每个健康项目对应不同时长（即客人在水疗中心参与项目的时间），每个时长包含两种房间类型，搭配DurationType枚举（代表周/晚）。每个房间有独立的RoomType、价格和折扣，例如单人间、双人间。

核心需求：用EF Core在数据库中映射预订关系，预订需关联一个健康项目、一个时长及一个房间，实体层级为：健康项目包含多个时长，时长包含多个房间。

当前实体代码 #

HealthProgram #

public class HealthProgram : BaseEntity
{
    public string Name { get; set; }
    public string Description { get; set; }
    public string EXPECT { get; set; }
    public string BENEFITS { get; set; }
    public string PACKAGE { get; set; }
    public string NameAR { get; set; }
    public string DescriptionAr { get; set; }
    public string EXPECTAr { get; set; }
    public string BENEFITSAR { get; set; }
    public string PACKAGEAr { get; set; }
    public virtual ICollection<Duration> duration { get; set; }
    public virtual ICollection<ProgramImages> ProgramImages { get; set; }
}

Duration #

public class Duration : BaseEntity
{
    public DurationType DurationType { get; set; }
    public int DurationNumbers { get; set; }
    public int HealthProgramId { get; set; }
    public virtual HealthProgram program { get; set; }
    public virtual ICollection<Room> rooms { get; set; } 
}

Room #

public class Room : BaseEntity
{
    public RoomType Type { get; set; }
    public decimal Price { get; set; }
    public decimal Discount { get; set; }
    public int durationId { get; set; }
    public virtual Duration duration { get; set; }
}

Gust（建议修正为Guest） #

public class Gust : BaseEntity
{
    public string FristName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public int Height { get; set; }
    public int Weight { get; set; }
    public bool cardiovascular_disease { get; set; }
    public bool taking_medications { get; set; }
    public bool practicing_sport { get; set; }
    public bool allergies { get; set; }
    public bool Any_illness { get; set; }
    public int BookedProgramId { get; set; }
    public virtual Booking Booking { get; set; }
}

BookedProgram #

public class BookedProgram : BaseEntity
{
    public string ProgramName { get; set; }
    public string ProgramNameAr { get; set; }
    public string duration { get; set; }
    public RoomType Room { get; set; }
    public int RoomId { get; set; }
    [Column(TypeName = "decimal(18,4)")] 
    public decimal Price { get; set; }
    public virtual Booking booking { get; set; }
}

Booking #

public class Booking : BaseEntity
{
    public int GistId { get; set; }
    public virtual Gust gust { get; set; }
    public int BookedProgramId { get; set; }
    public virtual BookedProgram BookedProgram { get; set; }
    public decimal TotalPrice { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

优化后的映射方案 #

一、实体模型调整 #

当前实体存在冗余字段（如BookedProgram中存储的ProgramName、duration），且部分关联关系不符合业务逻辑，调整后实体如下：

修正后的Guest实体

public class Guest : BaseEntity
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int Age { get; set; }
    public string PhoneNumber { get; set; }
    public string Email { get; set; }
    public int Height { get; set; }
    public int Weight { get; set; }
    public bool CardiovascularDisease { get; set; }
    public bool TakingMedications { get; set; }
    public bool PracticingSport { get; set; }
    public bool Allergies { get; set; }
    public bool AnyIllness { get; set; }
    // 一个客人可拥有多个预订
    public virtual ICollection<Booking> Bookings { get; set; } = new List<Booking>();
}

修正后的BookedProgram实体

public class BookedProgram : BaseEntity
{
    public int RoomId { get; set; }
    // 直接关联房间，通过房间导航至时长、健康项目
    public virtual Room Room { get; set; }
    [Column(TypeName = "decimal(18,4)")]
    // 存储预订时的实际结算价格（避免后续价格变动影响历史订单）
    public decimal ActualPrice { get; set; }
    public virtual Booking Booking { get; set; }
}

修正后的Booking实体

public class Booking : BaseEntity
{
    public int GuestId { get; set; }
    public virtual Guest Guest { get; set; }
    public int BookedProgramId { get; set; }
    public virtual BookedProgram BookedProgram { get; set; }
    public decimal TotalPrice { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime EndDate { get; set; }
}

二、EF Core Fluent API 关系配置 #

在DbContext的OnModelCreating方法中，明确各实体间的关联规则：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 健康项目 → 时长：一对多，级联删除
    modelBuilder.Entity<HealthProgram>()
        .HasMany(h => h.duration)
        .WithOne(d => d.program)
        .HasForeignKey(d => d.HealthProgramId)
        .OnDelete(DeleteBehavior.Cascade);

    // 时长 → 房间：一对多，级联删除
    modelBuilder.Entity<Duration>()
        .HasMany(d => d.rooms)
        .WithOne(r => r.duration)
        .HasForeignKey(r => r.durationId)
        .OnDelete(DeleteBehavior.Cascade);

    // 客人 → 预订：一对多，级联删除
    modelBuilder.Entity<Guest>()
        .HasMany(g => g.Bookings)
        .WithOne(b => b.Guest)
        .HasForeignKey(b => b.GuestId)
        .OnDelete(DeleteBehavior.Cascade);

    // 预订 → 已预订项目：一对一，级联删除
    modelBuilder.Entity<Booking>()
        .HasOne(b => b.BookedProgram)
        .WithOne(bp => bp.Booking)
        .HasForeignKey<Booking>(b => b.BookedProgramId)
        .OnDelete(DeleteBehavior.Cascade);

    // 已预订项目 → 房间：多对一（房间可被多次预订），限制删除（避免删除房间影响历史预订）
    modelBuilder.Entity<BookedProgram>()
        .HasOne(bp => bp.Room)
        .WithMany()
        .HasForeignKey(bp => bp.RoomId)
        .OnDelete(DeleteBehavior.Restrict);
}

三、预订创建示例 #

// 1. 获取目标房间（包含关联的时长、健康项目）
var targetRoom = await _context.Rooms
    .Include(r => r.duration)
    .Include(r => r.duration.program)
    .FirstOrDefaultAsync(r => r.Id == targetRoomId);

if (targetRoom == null)
{
    throw new Exception("指定房间不存在");
}

// 2. 计算实际结算价格
var actualPrice = targetRoom.Price - targetRoom.Discount;

// 3. 创建新客人（若为老客人则直接用已有ID）
var newGuest = new Guest
{
    FirstName = "张三",
    LastName = "王",
    Age = 35,
    PhoneNumber = "13900139000",
    Email = "zhangsan@test.com",
    // 其他健康信息字段赋值
};
_context.Guests.Add(newGuest);
await _context.SaveChangesAsync();

// 4. 创建已预订项目
var bookedProgram = new BookedProgram
{
    RoomId = targetRoom.Id,
    ActualPrice = actualPrice
};
_context.BookedPrograms.Add(bookedProgram);
await _context.SaveChangesAsync();

// 5. 创建预订记录
var booking = new Booking
{
    GuestId = newGuest.Id,
    BookedProgramId = bookedProgram.Id,
    TotalPrice = actualPrice,
    StartDate = DateTime.Now.AddDays(1),
    // 根据时长类型计算结束时间：如DurationType为周则乘以7
    EndDate = DateTime.Now.AddDays(1) + TimeSpan.FromDays(
        targetRoom.duration.DurationType == DurationType.Week 
            ? targetRoom.duration.DurationNumbers * 7 
            : targetRoom.duration.DurationNumbers
    )
};
_context.Bookings.Add(booking);
await _context.SaveChangesAsync();

四、预订详情查询示例 #

var bookingDetails = await _context.Bookings
    .Include(b => b.Guest)
    .Include(b => b.BookedProgram)
        .ThenInclude(bp => bp.Room)
            .ThenInclude(r => r.duration)
                .ThenInclude(d => d.program)
    .FirstOrDefaultAsync(b => b.Id == bookingId);

if (bookingDetails != null)
{
    var programName = bookingDetails.BookedProgram.Room.duration.program.Name;
    var durationInfo = $"{bookingDetails.BookedProgram.Room.duration.DurationNumbers} {bookingDetails.BookedProgram.Room.duration.DurationType}";
    var roomType = bookingDetails.BookedProgram.Room.Type;
    var actualPrice = bookingDetails.BookedProgram.ActualPrice;
    // 后续处理详情展示逻辑
}

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