Combining 2 Linq queries into 1

Question

Given the following information, how can I combine these 2 linq queries into 1. Having a bit of trouble with the join statement.

'projectDetails' is just a list of ProjectDetails
ProjectDetails (1 to many) PCardAuthorizations
ProjectDetails (1 to many) ExpenditureDetails

Notice I am grouping by the same information and selecting the same type of information

var pCardAccount =  from c in PCardAuthorizations
                    where projectDetails.Contains(c.ProjectDetail)
                        && c.RequestStatusId == 2 
                    group c by new { c.ProjectDetail, c.ProgramFund } into g

                    select new { Key = g.Key, Sum = g.Sum(x => x.Amount) };



var expenditures =  from d in ExpenditureDetails
                    where projectDetails.Contains(d.ProjectDetails)
                        && d.Expenditures.ExpenditureTypeEnum == 0
                    group d by new { d.ProjectDetails, d.ProgramFunds } into g
                    select new {
                        Key = g.Key,
                        Sum = g.Sum(y => y.ExpenditureAmounts.FirstOrDefault(a => a.IsCurrent && !a.RequiresAudit).CommittedMonthlyRecords.ProjectedEac)
                    };

Answer 1

I don't think Mike's suggestion will work here. These two queries are sufficiently different that combining them into one query will just make it more difficult to read.

• The objects have different types.
• The where clauses are different.
• The group by clause is different:
  • new { c.ProjectDetail, c.ProgramFund }
  • new { c.ProjectDetails, c.ProgramFunds }
• The select clauses are different.

In fact there isn't really anything that is the same. I'd recommend leaving it as it is.

Answer 2

You could always just concat them together before evaluating them.

pCardAccount.Concat(expenditures).ToArray()

should generate a single sql statement with a union. As to whether there is any benefit to this in your situation, I don't know. There's also a chance that linq-to-sql won't be able to generate the SQL for the concat and will throw an exception whenever you use it. I'm not sure what causes it, but I've seen it a few times when I was playing around in a similar situation.

Edit: I just noticed that your keys are different in each of the queries. I'm not sure if this is a typo or not, but if it isn't, you won't be able to concat them due to the different types and it wouldn't make any sense to anyway