locked
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?

    Wednesday, January 30, 2008 3:15 PM

Answers

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

      };

    Thursday, January 31, 2008 3:58 PM

All replies

  • See Jon Skeet and Marc Gravell's approach. It may be what you need.
    Wednesday, January 30, 2008 5:01 PM
  • 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)

      };

    Thursday, January 31, 2008 3:58 PM