CODEWITHSUNDEEP
c#linqleft-join
Alan Wayne
Alan Wayne

Reputation: 5384

Difficulty with Linq on inner and left outer joins

I can't see why this fails. The error is:

Failure: Identifier 'Patient_recid' is not a parameter or variable or field of 'Nova.Data.Returntooffice'. If 'Patient_recid' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias.

Yep...that is correct. The tables are set up such that

  1. Returntooffices references an encountertimes record (inner join).
  2. The encountertimes record references patient table record (inner join).
  3. The Returntoooffice record may or may not have an appointment record --This is the reason I am trying to use a left outer join.

The returntooffices record DOES NOT have a patient_recid, and this is why I am attempting to join it to a encountertimes record.

How is this done correctly?

TIA

var query = 
    from rto in cxt.Returntooffices
    from encounter in cxt.Encountertimes.Where(f => f.Recid == rto.Encounter_recid)
    from patient in cxt.Patients.Where(p => p.Recid == encounter.Patient_recid && p.Groupid == groupid)
    from appointment in cxt.Appointments.Where(a => a.Recid == rto.Appointment_recid).DefaultIfEmpty()
    select new
    {
        RTO = rto,
        APPOINTMENT = appointment
     };

 var b = query.ToList();

Upvotes: 1

Views: 231

Answers (1)

Alan Wayne
Alan Wayne

Reputation: 5384

In lieu of a better idea, this seems to compile and work (yea!)

var q = 
    from rto in cxt.Returntooffices
    join encounter in cxt.Encountertimes on rto.Encounter_recid equals encounter.Recid 
    join patient in cxt.Patients on encounter.Patient_recid equals patient.Recid
    join appointment in cxt.Appointments on rto.Appointment_recid equals appointment.Recid into apt
    from a in apt.DefaultIfEmpty() 
    where patient.Groupid == groupid
    select new
     {
        RTO = rto,
        APPOINTMENT = a
      }
    ).ToList();

Upvotes: 1

Related Questions