IQueryable LINQ ordering and grouping

Question

I have the following (incorrect) LINQ function (updated):

public IQueryable<ClassUsageReport> GetClassUsage()
    {
        //Class Code, Title, Usage, FiscalYear
        var queryable = (from agencyplan in _agencyPlansList
                         join classSchedule2012 in _classSchedule2012List
                            on agencyplan.Id equals classSchedule2012.AgencyPlanId
                         join classes in _classesList
                            on classSchedule2012.Class.Id equals classes.Id
                         orderby agencyplan.PlanYear.FiscalYear descending, classes.ClassCode.Count() descending
                         group agencyplan by new
                         {
                             agencyplan.PlanYear.FiscalYear,
                             classes.ClassCode,
                             classes.Title 
                         } into gcs
                         select new ClassUsageReport
                         {
                             ClassCode = gcs.Key.ClassCode,
                             Title = gcs.Key.Title,
                             Usage = gcs.Key.ClassCode.Count(),
                             FiscalYear = gcs.Key.FiscalYear 
                         }
                        );

        return queryable.AsQueryable();
    }

I am having trouble with the Group By and Order By clauses. Also with the COUNT().

I have written the correct SQL statement, that produces the results as needed (and expected):

select py.fiscalyear, c.classcode, c.title, count(c.classcode) as usage from classschedule2012 cs
inner join classes c on cs.class_id = c.id
inner join agencyplans ap on cs.agencyplanid = ap.Id 
inner join planyears py on ap.planyear_id = py.id
group by py.fiscalyear, c.classcode, c.title
order by py.fiscalyear desc, usage desc

What am I doing wrong with the grouping and ordering in my LINQ statement? I would like it to include "usage" like my SQL has. How can I get count to properly reflect the true count? As the query is at the moment, it only returns "9" in every row. This does not match my SQL, as the real results should be "55, 44, 14, 13" etc....

EDIT: 11/14/2013

Here is the final result:

   public IQueryable<ClassUsageReport> GetClassUsage()
   {
        //Class Code, Title, Usage, FiscalYear
        var queryable = (from agencyplan in _agencyPlansList
                         join classSchedule2012 in _classSchedule2012List
                            on agencyplan.Id equals classSchedule2012.AgencyPlanId
                         join classes in _classesList
                            on classSchedule2012.Class.Id equals classes.Id
                         where classes.Active = true
                         orderby agencyplan.PlanYear.FiscalYear descending
                         group agencyplan by new
                         {
                             agencyplan.PlanYear.FiscalYear,
                             classes.ClassCode,
                             classes.Title
                         } into gcs
                         select new ClassUsageReport
                         {
                             ClassCode = gcs.Key.ClassCode,
                             Title = gcs.Key.Title,
                             Usage = gcs.Count(),
                             FiscalYear = gcs.Key.FiscalYear
                         }
                        );

        return queryable.AsQueryable().OrderByDescending(x => x.FiscalYear).ThenByDescending(x => x.Usage);
    }

score 3 · Accepted Answer · answered Jun 24 '13 at 17:22

3

Once you group, you only have access to the columns you've grouped by and aggregate data of the other columns (like SUM or COUNT). In your query's select portion, you need to use g. instead of classes..

answered Jun 24 '13 at 17:22

Garrison Neely

3,238
3
27
39

so essentially I need to do multiple queries to make this happen? – TheOptimusPrimus Jun 24 '13 at 17:57
Nope, you can group by multiple columns just like your SQL query. Check this question: http://stackoverflow.com/questions/5231845/c-sharp-linq-group-by-on-multiple-columns – Garrison Neely Jun 24 '13 at 19:26
Thanks, that helped tremendously. I updated my question. I almost have the linq query finished, except I do not know how to get [usage] field to properly use a count on ClassCode? All I get is "9" in every row for usage...any clues on doing a Count on that in this query? – TheOptimusPrimus Jun 24 '13 at 20:17
usage is always 9, because thats the length of the string value. how can I count (aggregate) the field, and incorporate it in this query? – TheOptimusPrimus Jun 24 '13 at 20:28
I believe the correct syntax would be `gcs.Count(x=>x.ClassCode)`. Try that. – Garrison Neely Jun 24 '13 at 20:28
Thank you. Its actually gcs.Count(), that works as well. Thanks for your help! If I had enough points to upvote this, I would (and will). – TheOptimusPrimus Jun 24 '13 at 20:34

score 2 · Answer 2 · answered Jun 24 '13 at 17:22

2

Try using g.Key in the select statement.

answered Jun 24 '13 at 17:22

Wouter van der Houven

448
3
9

IQueryable LINQ ordering and grouping

EDIT: 11/14/2013

2 Answers2