Linq 子查询Where 子句
我需要一些有关 linq 查询的帮助。它应该相当简单,但它却让我大吃一惊。
我需要使用子查询来过滤掉主查询中的数据,但我尝试使用的每个路径都会失败。
子查询本身看起来像这样。
int pk = (from c in context.PtApprovedCertifications
where c.FkosParticipant == 112118 &&
(!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
c.EffectiveDate <= DateTime.Now &&
c.FkptApprovedCertificationVoidedBy == null
orderby c.EffectiveDate descending,c.PK descending
select c.PK).FirstOrDefault();
这按预期工作,但正如您所看到的,我插入了数字 112118。这应该是主查询的主键。
我一直在处理的组合查询如下所示。
IQueryable<PtAMember> result = (from p in context.PtAMembers
where (p.FkptACertification == (from c in context.PtApprovedCertifications
where c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK &&
(!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
c.EffectiveDate <= DateTime.Now &&
c.FkptApprovedCertificationVoidedBy == null
orderby c.EffectiveDate descending, c.PK descending
select c.PK).FirstOrDefault()) &&
(p.LastName.ToLower().Contains(param.ToLower()) ||
p.FirstName.ToLower().Contains(param.ToLower()) ||
p.SocialSecurityNumber.Contains(param))
select p).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName);
但这会导致错误。任何帮助解决这个难题的帮助将不胜感激。
谢谢!
I need some help with thsi linq query. It shoudl be fairly simple, but it is kicking my butt.
I need to use a subquery to filter out data from the main query, but every path I have tried to use results in failure.
The subquery by itself looks like this.
int pk = (from c in context.PtApprovedCertifications
where c.FkosParticipant == 112118 &&
(!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
c.EffectiveDate <= DateTime.Now &&
c.FkptApprovedCertificationVoidedBy == null
orderby c.EffectiveDate descending,c.PK descending
select c.PK).FirstOrDefault();
This works as expected but as you can see I plugged in the number 112118. This should be the primary key from main query.
The combined query I've been working on looks like this.
IQueryable<PtAMember> result = (from p in context.PtAMembers
where (p.FkptACertification == (from c in context.PtApprovedCertifications
where c.FkosParticipant == p.PtApprovedCertification.OsParticipant.PK &&
(!excludedActionTypes.Contains(c.FkMLSosCodeActionType)) &&
c.EffectiveDate <= DateTime.Now &&
c.FkptApprovedCertificationVoidedBy == null
orderby c.EffectiveDate descending, c.PK descending
select c.PK).FirstOrDefault()) &&
(p.LastName.ToLower().Contains(param.ToLower()) ||
p.FirstName.ToLower().Contains(param.ToLower()) ||
p.SocialSecurityNumber.Contains(param))
select p).Distinct().OrderBy(PtAMembers => PtAMembers.LastName).ThenBy(PtAMember => PtAMember.FirstName);
This results in an error though. Any help in solving this conundrum would greatly appreciated.
Thanks!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
如何将子查询转换为查找函数:
然后在主查询中使用它。
How about turning your subquery into a lookup function:
then using that in your main query.