如何通过单次LINQ-to-Entities调用获取学生家庭位置名称？

实体模型定义 #

class Attendance
{
  int Id;
  DateTime AttendanceDate;
  bool IsPresent;
  Student Student;
  Location Location;
}

class Student
{
  int Id;
  string Name;
  string HomeLocationCode;
}

class Location
{
  int Id;
  string LocationCode;
  string Name;
}

现有问题代码 #

当前LINQ代码仅能获取学生家庭位置编码，无法直接获取名称：

var attendances = await _dbContext.Attendances.Select(x => new QueryResult
{
    Id = x.Id,
    AttendanceDate = x.AttendanceDate,
    IsPresent = x.IsPresent,
    AttendanceLocationCode = x.Location.LocationCode,
    AttendanceLocationName = x.Location.LocationName,
    HomeLocation = x.Student.HomeLocationCode, // 此处需替换为位置名称
}, cancellationToken);

参考SQL查询 #

SELECT c.Id, c.AttendanceDate, c.Name, c.LastName, c.StudentId, m.LocationName AS AttendanceLocation, s.LocationCode AS HomeLocation
FROM [Attednace] AS c
LEFT JOIN [Student] AS s ON c.Id = s.Id
INNER JOIN [Location] AS m ON c.Id = m.Id

现有两次查询实现 #

目前通过两次数据库调用实现需求，但希望优化为单次调用：

// 第一次数据库调用，获取除家庭位置名称外的所有信息
var attendances = await _dbContext.Attendances.Select(x => new QueryResult
{
    Id = x.Id,
    AttendanceDate = x.AttendanceDate,
    IsPresent = x.IsPresent,
    AttendanceLocationCode = x.Location.LocationCode,
    AttendanceLocationName = x.Location.LocationName,
    HomeLocation = x.Student.HomeLocationCode, // 临时存储编码
}, cancellationToken);

// 第二次数据库调用
var locations = await _dbContext.Locations
    .Where(x => attendances.Select(c => c.HomeLocation).Distinct().Contains(x.LocationCode))
    .ToListAsync();

foreach (var item in attendances)
{
    item.HomeLocation = locations
        .Where(l => l.LocationCode == item.HomeLocation)
        .Select(l => l.Name)
        .SingleOrDefault();
}

// 此时所有列表项的HomeLocation已正确填充为名称

优化后的单次查询方案 #

方案1：使用Join关联查询 #

利用LINQ的Join和GroupJoin操作，将三个表通过编码关联，对应SQL的LEFT JOIN逻辑，实现单次数据库查询：

var attendances = await _dbContext.Attendances
    .Join(_dbContext.Students, 
          attendance => attendance.Student.Id, 
          student => student.Id, 
          (attendance, student) => new { attendance, student })
    .GroupJoin(_dbContext.Locations, 
               combined => combined.student.HomeLocationCode, 
               location => location.LocationCode, 
               (combined, locations) => new QueryResult
               {
                   Id = combined.attendance.Id,
                   AttendanceDate = combined.attendance.AttendanceDate,
                   IsPresent = combined.attendance.IsPresent,
                   AttendanceLocationCode = combined.attendance.Location.LocationCode,
                   AttendanceLocationName = combined.attendance.Location.Name,
                   HomeLocation = locations.Select(l => l.Name).SingleOrDefault()
               })
    .ToListAsync(cancellationToken);

方案2：子查询写法 #

直接在Select中嵌入子查询，更贴近SQL的写法，逻辑清晰：

var attendances = await _dbContext.Attendances.Select(x => new QueryResult
{
    Id = x.Id,
    AttendanceDate = x.AttendanceDate,
    IsPresent = x.IsPresent,
    AttendanceLocationCode = x.Location.LocationCode,
    AttendanceLocationName = x.Location.Name,
    HomeLocation = x.Student != null 
        ? _dbContext.Locations.Where(l => l.LocationCode == x.Student.HomeLocationCode).Select(l => l.Name).SingleOrDefault()
        : null
}).ToListAsync(cancellationToken);

说明 #

• 两种方式都会被EF Core转换为单次SQL查询，避免多次数据库往返
• GroupJoin适合处理位置不存在的场景，保证即使无对应位置也能返回结果
• 子查询写法更简洁，适合仅需获取单个关联值的场景

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