0

Suppose we have a simplified Orders entity, with this properties: Id (int, PK), orderDate (datetime, not null) and productCategory (string, not null).

What would be the LINQ to Entities query that returns the count of orders for each category, grouped by month, for the past 12 months, ordered by year, month?

The output should look like this, in order to pass it as series for a Highcharts lines chart, using JQuery.getJSON():

[{
   name: 'Dairy',
   data: [23, 27, 32, 44, 21, 30, 11, 0, 9, 24, 3, 19]
 },
 {
   name: 'Frozen',
   data: [11, 4, 0, 6, 8, 10, 17, 24, 18, 8, 23, 10]
}]

That is, 12 values for each category, including zeros.

Nicolás Lope de Barrios
  • 143
  • 1
  • 9

2 Answers2

1

This seems to work:

var orders = from o in orderList.ToList().Where(x => x.orderDate >= DateTime.Now.AddYears(-1)).GroupBy(x => x.productCategory)
        select new {
            name = o.Key,
            data = ((Func<int[]>)(() => {
                var months = new int[12];        
                for (int i = 0; i < 12; i++) {
                    months[i] = o.Where(x => x.productCategory == o.Key && x.orderDate.Month == i).Count();
                }
                return months;
            }))()
        };

First we filter the orders for anything with a date in the last year. Then we group the results by the productCategory field. From this grouping, we create an anonymous projection class. Here we set the name field equal to the grouping key (the category). In order to fill the data field, we use an anonymous function to enumerate each month and count up each record that matches the month number and product category which we have grouped by.

This allows us to put 0 counts for months that we don't have data for.

ohlando
  • 321
  • 1
  • 7
  • Hi ohlando, and thank you. I am getting the error: `A lambda expression with a statement body cannot be converted to an expression tree` after `((Func)(`. Any suggestions? – Nicolás Lope de Barrios Jul 15 '14 at 21:47
  • I believe you were having an issue because your collection was not an IEnumerable. I've updated the answer in case you are interested to convert it prior to executing the query. – ohlando Jul 16 '14 at 03:38
0
 var months = Enumerable.Range(1, 12);
 var max = DateTime.Now.AddYears(-1);
 var result = data.Where(d => d.OrderDate >= max)
                .GroupBy(d => d.ProductCategory)
                .Select(g =>
                        new
                        {
                            Name = g.Key,
                            Data =( 
                            from m in months
                            join  d in
                                g.OrderBy(gg => gg.OrderDate.Year)
                                .ThenBy(gg => gg.OrderDate.Month)
                                .GroupBy(gg => gg.OrderDate.Month)
                                on m equals d.Key into gj
                                from j in gj.DefaultIfEmpty()
                                    select j.Key != null ? j.Count() : 0)
                        }).ToArray();
Nicolás Lope de Barrios
  • 143
  • 1
  • 9
AD.Net
  • 13,352
  • 2
  • 28
  • 47
  • It _almost_ Works, except for 3 things: LINQ to Entities does not accept `DateTime.Now.AddYears()`. I used `EntityFunctions.AddYears(DateTime.Today, -1))` instead. `ToArray()` was invalid in that context, I extracted it out, after `query.Execute()`, and lastly, the only thing I couldn't fix, is that it doesn't return 0 if there are no Orders for that month. – Nicolás Lope de Barrios Jul 15 '14 at 21:07
  • You can do DateTime.Now.AddYears() before and use the variable. I'll update the answer later for the 0 for no orders, basically you can do a left join with an array of months [1-12] and add 0 as count when empty – AD.Net Jul 15 '14 at 21:57
  • Thanks a lot AD.Net. Unfortunately this error is thrown: `Unable to create a constant value of type 'Anonymous type'. Only primitive types or enumeration types are supported in this context.` One possible cause is described here: [link](http://stackoverflow.com/questions/18929483/unable-to-create-a-constant-value-of-type-only-primitive-types-or-enumeration-ty) – Nicolás Lope de Barrios Jul 16 '14 at 02:48
  • Check it now, there is really no need to create an anonymous type for months, I was just testing different things. It should be fine now. – AD.Net Jul 16 '14 at 02:51
  • You are close. I got a `DataService Operation Exception: Cannot compare elements of type 'System.Linq.IGrouping'2'. Only primitive types, enumeration types and entity types are supported.` Thanks for your time and sorry to bother you. – Nicolás Lope de Barrios Jul 16 '14 at 03:06
  • I fixed it, changed `select j != null ? j.Count() : 0)` to `select j.Key != null ? j.Count() : 0)`. It works! Thanks again, AD.Net. I will update you answer. – Nicolás Lope de Barrios Jul 16 '14 at 03:15
  • Thanks, I was trying with `in-memory list`, thanks for updating it. – AD.Net Jul 16 '14 at 03:29
  • Testing the query with real data I discovered a new issue: it always returns the `data[]` array ordered by month, from 1 to 12, instead of OrderDate.Year and then by OrderDate.Month. For example, for data 12 months back from today, it should start with the count of orders of `july'13` to `june'14`. Any suggestions? – Nicolás Lope de Barrios Jul 16 '14 at 20:21
  • You probably should get more data in the anonymous object form, for example, Month, Year and Count and then do the proper sorting at the end. – AD.Net Jul 16 '14 at 20:34