C# 如果联接中有多个字段，您如何在 Linq 中退出联接？

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

提问by Shaul Behr

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.

我之前问过一个问题，为什么 Linq 中的左连接不能使用定义的关系；迄今为止，我还没有得到满意的答复。

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

现在，在平行轨道上，我已经接受我需要使用join关键字，就好像我的对象之间没有定义任何关系一样，我正在尝试找出如何在 Linq 中表达我的查询。问题是，它是多个表之间左连接的聚合，连接中涉及多个字段。没有办法简化这一点，所以这里是 SQL 的所有未掩饰的荣耀：

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 TreatmentPlanDetailrecords for this Patientwhere the authorizing Payerrequires a prescription for this ServiceType, but there is either no ServicePerscriptionrecord, or it has expired.)

（仅供参考，如果它有助于理解我正在尝试做的事情：我正在尝试确定是否有任何TreatmentPlanDetail记录，Patient授权Payer需要为此提供处方ServiceType，但要么没有ServicePerscription记录，要么已经过期。 )

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

现在，这是我的 C# 代码的样子：

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

糟糕，无法编译！出于某种原因（我想要一个解释）我不能在 equijoin 中使用那个文字布尔值！好吧，我会忽略它，稍后过滤掉“RequiresPrescription”的东西......

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

那么，您应该如何使用多个字段进行左连接？

采纳答案by lc.

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

我认为您需要使用into关键字并在加入之后解决缺少的孩子的 DefaultIfEmpty() 问题，而不是之前：

...
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 inwill be executed before selecting into it, which is not your desired behavior. You want the matching rows or null if no matching rows exist.

LinqUtils.GetTable<PayerServiceTypeRules>().DefaultIfEmpty()可能会变成空值，因为返回的DataTable 不包含 rows，从而导致您的异常。注意整个语句 afterin将在选择之前执行，这不是您想要的行为。如果不存在匹配的行，您需要匹配的行或 null。

---

---

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

对于布尔问题，这是一个命名问题（右侧没有匹配“RxReq”，左侧没有匹配“RequiresPrescription”）。尝试true像上面一样命名“RequiresPrescription”（或命名右侧的pstr.RequiresPrescription“RxReq”）。