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

}

And why you want to map your Patients this way? You could also just load the Patients and include the Notes like described in the [documentation](https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx) — Nikolaus, Mar 27 '18 at 15:05
@Nikolaus Yes include will work. But include only link 2 Tables together. I want to link more than 7-8 tables. Include would not work in this case. — , Mar 27 '18 at 15:10
Why not? you can use Include() for the first-level multiple times and ThenInclude() if you need to go beyond. — Nikolaus, Mar 27 '18 at 15:12
@Nikolaus thanks but i dont understand what u meant by include first first level multiple times and then include if u want go beyond. — , Mar 27 '18 at 15:15

Nikolaus · Answer 1 · 2018-03-27T17:52:09.337

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

edited Mar 27 '18 at 17:52

answered Mar 27 '18 at 15:27

Nikolaus

1,859
1
10
16

I cant do this. Patient table does not contain PatientAllocation or DoctorNotes foreign key. So include would not work. – Mar 27 '18 at 15:30
PatientAllocation and DoctorNotes contains PatientID(foreign key) – Mar 27 '18 at 15:32
@user2837162 But vis-versa. Just try it. – Nikolaus Mar 27 '18 at 15:32
Did you try it? – Nikolaus Mar 27 '18 at 15:33
i tried. I can do this. var person = _context.PatientAllocation.Include(p=>p.Patient).ToList(). – Mar 27 '18 at 15:33
And what’s the result? – Nikolaus Mar 27 '18 at 15:35
You shouldn't need JOINs. See [here](https://coding.abel.nu/2012/06/dont-use-linqs-join-navigate/). What does your patient model look like? – Steve Greene Mar 27 '18 at 15:37
PatientAllocation and DoctorNotes highlighted in red. Cause it cant be found. – Mar 27 '18 at 15:38
i edited the question with patient & doctorNotes class – Mar 27 '18 at 15:42
@user2837162 I edited my answer. Add Navigation-Properties. – Nikolaus Mar 27 '18 at 15:48
@SteveGreene I think if you want eager-loaded-Navigation, you can simply Include. If he wanted only one Property Navigation-Properties would be simpler. – Nikolaus Mar 27 '18 at 15:54
It is not working though. There is an error. Type 'System.Data.Entity.DynamicProxies.Patient_59285D' with data contract name 'Patient_5EC67FD:http://schemas.datacontract.org/2004/07/System.Data.Entity.DynamicProxies' is not expected. Consider using a DataContractResolver if you are using DataContractSerializer or add any types not known statically to the list of known t – Mar 27 '18 at 16:01
I tried return Ok(person.Select(Mapper.Map)); and Ok(person) both give same error which listed above. Patient n PatientDto have the same attributes – Mar 27 '18 at 16:14
What is the StackTrace to this Error? – Nikolaus Mar 27 '18 at 16:18
@user2837162 Please add StackTrace! – Nikolaus Mar 27 '18 at 16:23
https://stackoverflow.com/questions/13077328/serialization-of-entity-framework-objects-with-one-to-many-relationship/13077670 – Steve Greene Mar 27 '18 at 17:03
@user2837162 I edited the testing-method to prevent the error – Nikolaus Mar 27 '18 at 17:54

EF Joining multiple tables and display multiple data sets

1 Answers1