如果连接中有多个字段,如何在 Linq 中保留连接?

发布于 2024-07-26 03:29:35 字数 2105 浏览 9 评论 0原文

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

现在,在并行的轨道上,我已经接受我需要使用 join 关键字,就好像我的对象之间没有定义关系一样,并且我正在尝试找出如何表达我的查询在林克。 问题是,它是多个表之间左连接的集合,连接涉及多个字段。 没有办法简化这一点,所以这里是 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

仅供参考,如果它有助于理解我正在尝试做的事情:我正在尝试确定是否有任何 TreatmentPlanDetailPatient 的记录,其中授权Payer 需要此ServiceType 的处方,但没有ServicePerscription记录,或者已过期。)

现在,我的 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 };

哎呀,无法编译! 由于某种原因(我想要一个解释)我不能在等值连接中使用该文字布尔值! 好吧,我将忽略它,并稍后过滤掉“RequiresPrescription”内容……

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

现在它可以编译 - 但当我运行时,我在这一行收到“对象引用未设置”异常。 DUH!当然有一个空! 如果不允许引用右侧的对象(该对象可能为空),您还应该如何与左连接进行比较?

那么,您应该如何使用多个字段进行左连接呢?

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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

诗化ㄋ丶相逢 2024-08-02 03:29:35

我认为您需要使用 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()。 DefaultIfEmpty() 可能会出现 null,因为返回的 DataTable 不包含行,从而导致异常。 请注意,in 之后的整个语句将在选择之前执行,这不是您想要的行为。 您需要匹配的行,如果不存在匹配的行,则需要 null。


对于布尔问题,这是一个命名问题(没有任何内容与右侧的“RxReq”匹配,也没有任何内容与左侧的“RequiresPrescription”匹配)。 尝试将 true 命名为“RequiresPrescription”,如我上面所述(或将右侧的 pstr.RequiresPrescription 命名为“RxReq”)。

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文