Combining two select statements

Question

I have two statements as such:

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

and I'm wanting to be able to combine them into one; as currently it loops through the database twice - which is very inefficient. I've tried researching how to put multiple select statements within the where clause but I can't seem to find anything that I can understand.

Can anyone shed some light on my situation?

I tried modifying an answer below:

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

However as I'm trying to access different joins based on the where clause, I can't change that in the select statement; for example, if the second half of the OR is true, it should use a and y in the select rather than a and c.

Answer 1

Try to this

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

Answer 2

You can try this:

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) ||
              (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
                  }).Distinct();