EF Core使用Include查询Board时触发‘Sequence contains more than one element’错误

问题描述 #

应用中存在Board实体，包含Column实体集合。使用EF Core查询指定Board并通过Include关联Columns时，抛出Sequence contains more than one element错误。移除Include后查询正常，迁移和数据库更新操作无异常。

相关代码 #

获取Board的方法

public async Task<Board?> GetBoardByWorkspaceIdAndBoardIdAsync(WorkspaceId workspaceId, BoardId boardId)
{
    var result = await dbContext
        .Boards
        .Where(board => board.WorkspaceId == workspaceId && board.Id == boardId)
        .Include(board => board.Columns)
        .FirstOrDefaultAsync();

    return result;
}

Board类

public BoardId Id { get; }
public string Name { get; private set; }

public WorkspaceId WorkspaceId { get; }
public ICollection<Column> Columns { get; protected set; } = new List<Column>();

Board配置

public void Configure(EntityTypeBuilder<Board> builder)
{
    builder.ToTable("Boards");

    builder.HasKey(board => board.Id);
    builder.Property(board => board.Id)
        .HasConversion(
            boardId => boardId.Value,
            value => BoardId.Create(value));

    builder.Property(board => board.Name)
        .HasMaxLength(50)
        .IsRequired();

    builder.Property(board => board.WorkspaceId)
        .HasConversion(
            workspaceId => workspaceId.Value,
            value => WorkspaceId.Create(value))
        .IsRequired();

    builder.HasMany(board => board.Columns)
        .WithOne()
        .HasForeignKey(column => column.BoardId)
        .OnDelete(DeleteBehavior.Cascade);
}

Column类

public ColumnId Id { get; }
public string Name { get; }
public BoardId BoardId { get; }

Column配置

public void Configure(EntityTypeBuilder<Column> builder)
{
    builder.ToTable("Columns");

    builder.HasKey(column => column.Id);
    builder.Property(column => column.Id)
        .HasConversion(
            columnId => columnId.Value,
            value => ColumnId.Create(value));

    builder.Property(column => column.Name)
        .HasMaxLength(50)
        .IsRequired();
}

问题原因 #

这个错误的核心是EF Core在加载关联集合时，无法正确映射实体或遇到数据冲突：

1. Column实体的外键配置缺失：Column类中的BoardId是值对象，但配置中未添加转换逻辑，且未显式关联到Board的一对多关系，导致EF Core无法正确识别外键关联规则。
2. 潜在的数据异常：数据库Columns表中可能存在同一BoardId下，Column主键（Id）重复的记录，EF Core加载时无法区分重复实体，触发序列重复错误。

解决方案 #

1. 完善Column的实体配置 #

修改Column配置类，添加BoardId的值对象转换，并显式关联一对多关系：

public void Configure(EntityTypeBuilder<Column> builder)
{
    builder.ToTable("Columns");

    builder.HasKey(column => column.Id);
    builder.Property(column => column.Id)
        .HasConversion(
            columnId => columnId.Value,
            value => ColumnId.Create(value));

    builder.Property(column => column.Name)
        .HasMaxLength(50)
        .IsRequired();

    // 新增：配置BoardId的值对象转换
    builder.Property(column => column.BoardId)
        .HasConversion(
            boardId => boardId.Value,
            value => BoardId.Create(value))
        .IsRequired();

    // 显式关联到Board的一对多关系，与Board配置中的WithOne对应
    builder.HasOne<Board>()
        .WithMany(board => board.Columns)
        .HasForeignKey(column => column.BoardId)
        .OnDelete(DeleteBehavior.Cascade);
}

2. 检查数据库数据 #

• 检查Columns表，确认不存在同一BoardId下Column主键重复的记录。
• 验证Columns表的BoardId字段值与Boards表的Id字段值类型一致（如均为GUID或整数）。

3. 优化查询逻辑（可选） #

若数据量较大，可采用延迟加载关联集合的方式，避免一次性关联查询的潜在问题：

public async Task<Board?> GetBoardByWorkspaceIdAndBoardIdAsync(WorkspaceId workspaceId, BoardId boardId)
{
    var board = await dbContext
        .Boards
        .FirstOrDefaultAsync(board => board.WorkspaceId == workspaceId && board.Id == boardId);

    if (board != null)
    {
        await dbContext.Entry(board)
            .Collection(b => b.Columns)
            .LoadAsync();
    }

    return board;
}

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