How do you left join in Linq if there is more than one field in the join?

Question

I asked a question earlier about why left joins in Linq can't use defined relationships; to date I haven't got a satisfactory response.

Now, on a parallel track, I've accepted that I need to use the join 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:

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

(FYI, if it helps to understand what I'm trying to do: I'm trying to identify if there are any TreatmentPlanDetail records for this Patient where the authorizing Payer requires a prescription for this ServiceType, but there is either no ServicePerscription record, or it has expired.)

Now, here's what my C# code looks like:

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

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...

...
join pstr in LinqUtils.GetTable<PayerServiceTypeRules>().DefaultIfEmpty()
on new { auth.PayerID, tpd.ServiceTypeID } equals new { pstr.PayerID, pstr.ServiceTypeID }
...

... and now it compiles - but when I run, I get an "Object reference not set" exception on this line. DUH! 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?

So, how are you supposed to do a left join using multiple fields?

Answer 1

I think you need to use the into keyword and resolve the missing children's DefaultIfEmpty() after the join, not before:

...
join pstr in LinqUtils.GetTable<PayerServiceTypeRules>()
on new { auth.PayerID, tpd.ServiceTypeID, bool RequiresPrescription = true } 
equals new { pstr.PayerID, pstr.ServiceTypeID, pstr.RequiresPrescription } 
into pstrs
from PSTR in pstrs.DefaultIfEmpty()
select new { 
    Payer = auth.Payer, 
    Prescription = rx, 
    TreatmentPlanDetail = tpd, 
    Rules = PSTR 
};

LinqUtils.GetTable<PayerServiceTypeRules>().DefaultIfEmpty() is probably turning up a null because the DataTable returned contains no rows, thus causing your exception. Note the entire statement after in will be executed before selecting into it, which is not your desired behavior. You want the matching rows or null if no matching rows exist.

---

For the boolean problem, it is a naming problem (nothing matches "RxReq" on the right side and nothing matches "RequiresPrescription" on the left side). Try naming the true "RequiresPrescription" as I have above (or name the right side's pstr.RequiresPrescription "RxReq").