如何编写LINQ左连接分组查询获取员工问卷完成状态？

现有三个.NET实体类：Employee、Answer、FinalizedEmployee，业务规则为：每个员工默认在Answer表存在记录，完成所有问题后会在FinalizedEmployee表添加对应记录。需要编写LINQ查询展示所有员工实体及其是否完成全部问题的状态。当前尝试的查询能返回正确状态，但仅输出员工ID而非完整员工实体，需给出正确写法及优化方案。

实体类代码 #

public class Employee
{
    public Guid Id { get; set; }
    public string FirstName { get; set; } = null!;
    public string LastName { get; set; } = null!;
}

public class Answer
{
    public Guid Id { get; set; }
    public Guid QuestionId { get; set; }
    public Guid AppraiserId { get; set; }// 评估人ID
    public Guid AppraisedId { get; set; }// 被评估人ID
}

public class FinalizedEmployee
{
    public long Id { get; set; }
    public Guid AppraiserId { get; set; }
    public Guid AppraisedId { get; set; }
}

当前尝试的查询代码 #

var attendees = from answers in dbContext.Answers
            join employees in dbContext.Employees
            on answers.AppraiserId equals employees.Id
            join finalize in dbContext.FinalizedEmployees 
            on answers.AppraiserId equals finalize.AppraisedId into finalized
            from completed in finalized.DefaultIfEmpty()
            where answers.AppraisalId == request.appraisalId

            group employees by new { appraiser=answers.AppraiserId,hasComplete=completed!=null} into attendee
            select new {attendee.Key.appraiser,attendee.Key.hasComplete };

正确查询写法与优化方案 #

1. 修正后的基础查询（查询表达式风格） #

var attendees = from ans in dbContext.Answers
                join emp in dbContext.Employees
                    on ans.AppraiserId equals emp.Id
                join finalized in dbContext.FinalizedEmployees
                    on ans.AppraiserId equals finalized.AppraisedId into finalizedGroup
                from completed in finalizedGroup.DefaultIfEmpty()
                where ans.AppraisalId == request.appraisalId
                // 按员工ID分组，避免同一员工因多条Answer记录重复输出
                group new { emp, completed } by emp.Id into attendeeGroup
                select new 
                {
                    // 取分组内的员工实体（同一ID的员工实体一致）
                    Employee = attendeeGroup.First().emp,
                    // 判断是否存在完成记录
                    HasCompleted = attendeeGroup.Any(item => item.completed != null)
                };

2. 更高效的优化写法（方法链风格） #

针对原查询可能出现的重复员工记录问题，先筛选去重再关联，提升查询效率：

// 先获取参与指定评估的所有员工（去重）
var targetEmployees = dbContext.Answers
    .Where(a => a.AppraisalId == request.appraisalId)
    .Join(dbContext.Employees,
          ans => ans.AppraiserId,
          emp => emp.Id,
          (ans, emp) => emp)
    .Distinct();

// 关联FinalizedEmployee判断完成状态
var attendees = targetEmployees
    .GroupJoin(dbContext.FinalizedEmployees,
               emp => emp.Id,
               finalized => finalized.AppraisedId,
               (emp, finalizedRecords) => new 
               {
                   Employee = emp,
                   HasCompleted = finalizedRecords.Any()
               });

3. 进一步优化建议 #

• 使用自定义DTO替代匿名类型：定义专门的状态返回类，方便后续业务复用和类型安全：

  public class EmployeeCompletionStatus
  {
      public Employee Employee { get; set; } = null!;
      public bool HasCompleted { get; set; }
  }
  

  查询时将投影目标改为该DTO即可。
• 利用EF Core导航属性：如果已在实体类中配置导航关系（比如Employee添加ICollection<Answer>和ICollection<FinalizedEmployee>导航属性），可以大幅简化查询，避免手动join：

  var attendees = dbContext.Employees
      .Where(emp => emp.Answers.Any(a => a.AppraisalId == request.appraisalId))
      .Select(emp => new EmployeeCompletionStatus
      {
          Employee = emp,
          HasCompleted = emp.FinalizedEmployees.Any()
      });
  

• 过滤逻辑前置：始终将AppraisalId的过滤条件放在最前面，减少后续关联的数据量，提升查询性能。

内容的提问来源于stack exchange，提问作者Ali.Rashidi