我有两个声明:

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();

10-05 23:11
查看更多