How to write a Linq query equivalent to this SQL

Question

I'm trying to figure out how to write a linq query that will return equivalent results to the sql query below. The problem I'm having has to do with the two select count queries included in the select list of the main query. I need to get counts of two different types of records from the PaymentHistory table for the last year. Can the equivalent of this be written using linq? Preferrably using lambda syntax.

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

Any help will be great.

score 0 · Answer 1 · answered May 23 '13 at 18:27

0

Perhaps something like this:

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)
}

answered May 23 '13 at 18:27

Magnus

45,362
8
80
118

Thanks for your help Magnus. I wound up doing something a little different. The SQL statement I provided in my original post, if I could get that working in Linq, then I was going to use a second linq query to select the records from the first linq query's results to get the data I really need. I found a way from a colleague to get all the results in one linq query and wanted to share it in case you were interested. Here it is: – Chris May 23 '13 at 21:11
var backdate = DateTime.Today.AddYears(-1); var query = from sa in DataContext.serviceaddrs let Set100 = sa.PaymentHistories.Count(p => p.PostDate >= backdate && p.SetID == 100) let Set101 = sa.PaymentHistories.Count(p => p.PostDate >= backdate && p.SetID == 101) where sa.State == "TX" && !sa.Status.Contains("Closed") && !sa.Status.Contains("Closed") && !sa.Status.Contains("Inactive-WOFF") && !sa.Status.Contains("Cancel") && !sa.Status.Contains("Bucket") && ((Set100 - Set101) < 2) select sa.IEInum; return query.ToList(); – Chris May 23 '13 at 21:14

How to write a Linq query equivalent to this SQL

1 Answers1