EF6使用Include加载关联实体异常：仅返回首个子实体求助

问题背景 #

定义了Review和Competency一对多关联实体，ReviewId=172的记录对应3个Competency，但执行包含Include的查询后，仅返回包含第一个Competency的Review对象，而EF生成的SQL查询结果明确显示有3条关联数据。

实体类与DbContext代码 #

public class Review
{
    public int ReviewId { get; set; }
    public ICollection<Competency> Competencies { get; set; }
}

public class Competency
{
    public int Id { get; set; }
    public int ReviewId { get; set; }
    public Review Review { get; set; }
}

public class MyContext: DbContext
{
    public DbSet<Review> Reviews { get; set; }
    // 注意此处拼写错误：Compitencies → Competencies
    public DbSet<Competency> Compitencies { get; set; }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Competency>()
            .HasRequired(c => c.Review)
            .WithMany(r => r.Competencies)
            .HasForeignKey(c => c.ReviewId);
    }
}

执行的查询代码 #

Review review = _context.Reviews
                .Where(r => r.ReviewId == 172)                
                .Include(r => r.Competencies)
                .FirstOrDefault();

EF生成的SQL语句 #

SELECT 
    [Project1].[ReviewId] AS [ReviewId], 
    [Project1].[ReviewPeriodId] AS [ReviewPeriodId], 
    [Project1].[ServiceNumber] AS [ServiceNumber], 
    [Project1].[ManagerServiceNumber] AS [ManagerServiceNumber], 
    [Project1].[ModeratorServiceNumber] AS [ModeratorServiceNumber], 
    [Project1].[ReviewStatusId] AS [ReviewStatusId], 
    [Project1].[IsLocked] AS [IsLocked], 
    [Project1].[DateCreated] AS [DateCreated], 
    [Project1].[AddedByServiceNumber] AS [AddedByServiceNumber], 
    [Project1].[C1] AS [C1], 
    [Project1].[CompetencyId] AS [CompetencyId], 
    [Project1].[ReviewCompetency] AS [ReviewCompetency], 
    [Project1].[PerformanceStandard] AS [PerformanceStandard], 
    [Project1].[Remark] AS [Remark], 
    [Project1].[AddedByServiceNumber1] AS [AddedByServiceNumber1], 
    [Project1].[DateAdded] AS [DateAdded], 
    [Project1].[ReviewId1] AS [ReviewId1]
FROM ( SELECT 
    [Extent1].[ReviewId] AS [ReviewId], 
    [Extent1].[ReviewPeriodId] AS [ReviewPeriodId], 
    [Extent1].[ServiceNumber] AS [ServiceNumber], 
    [Extent1].[ManagerServiceNumber] AS [ManagerServiceNumber], 
    [Extent1].[ModeratorServiceNumber] AS [ModeratorServiceNumber], 
    [Extent1].[ReviewStatusId] AS [ReviewStatusId], 
    [Extent1].[IsLocked] AS [IsLocked], 
    [Extent1].[DateCreated] AS [DateCreated], 
    [Extent1].[AddedByServiceNumber] AS [AddedByServiceNumber], 
    [Extent2].[CompetencyId] AS [CompetencyId], 
    [Extent2].[ReviewCompetency] AS [ReviewCompetency], 
    [Extent2].[PerformanceStandard] AS [PerformanceStandard], 
    [Extent2].[Remark] AS [Remark], 
    [Extent2].[AddedByServiceNumber] AS [AddedByServiceNumber1], 
    [Extent2].[DateAdded] AS [DateAdded], 
    [Extent2].[ReviewId] AS [ReviewId1], 
CASE WHEN ([Extent2].[CompetencyId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM  [dbo].[Review] AS [Extent1]
LEFT OUTER JOIN [dbo].[Competency] AS [Extent2] ON [Extent1].[ReviewId] = [Extent2].[ReviewId]
WHERE [Extent1].[ReviewId] = 172)  AS [Project1]
ORDER BY [Project1].[ReviewId] ASC, [Project1].[C1] ASC

问题原因 #

1. DbSet名称拼写错误：MyContext中Competency对应的DbSet写成了Compitencies（拼写错误，正确应为Competencies），导致EF无法正确识别该实体集的映射关系，在处理关联数据时无法正确将多条Competency实例合并到Review的Competencies集合中。
2. 主键映射不匹配：实体类Competency的主键属性是Id，但生成的SQL中使用的是CompetencyId作为主键列名，说明数据库表的主键列与实体类属性名不一致，EF无法通过主键区分不同的Competency实例，最终只保留了第一条数据。

解决方法 #

1. 修正DbSet名称：将MyContext中的Compitencies改为Competencies，确保与实体类名称一致：

public DbSet<Competency> Competencies { get; set; }

2. 对齐主键映射：如果数据库中Competency表的主键列是CompetencyId，可通过以下两种方式修正：
   • 修改实体类属性名：将public int Id { get; set; }改为public int CompetencyId { get; set; }
   • 配置Fluent API映射：在OnModelCreating中添加主键列名配置：

     modelBuilder.Entity<Competency>()
         .HasKey(c => c.Id)
         .Property(c => c.Id)
         .HasColumnName("CompetencyId");
     

3. 验证关联映射：确认OnModelCreating中的一对多配置正确，WithMany(r => r.Competencies)指向的是Review类中正确的集合属性。
4. 备选方案：显式加载：如果上述修正后仍有问题，可改用显式加载替代Include：

var review = _context.Reviews.FirstOrDefault(r => r.ReviewId == 172);
if (review != null)
{
    _context.Entry(review).Collection(r => r.Competencies).Load();
}

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