title: How do you left join in Linq if there is more than one field in the join?
body: <p>I asked a question earlier about <a href="https://stackoverflow.com/questions/1092562/left-join-in-linq">why left joins in Linq can't use defined relationships</a>; to date I haven't got a satisfactory response.</p>
  
  <p>Now, on a parallel track, I've accepted that I need to use the <code>join</code> keyword as if there were no relationship defined between my objects, and I'm trying to work out how to express my query in Linq.  Trouble is, it's a conglomeration of left joins between multiple tables, with multiple fields involved in the join.  There's no way of simplifying this, so here's the SQL in all its unmasked glory:</p>
  
  <pre><code>select *
  from TreatmentPlan tp
  join TreatmentPlanDetail tpd on tpd.TreatmentPlanID = tp.ID
  join TreatmentAuthorization auth on auth.TreatmentPlanDetailID = tpd.ID
  left join PatientServicePrescription rx on tpd.ServiceTypeID = rx.ServiceTypeID
  left join PayerServiceTypeRules pstr on auth.PayerID = pstr.PayerID and tpd.ServiceTypeID = pstr.ServiceTypeID and pstr.RequiresPrescription = 1
  where tp.PatientID = @PatientID
  </code></pre>
  
  <p>(FYI, if it helps to understand what I'm trying to do: I'm trying to identify if there are any <code>TreatmentPlanDetail</code> records for this <code>Patient</code> where the authorizing <code>Payer</code> requires a prescription for this <code>ServiceType</code>, but there is either no <code>ServicePerscription</code> record, or it has expired.)</p>
  
  <p>Now, here's what my C# code looks like:</p>
  
  <pre><code>var q = from tp in TreatmentPlans
          from tpd in tp.Details
          from auth in tpd.Authorizations
          join rx in ServicePrescriptions.DefaultIfEmpty() on tpd.ServiceTypeID equals rx.ServiceTypeID
          // from pstr in auth.Payer.ServiceTypeRules.DefaultIfEmpty() -- very frustrating that this doesn't work!!
          join pstr in LinqUtils.GetTable&lt;PayerServiceTypeRules&gt;().DefaultIfEmpty()
          on new { auth.PayerID, tpd.ServiceTypeID, RxReq = (bool)true } equals new { pstr.PayerID, pstr.ServiceTypeID, pstr.RequiresPrescription }
          select new { Payer = auth.Payer, Prescription = rx, TreatmentPlanDetail = tpd, Rules = pstr };
  </code></pre>
  
  <p>Oops, doesn't compile!  For some reason (I'd love an explanation) I can't use that literal boolean inside the equijoin!  Fine, I'll leave it out, and filter out the "RequiresPrescription" stuff later...</p>
  
  <pre><code>...
  join pstr in LinqUtils.GetTable&lt;PayerServiceTypeRules&gt;().DefaultIfEmpty()
  on new { auth.PayerID, tpd.ServiceTypeID } equals new { pstr.PayerID, pstr.ServiceTypeID }
  ...
  </code></pre>
  
  <p>... and now it compiles - but when I run, I get an "Object reference not set" exception on this line.  <strong>DUH!</strong>  Of course there's a null in there!  How else are you supposed to perform a comparison with a left join, if you're not allowed to reference the object on the right side, that might potentially be null?</p>
  
  <p>So, how are you supposed to do a left join using multiple fields?</p>