how to make a LINQ query return multiple count() or sum() columns?

Question

 

Hi everyone, I'm pretty new to LINQ and I've run across a problem that I can't seem to fix myself. I have a working SQL query that looks like this:

 

Code Snippet

 

SELECT COUNT(dbo.PDI.coilId) AS NumberOfCoils,

ROUND(SUM(dbo.GagTrends.coilLength), 0) AS TotalLength

FROM dbo.PcProcStat

INNER JOIN dbo.PDI ON dbo.PcProcStat.cid = dbo.PDI.cid

INNER JOIN dbo.GagTrends ON dbo.PDI.cid = dbo.GagTrends.cid

WHERE (dbo.PcProcStat.trMndClpsd_time BETWEEN @DateStart AND @DateEnd)

AND ((dbo.GagTrends.aliasId = '620') OR (dbo.GagTrends.aliasId = '9463'))

 

 

And here is my LINQ query:

 

Code Snippet

var ShiftQuery = from pcProcStat in TACoilSummary.TAPcProcStats

  join pdi in TACoilSummary.TAPDIs on pcProcStat.cid equals pdi.cid

  join gagTrends in TACoilSummary.TAGagTrends on pcProcStat.cid equals

gagTrends.cid

  where ((Convert.ToDateTime(pcProcStat.trMndClpsd_time) >= StartDate)

&& (Convert.ToDateTime(pcProcStat.trMndClpsd_time) <= EndDate))

&& ((gagTrends.aliasId == 620) || (gagTrends.aliasId == 9463))

  select new

  {

coilsNum = TACoilSummary.TAPDIs.Count(p => (p.coilId != "")),

coilLength = TACoilSummary.TAGagTrends.Sum(p => p.coilLength)

  };

 

 

 

I can't seem to get the same functionality out of LINQ. Everything I have tried doesn't yield the intended results. I can get it to work if I just enclose the whole LINQ query in parentheses and put a .Count() at the end of it, but that only returns one of my results and it would seem redundant to have to do the same thing with a .Sum() just to get the other one.  Does anyone have any pointers on how to do this?

Answer 1

See Jon Skeet and Marc Gravell's approach. It may be what you need.

Answer 2

Sine aggregates only operate on sequences, and a query represents a single sequence, LINQ does not give you a good way to describe computing multiple aggregates at once without first partitioning the data into groups with a 'group by' operation that will turn your sequence in a sequence of sequences.

 

You can still do what you want, however, you have to use a constant group by to do it.

 

var ShiftQuery = from pcProcStat in TACoilSummary.TAPcProcStats

                        join pdi in TACoilSummary.TAPDIs on pcProcStat.cid equals pdi.cid

                        join gagTrends in TACoilSummary.TAGagTrends on pcProcStat.cid equals gagTrends.cid

                        where ((Convert.ToDateTime(pcProcStat.trMndClpsd_time) >= StartDate)

                              && (Convert.ToDateTime(pcProcStat.trMndClpsd_time) <= EndDate))

                              && ((gagTrends.aliasId == 620) || (gagTrends.aliasId == 9463))

                        group gagTrends.coilLength by 1 into g

                        select new

  {

NumberOfCoils = g.Count(),

TotalLength = g.Sum(x => x.coilLength)

  };