ASP.NET Core MVC中如何高效实现帖子与收藏集的关联存储？

你提到的两种用逗号分隔ID的方案都不符合SQL Server这类关系型数据库的设计规范，会带来查询低效、维护困难、数据完整性无法保障的问题，正确的做法是创建一个中间关联表来处理帖子（Posts）和收藏集（Collections）之间的多对多关系。

为什么你的两种方案不可行？ #

方案1：Posts表添加CollectionId（逗号分隔多个ID） #

• 无法用SQL的JOIN做高效关联查询，比如找某个收藏集下的所有帖子，只能用模糊匹配（LIKE '%1%'），性能差还容易误匹配（比如ID=1和ID=11会被混淆）
• 没法建立外键约束，删除某个收藏集时，没法自动同步更新Posts表的CollectionId字段，容易留下无效ID
• 维护成本高：添加/移除帖子到收藏集时，要修改字符串内容，操作繁琐且易出错

方案2：Collections表添加PostId（逗号分隔多个ID） #

• 同样存在查询效率问题，找某个帖子所属的所有收藏集只能用模糊匹配
• 外键约束无法生效，删除帖子时没法自动清理Collections表中的无效PostId
• 收藏集帖子多了之后，字符串字段会变得冗长，不利于存储和处理

推荐的设计方案：创建中间关联表 #

新建一个名为PostCollections的中间表，结构如下：

把PostId和CollectionId设为联合主键，避免同一帖子重复加入同一收藏集。

示例数据 #

该方案的优势 #

1. 数据完整性：通过外键约束，确保PostId和CollectionId都是有效存在的ID，杜绝无效数据
2. 查询高效：用JOIN就能快速查关联数据，比如：
   • 查询收藏集1下的所有帖子：

     SELECT p.* FROM Posts p
     JOIN PostCollections pc ON p.Id = pc.PostId
     WHERE pc.CollectionId = 1
     

   • 查询帖子1所属的所有收藏集：

     SELECT c.* FROM Collections c
     JOIN PostCollections pc ON c.Id = pc.CollectionId
     WHERE pc.PostId = 1
     

3. 维护便捷：添加帖子到收藏集只需插入一行记录，移除只需删除对应行，操作简单不易出错
4. 扩展性强：后续可以在中间表加AddedTime（加入时间）、SortOrder（排序顺序）等字段，满足更多业务需求

在ASP.NET Core MVC中的实现（EF Core） #

如果用Entity Framework Core，可通过实体类配置多对多关系：

// Post实体类
public class Post
{
    public int Id { get; set; }
    public string PostTitle { get; set; }
    // 其他字段...
    public ICollection<PostCollection> PostCollections { get; set; } = new List<PostCollection>();
}

// Collection实体类
public class Collection
{
    public int Id { get; set; }
    public string CollectionName { get; set; }
    // 其他字段...
    public ICollection<PostCollection> PostCollections { get; set; } = new List<PostCollection>();
}

// 中间表实体类
public class PostCollection
{
    public int PostId { get; set; }
    public Post Post { get; set; }
    public int CollectionId { get; set; }
    public Collection Collection { get; set; }
}

// 在DbContext中配置关系
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<PostCollection>()
        .HasKey(pc => new { pc.PostId, pc.CollectionId });

    modelBuilder.Entity<PostCollection>()
        .HasOne(pc => pc.Post)
        .WithMany(p => p.PostCollections)
        .HasForeignKey(pc => pc.PostId);

    modelBuilder.Entity<PostCollection>()
        .HasOne(pc => pc.Collection)
        .WithMany(c => c.PostCollections)
        .HasForeignKey(pc => pc.CollectionId);
}

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