在Linq中加入子查询结果 [英] Join Subquery result in Linq
本文介绍了在Linq中加入子查询结果的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对我的另一个疑问是: 有没有一种方法可以使用一个查询的结果,然后像在SQL中一样将其进一步联接:
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
我想避免在foreach查询中使用相同的方法,因为这将非常耗时.
I want to avoid the same in foreach of the query as that will be quite time consuming.
推荐答案
是的,您可以加入子查询.像这样:
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 };
这篇关于在Linq中加入子查询结果的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文