How to construct a specific linq query?-CodePudding

I have three tables, doctor, office and appointment. Office table has DoctorId as a foreign key, and Apponitment has a foreign key OfficeId. I want to fetch all the appointments that have OfficeId equal to Ids in the list of offices that have the same doctorId. Specifically, I don't know how to extract ids from the list of offices. Here is my code, I skipped some parts for brevity:

 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();        
    }

The problem is with office which gives firstordefaultasync, and should give list, because I want all the ids, but in the end I get only appointments that have one identical officeid as a foreign key...thanks in advance!

CodePudding user response：

Here is the answer, I needed this part of code, my question was not precise so I appologize:

var offices = await _context.Offices.Where(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);