如何构造一个特定的linq查询?

Question

我有三个表，医生，办公室和预约。Office表的外键为DoctorId, appointment表的外键为OfficeId。我想获取OfficeId等于具有相同doctorId的办公室列表中id的所有约会。具体来说，我不知道如何从办公室列表中提取id。下面是我的代码，为了简洁起见，我省略了一些部分:

public class Appointment1 : BaseEntity
{
public int? Patient1Id { get; set; }     
[ForeignKey("Patient1Id")]
public Patient1 Patient { get; set; } 
public int Office1Id { get; set; }     
[ForeignKey("Office1Id")]
public Office1 Office { get; set; }            
[DataType(DataType.Date)]
public DateTime StartDateAndTimeOfAppointment { get; set; }
[DataType(DataType.Date)]
public DateTime EndDateAndTimeOfAppointment { get; set; }
public bool? Status { get; set; }
public string Remarks { get; set;}       
}
public class Doctor1 : BaseEntity
{
public int ApplicationUserId { get; set; }     
[ForeignKey("ApplicationUserId")]
public ApplicationUser ApplicationUser { get; set; }   

public string Name { get; set; }
public string Resume { get; set; }
}
public class Office1 : BaseEntity
{
public int Doctor1Id { get; set; }
[ForeignKey("Doctor1Id")]
public Doctor1 Doctor { get; set; }

public decimal InitialExaminationFee { get; set; }
public decimal FollowUpExaminationFee { get; set; }
public string Street { get; set; }
public string City { get; set; }
public string Country { get; set; }
}
public async Task<List<Appointment1>> GetAppointmentsWithSearchingAndPaging(QueryParameters queryParameters, 
int userId)
{
var doctor = await _context.Doctors1.Where(x => x.ApplicationUserId == userId)
.FirstOrDefaultAsync();
var office = await _context.Offices.Where(x => x.Doctor1Id == doctor.Id)
.FirstOrDefaultAsync();
IQueryable<Appointment1> appointment = _context.Appointments1.Include(x => x.Patient)
.Where(x => x.Office1Id == office.Id)
.AsQueryable().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();        
}

问题是办公室给出了firstdefaultasync，应该给出列表，因为我想要所有的id，但最后我只得到一个相同的officeid作为外键的约会…提前感谢!

Answer 1

这就是答案，我需要这部分代码，我的问题不精确，所以我道歉:

var offices = await _context.Offices。在(x =比;x.Doctor1Id == doctor.Id)

.ToListAsync ();

IEnumerable<int> ids = offices.Select(x => x.Id);
IQueryable<Appointment1> appointment = _context.Appointments1.Include(x => x.Patient)
.Where(x => ids.Contains(office.Id))
.AsQueryable().OrderBy(x => x.Id);