EF Joining multiple tables and display multiple data sets

Question

Patient table has a one to many relationship with doctornote table. How can i fetch doctorNoteID 3 and 4 together into my person result?? Look at the attached image below. Currently i can only fetch one result which is doctornoteID 3.

    public IHttpActionResult testing(int patientID, string token)
    {
        var person = (from p in _context.Patients
                      join e in _context.PatientAllocations
                      on p.patientID equals e.patientID
                      join d in _context.DoctorNotes
                      on p.patientID equals d.patientID
                      where p.patientID == patientID
                      select new
                      {
                          patient_patientID = p.patientID,
                          patient_firstName = p.firstName,
                          patient_lastName = p.lastName,
                          patientallocation_patientAllocationID = e.patientAllocationID,
                          patientallocation_patientID = e.patientID,
                          DoctorNote_doctorNoteID = d.doctorNoteID,
                          DoctorNote_doctorNote = d.note,
                      }).ToList();
        return Ok(person);
    }

Patient Model

public class Patient
{
    [Required]
    public int patientID { get; set; }

    [StringLength(255)]
    public string firstName { get; set; }

    [StringLength(255)]
    public string lastName { get; set; }
}

DoctorNote

public class DoctorNote
{
    [Required]
    public int doctorNoteID { get; set; }
    public string note { get; set; }
    public Patient Patient { get; set; }
    public int patientID { get; set; }

}

Answer 1

Instead of joining manually like you try:

public IHttpActionResult testing(int patientID, string token)
{
    var person = (from p in _context.Patients
                  join e in _context.PatientAllocations
                  on p.patientID equals e.patientID
                  join d in _context.DoctorNotes
                  on p.patientID equals d.patientID
                  where p.patientID == patientID
                  select new
                  {
                      patient_patientID = p.patientID,
                      patient_firstName = p.firstName,
                      patient_lastName = p.lastName,
                      patientallocation_patientAllocationID = e.patientAllocationID,
                      patientallocation_patientID = e.patientID,
                      DoctorNote_doctorNoteID = d.doctorNoteID,
                      DoctorNote_doctorNote = d.note,
                  }).ToList();
    return Ok(person);
     }

You could try this: Assuming that the Navigation-Properties are named like the Sets...

public IHttpActionResult testing(int patientID, string token)
 {
    var person = Context.Patients
                          .AsNoTracking()
                          .Include(p=>p.PatientAllocations)
                          .Include(d=>d.DoctorNotes)
                           .Where(p=>p.PatientID==patientID)
                           .ToList();

   return Ok(person);
 }

Edit:

Change your Patient-class like this, then your problem should be gone:

public class Patient
{
    [Required]
    public int patientID { get; set; }

    [StringLength(255)]
    public string firstName { get; set; }

    [StringLength(255)]
    public string lastName { get; set; }

    public virtual ICollection<DoctorNote> DoctorNotes { get; set;}
    public virtual ICollection<PatientAllocation> PatientAllocations { get; set; }
}