Michael Seery

Michael Seery

  • 1.9k
  • 62
  • 319

OrderBy when Grouping

May 21 2020 10:31 PM
I am having trouble adding an orderby clause to this link query.
  1. var purchaseOrderData = await _context.PurchaseOrders    
  2.         .Where(s => s.SupplierNumber == "86087319")    
  3.         .GroupBy(o => new     
  4.              {     
  5.               SupplierNumber = o.SupplierNumber,    
  6.               SupplierName = o.Supplier.SupplierName,    
  7.               Month = o.OrderDate.Month,     
  8.               Year = o.OrderDate.Year     
  9.               })    
  10.          .Select(g => new    
  11.              {    
  12.               SupplierNumber = g.Key.SupplierNumber,    
  13.               SupplierName = g.Key.SupplierName,    
  14.               MonthOrd = g.Key.Month,    
  15.               Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(g.Key.Month),    
  16.               Year = g.Key.Year,    
  17.               Count = g.Count(),    
  18.               OrderAmount= g.Sum(o => o.OrderTotal)    
  19.               })    
  20.           .ToListAsync();  
The above returns
 
[
{
"supplierNumber": "86087319",
"supplierName": "SUPPLIER 1",
"monthOrd": 2,
"month": "February",
"year": 2019,
"count": 5,
"orderAmount": 414402.86
},
{
"supplierNumber": "86087319",
"supplierName": "SUPPLIER 2",
"monthOrd": 3,
"month": "March",
"year": 2019,
"count": 19,
"orderAmount": 190563.24
},....]
 
I am trying to order this list by year and then month (number) by adding
 
.OrderBy(a => a.Year)
.ThenBy(a => a.Month)
before the .ToListAsync();
 
But the query returns an error indicating that the query cannot be translated.
Can you please help me add the orderby clauses to the above linq query?
Thanks for your help.

Brought to you by:

Answers (13)