如何编写与此SQL等价的Linq查询

Question

我正试图找出如何编写一个linq查询，将返回等效的结果到下面的sql查询。我所遇到的问题与主查询的选择列表中包含的两个选择计数查询有关。我需要从去年的PaymentHistory表中获得两种不同类型记录的计数。是否可以使用linq编写与此等价的代码?最好使用lambda语法。

select ieinum, serviceaddrkey,
  (select count(*) from PaymentHistory where serviceaddrid = serviceaddrkey
   and PostDate >= DateAdd(year, -1 , GetDate())
   and SetID = 100) as ReturnedFees,
(select count(*) from PaymentHistory where serviceaddrid = serviceaddrkey
   and PostDate >= DateAdd(year, -1 , GetDate())
   and SetID = 101) as CorrectedReturnedFees
from Serviceaddr 

Answer 1

可能是这样的:

from s in Serviceaddr
let ph = PaymentHistory.Where(p => s.serviceaddrkey == p.serviceaddrkey &&
                                   p.PostDate >= DateTime.Now.AddYears(-1))
select new
{
  s.ieinum,
  s.serviceaddrkey,
  ReturnedFees = ph.Count(p => p.SetID == 100),
  CorrectedReturnedFees = ph.Count(p => p.SetID == 101)
}