Join Subquery result in Linq

join, linq, sql

Solution

Yes, you can join sub queries. Like this:

var query = from f in db.ApplicationFees
            join sub in (from p in db.Payments
                         where p.Type == 1 && p.Position == 1 && 
                               p.Date >= fromDate && p.Date <= toDate
                         group p by p.ApplicationNo into g
                         select new {
                              ApplicationNo = g.Key,
                              CNT = g.Count(),
                              AMNT = g.Sum(x => x.Amount)
                         }) 
           on f.ApplicationNo equals sub.ApplicationNo into feePayments
           select new { Fee = f, Payments = feePayments };

But writing it in single query is not very maintainable. Consider to compose your query from sub-queries defined separately:

var payments = from p in db.Payments
               where p.Type == 1 && p.Position == 1 && 
               p.Date >= fromDate && p.Date <= toDate
               group p by p.ApplicationNo into g
               select new {
                    ApplicationNo = g.Key,
                    CNT = g.Count(),
                    AMNT = g.Sum(x => x.Amount)
              };

var query = from f in db.ApplicationFees
            join p in payments 
               on f.ApplicationNo equals p.ApplicationNo into feePayments
            select new { Fee = f, Payments = feePayments };

Problem

I am posting one more doubt of mine: Is there a way by which we can use the result of one query and then join the same further just like we do in SQL: ``` SELECT Applications.* , ApplicationFees.ApplicationNo, ApplicationFees.AccountFundDate1,ApplicationFees.AccountFundDate2 ,ApplicationFees.AccountFundDate3 , ApplicationFees.AccountCloseDate1, ApplicationFees.AccountCloseDate2,ApplicationFees.AccountCloseDate3, isnull(SUBQRY11.AMNT ,0) as SCMSFEE1R, isnull(SUBQRY12.AMNT,0) as SCMSFEE2R, Left Join ( SELECT ApplicationNo,COUNT(ApplicationNo) AS CNT, SUM(Amount) as AMNT FROM Payments where (FEETYPE=1 AND FeePosition=1) and (FeeDate>='2011-01-01') and (FeeDate<='2012-01-01') GROUP BY ApplicationNo )SUBQRY11 ON ApplicationFees.ApplicationNo= SUBQRY11.ApplicationNo Left Join ( SELECT ApplicationNo,COUNT(ApplicationNo) AS CNT2, SUM(Amount) as AMNT FROM Payments where (FEETYPE=1 AND FeePosition=2) and (FeeDate>='2011-01-01') and (FeeDate<='2012-01-01') GROUP BY ApplicationNo )SUBQRY12 ON ApplicationFees.ApplicationNo=SUBQRY12.ApplicationNo ``` I want to avoid the same in foreach of the query as that will be quite time consuming.

Original source