我有两个声明:
var chgAssociationQuery1 = (from a in sostenuto.PROBLEMS
join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.FROMSERVICEREQNO
join c in sostenuto.Changes on b.TOSERVICEREQNO equals c.SERVICEREQNO
where b.FROMSERVICEID == 101001110
&& b.TOSERVICEID == 101001109
&& a.NAME.Contains(name)
select new { ProblemReqNo = a.SERVICEREQNO,
ProblemId = a.SERVICEREQID,
ChangeReqNo = c.SERVICEREQNO,
ChangeId = c.SERVICEREQID,
ChangeNumber = c.CSEBRANCHCHANGE,
ChangeBranch = c.MAINTBRANCH});
var chgAssociationQuery2 = (from a in sostenuto.PROBLEMS
join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.TOSERVICEREQNO
join c in sostenuto.Changes on b.FROMSERVICEREQNO equals c.SERVICEREQNO
where b.FROMSERVICEID == 101001109
&& b.TOSERVICEID == 101001110
&& a.NAME.Contains(name)
select new { ProblemReqNo = a.SERVICEREQNO,
ProblemId = a.SERVICEREQID,
ChangeReqNo = c.SERVICEREQNO,
ChangeId = c.SERVICEREQID,
ChangeNumber = c.CSEBRANCHCHANGE,
ChangeBranch = c.MAINTBRANCH });
var vProblemxChange = chgAssociationQuery1.Union(from a in chgAssociationQuery2 select a);
我希望能够将它们组合成一个;因为目前它在数据库中循环两次——这是非常低效的。我试过研究如何在where子句中放置多个select语句,但似乎找不到我能理解的任何东西。
有人能说明一下我的情况吗?
我试图修改下面的答案:
var vProblemxChange =
(from a in sostenuto.PROBLEMS
join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.FROMSERVICEREQNO
join c in sostenuto.Changes on b.TOSERVICEREQNO equals c.SERVICEREQNO
join z in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals z.TOSERVICEREQNO
join y in sostenuto.Changes on z.FROMSERVICEREQNO equals y.SERVICEREQNO
where ((b.FROMSERVICEID == 101001110 && b.TOSERVICEID == 101001109 && a.NAME.Contains(name)) ||
(z.FROMSERVICEID == 101001109 && z.TOSERVICEID == 101001110) && a.NAME.Contains(name))
&& a.NAME.Contains(name)
select new
{
ProblemReqNo = a.SERVICEREQNO,
ProblemId = a.SERVICEREQID,
ChangeReqNo = c.SERVICEREQNO,
ChangeId = c.SERVICEREQID,
ChangeNumber = c.CSEBRANCHCHANGE,
ChangeBranch = c.MAINTBRANCH
});
但是,当我试图基于where子句访问不同的连接时,我不能在select语句中更改它;例如,如果or的后半部分为true,则应该在select中使用a和y,而不是a和c。
最佳答案
试着这样做
var chgAssociationQuery1 = ((from a in sostenuto.PROBLEMS
join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.FROMSERVICEREQNO
join c in sostenuto.Changes on b.TOSERVICEREQNO equals c.SERVICEREQNO
where b.FROMSERVICEID == 101001110
&& b.TOSERVICEID == 101001109
&& a.NAME.Contains(name)
select new { ProblemReqNo = a.SERVICEREQNO, ProblemId = a.SERVICEREQID, ChangeReqNo = c.SERVICEREQNO, ChangeId = c.SERVICEREQID, ChangeNumber = c.CSEBRANCHCHANGE, ChangeBranch = c.MAINTBRANCH})
.Union(from a in sostenuto.PROBLEMS
join b in sostenuto.S_ASSOCIATION on a.SERVICEREQNO equals b.TOSERVICEREQNO
join c in sostenuto.Changes on b.FROMSERVICEREQNO equals c.SERVICEREQNO
where b.FROMSERVICEID == 101001109
&& b.TOSERVICEID == 101001110
&& a.NAME.Contains(name)
select new { ProblemReqNo = a.SERVICEREQNO, ProblemId = a.SERVICEREQID, ChangeReqNo = c.SERVICEREQNO, ChangeId = c.SERVICEREQID, ChangeNumber = c.CSEBRANCHCHANGE, ChangeBranch = c.MAINTBRANCH })).ToList();