EF Core多对多数据库结构设计求助：书籍-用户关联优化

当前设计的问题 #

• 实体类拼写错误：UserBookEnty 应为 UserBookEntry
• 外键关联错误：UserChapterEntry 不应直接关联BookId，应关联ChapterId；UserPageNoteEntry应关联PageNoteId而非ChapterId
• 缺少导航属性：所有用户专属实体未配置与User、基础实体的导航属性，EF Core无法自动识别关联关系
• 主键设计冗余：用户专属实体无需单独自增Id，使用**复合主键（UserId + 关联实体Id）**更合理，同时保证数据唯一性
• 关系映射不明确：未通过Fluent API或数据注解明确EF Core的关联关系

修正后的实体类定义 #

基础实体（共享数据） #

public class Book
{
    [Key]
    public string Id { get; set; } = Guid.NewGuid().ToString();

    [Required]
    public string Name { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public decimal Price { get; set; } = decimal.Zero;

    // 导航属性：关联章节
    public ICollection<Chapter> Chapters { get; set; } = new List<Chapter>();
    // 导航属性：用户关联记录
    public ICollection<UserBookEntry> UserBookEntries { get; set; } = new List<UserBookEntry>();
}

public class Chapter
{
    [Key]
    public string Id { get; set; } = Guid.NewGuid().ToString();

    [Required]
    public string Title { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;

    // 导航属性：关联页面笔记
    public ICollection<PageNote> PageNotes { get; set; } = new List<PageNote>();
    // 导航属性：用户关联记录
    public ICollection<UserChapterEntry> UserChapterEntries { get; set; } = new List<UserChapterEntry>();

    // 外键与导航属性：关联书籍
    [ForeignKey(nameof(Book))]
    public string BookId { get; set; }
    public Book Book { get; set; } = null!;
}

public class PageNote
{
    [Key]
    public string Id { get; set; } = Guid.NewGuid().ToString();

    public string Title { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;

    // 导航属性：用户关联记录
    public ICollection<UserPageNoteEntry> UserPageNoteEntries { get; set; } = new List<UserPageNoteEntry>();

    // 外键与导航属性：关联章节
    [ForeignKey(nameof(Chapter))]
    public string ChapterId { get; set; }
    public Chapter Chapter { get; set; } = null!;
}

// 假设的用户实体（需根据实际项目调整）
public class AppUser
{
    [Key]
    public string Id { get; set; } = Guid.NewGuid().ToString();
    // 其他用户属性：用户名、邮箱等

    // 导航属性：用户的书籍记录
    public ICollection<UserBookEntry> UserBookEntries { get; set; } = new List<UserBookEntry>();
    // 导航属性：用户的章节记录
    public ICollection<UserChapterEntry> UserChapterEntries { get; set; } = new List<UserChapterEntry>();
    // 导航属性：用户的页面笔记记录
    public ICollection<UserPageNoteEntry> UserPageNoteEntries { get; set; } = new List<UserPageNoteEntry>();
}

用户专属实体（进度/自定义记录） #

// 用户书籍关联记录（追踪是否拥有、阅读进度等）
public class UserBookEntry
{
    // 复合主键：UserId + BookId
    [Key, Column(Order = 0)]
    public string UserId { get; set; } = string.Empty;
    [Key, Column(Order = 1)]
    public string BookId { get; set; } = string.Empty;

    // 用户专属属性
    public bool IsOwned { get; set; } = false;
    public DateTime? LastReadDate { get; set; }
    public decimal? ProgressPercentage { get; set; }

    // 导航属性
    public AppUser User { get; set; } = null!;
    public Book Book { get; set; } = null!;
}

// 用户章节关联记录（追踪是否完成阅读等）
public class UserChapterEntry
{
    // 复合主键：UserId + ChapterId
    [Key, Column(Order = 0)]
    public string UserId { get; set; } = string.Empty;
    [Key, Column(Order = 1)]
    public string ChapterId { get; set; } = string.Empty;

    // 用户专属属性
    public bool IsCompleted { get; set; } = false;
    public DateTime? CompletedDate { get; set; }
    public int? ReadCount { get; set; } = 0;

    // 导航属性
    public AppUser User { get; set; } = null!;
    public Chapter Chapter { get; set; } = null!;
}

// 用户页面笔记关联记录（追踪是否已读、添加自定义笔记等）
public class UserPageNoteEntry
{
    // 复合主键：UserId + PageNoteId
    [Key, Column(Order = 0)]
    public string UserId { get; set; } = string.Empty;
    [Key, Column(Order = 1)]
    public string PageNoteId { get; set; } = string.Empty;

    // 用户专属属性
    public bool IsCompleted { get; set; } = false;
    public string? UserCustomNote { get; set; }
    public DateTime? LastViewedDate { get; set; }

    // 导航属性
    public AppUser User { get; set; } = null!;
    public PageNote PageNote { get; set; } = null!;
}

DbContext配置 #

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options) { }

    // 基础实体DbSet
    public DbSet<Book> Books { get; set; }
    public DbSet<Chapter> Chapters { get; set; }
    public DbSet<PageNote> PageNotes { get; set; }
    // 用户实体DbSet
    public DbSet<AppUser> AppUsers { get; set; }
    // 用户关联记录DbSet
    public DbSet<UserBookEntry> UserBookEntries { get; set; }
    public DbSet<UserChapterEntry> UserChapterEntries { get; set; }
    public DbSet<UserPageNoteEntry> UserPageNoteEntries { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 配置UserBookEntry的关联关系
        modelBuilder.Entity<UserBookEntry>()
            .HasOne(ube => ube.User)
            .WithMany(u => u.UserBookEntries)
            .HasForeignKey(ube => ube.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<UserBookEntry>()
            .HasOne(ube => ube.Book)
            .WithMany(b => b.UserBookEntries)
            .HasForeignKey(ube => ube.BookId)
            .OnDelete(DeleteBehavior.Cascade);

        // 配置UserChapterEntry的关联关系
        modelBuilder.Entity<UserChapterEntry>()
            .HasOne(uce => uce.User)
            .WithMany(u => u.UserChapterEntries)
            .HasForeignKey(uce => uce.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<UserChapterEntry>()
            .HasOne(uce => uce.Chapter)
            .WithMany(c => c.UserChapterEntries)
            .HasForeignKey(uce => uce.ChapterId)
            .OnDelete(DeleteBehavior.Cascade);

        // 配置UserPageNoteEntry的关联关系
        modelBuilder.Entity<UserPageNoteEntry>()
            .HasOne(upne => upne.User)
            .WithMany(u => u.UserPageNoteEntries)
            .HasForeignKey(upne => upne.UserId)
            .OnDelete(DeleteBehavior.Cascade);

        modelBuilder.Entity<UserPageNoteEntry>()
            .HasOne(upne => upne.PageNote)
            .WithMany(pn => pn.UserPageNoteEntries)
            .HasForeignKey(upne => upne.PageNoteId)
            .OnDelete(DeleteBehavior.Cascade);
    }
}

设计说明 #

1. 复合主键的优势：避免冗余的自增Id，同时保证每个用户对同一基础实体只能有一条专属记录，数据更严谨
2. 明确的导航属性：EF Core可以自动处理关联查询，方便后续编写Include等查询语句
3. 数据隔离：基础实体（Book/Chapter/PageNote）存储共享数据，用户专属实体只存储用户个性化的进度、状态数据，无需复制整个基础实体，节省存储空间
4. 级联删除配置：确保当用户或基础实体被删除时，对应的关联记录也会被清理（可根据业务需求调整OnDelete行为）

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