sql - 合并两个选择语句

Question

我有两个声明:

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 的后半部分为真，则应该在选择中使用 a 和 y 而不是 a 和 c。

Answer 1

试试这个

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