SQL 在 Linq 中加入子查询结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16166151/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Join Subquery result in Linq
提问by JulyOrdinary
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:
我发布了我的另一个疑问：有没有一种方法可以使用一个查询的结果，然后像我们在 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.
我想在 foreach 的查询中避免相同的情况，因为这将非常耗时。
回答by Sergey Berezovskiy
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 definedseparately:
但是在单个查询中编写它不是很容易维护。考虑从单独定义的子查询组成您的查询：
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 };
