如何构造特定LINQ查询，获取医生名下所有诊所对应的全部预约记录

核心修改逻辑：将单诊所查询替换为获取当前医生关联的所有诊所ID集合，再通过Contains方法筛选符合条件的预约记录，EF Core会自动将该逻辑翻译为SQL的IN语句。

修改后的完整代码 #

public async Task<List<Appointment1>> GetAppointmentsWithSearchingAndPaging(QueryParameters queryParameters, 
        int userId)
{
    // 先查询对应医生，增加空值判断避免空引用异常
    var doctor = await _context.Doctors1
        .FirstOrDefaultAsync(x => x.ApplicationUserId == userId);
    if (doctor == null)
    {
        return new List<Appointment1>();
    }

    // 获取该医生下所有诊所的ID集合
    var officeIds = await _context.Offices
        .Where(x => x.Doctor1Id == doctor.Id)
        .Select(x => x.Id)
        .ToListAsync();

    IQueryable<Appointment1> appointment = _context.Appointments1
        .Include(x => x.Patient)
        .Include(x => x.Office) // 后续搜索用到Office.Street，需要提前Include加载关联数据
        // 筛选Office1Id属于当前医生诊所ID集合的记录
        .Where(x => officeIds.Contains(x.Office1Id))
        .OrderBy(x => x.Id);
    
    if (queryParameters.HasQuery())
    {
        appointment = appointment
            .Where(x => x.Office.Street.Contains(queryParameters.Query));
    }

    appointment = appointment
        .Skip(queryParameters.PageCount * (queryParameters.Page - 1))
        .Take(queryParameters.PageCount);
    
    return await appointment.ToListAsync();        
}

更高性能的优化写法 #

可以把两次数据库查询合并为一次，减少网络IO开销，EF Core会自动生成多表关联的SQL语句：

public async Task<List<Appointment1>> GetAppointmentsWithSearchingAndPaging(QueryParameters queryParameters, 
        int userId)
{
    IQueryable<Appointment1> appointment = _context.Appointments1
        .Include(x => x.Patient)
        .Include(x => x.Office)
        // 直接通过导航属性关联判断，无需单独查询医生和诊所ID
        .Where(x => x.Office.Doctor.ApplicationUserId == userId)
        .OrderBy(x => x.Id);
    
    if (queryParameters.HasQuery())
    {
        appointment = appointment
            .Where(x => x.Office.Street.Contains(queryParameters.Query));
    }

    appointment = appointment
        .Skip(queryParameters.PageCount * (queryParameters.Page - 1))
        .Take(queryParameters.PageCount);
    
    return await appointment.ToListAsync();        
}

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