OrderBy when Grouping

Michael Seery

6y1.4kLINQ

I am having trouble adding an orderby clause to this link query.

var purchaseOrderData = await _context.PurchaseOrders
.Where(s => s.SupplierNumber == "86087319")
.GroupBy(o => new
{
SupplierNumber = o.SupplierNumber,
SupplierName = o.Supplier.SupplierName,
Month = o.OrderDate.Month,
Year = o.OrderDate.Year
})
.Select(g => new
{
SupplierNumber = g.Key.SupplierNumber,
SupplierName = g.Key.SupplierName,
MonthOrd = g.Key.Month,
Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(g.Key.Month),
Year = g.Key.Year,
Count = g.Count(),
OrderAmount= g.Sum(o => o.OrderTotal)
})
.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.

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Asma Khalid6y ago
Accepted answer
Use the Order By clause as follow i.e.

Solution
.Where(...)
.GroupBy(...)
.Select(...)
.ToListAsync();

var lst = purchaseOrderData.OrderBy(a => a.Year).ThenBy(a => a.Month).select(p=>p).ToList();
Thanks & Regards,
Asma Khalid
+2
Asma Khalid6y ago
Yup!! Glad to help.

Thanks & Regards,
Asma Khalid

0
Michael Seery6y ago
Ok, got it.

purchaseOrderData = purchaseOrderData.OrderBy(a => a.Year).ThenBy(a => a.Month).ToList();

Didn't need the additional select statement.

The learning for me is that when projecting to a new list the orderby needs to happen after.
0
Asma Khalid6y ago
Updated the answer
0
Michael Seery6y ago
When adding

.ToListAsync();
purchaseOrderData = purchaseOrderData.OrderBy(a => a.Year).ThenBy(a => a.Month);

I get the following

Cannot implicitly convert type 'System.Linq.IOrderedEnumerable<>' to 'System.Collections.Generic.List<>'. An explicit conversion exists (are you missing a cast?)
+1
Asma Khalid6y ago
I have updated my original answer. The thing is that you have made new projection of your list. So, Orderby clause will not work in the same cascading. You have to do this in new line. OrderBy clause is conflicting between original and new projection and hence it is unable to translate your requirement in cascading operations.
+1
Michael Seery6y ago
Same errors
0
Michael Seery6y ago
The interesting thing is that is works fine without the orderby clauses
0
Michael Seery6y ago
Solution2 with a.Month & a.Year was my original query.

Removing the async and await made no difference
0
Asma Khalid6y ago
I have edited my solutions try them.
0
Michael Seery6y ago
When using

.OrderBy(a => a.Year)
.ThenBy(a => a.Month)
.ToListAsync();

The original error is

Microsoft.AspNetCore.Server.Kestrel[13]
Connection id "0HLVU100DE3RD", Request id "0HLVU100DE3RD:00000001": An unhandled exception was thrown by the application.
System.InvalidOperationException: The LINQ expression 'DbSet
.Where(p => p.SupplierNumber == "86087319")
.Join(
outer: DbSet,
inner: p => EF.Property(p, "SupplierNumber"),
outerKeySelector: s => EF.Property(s, "SupplierNumber"),
innerKeySelector: (o, i) => new TransparentIdentifier(
Outer = o,
Inner = i
))
.GroupBy(
source: p => new {
SupplierNumber = p.Outer.SupplierNumber,
SupplierName = p.Inner.SupplierName,
Month = p.Outer.OrderDate.Month,
Year = p.Outer.OrderDate.Year
},
keySelector: p => p.Outer)
.Select(e => new {
SupplierNumber = e.Key.SupplierNumber,
SupplierName = e.Key.SupplierName,
MonthOrd = e.Key.Month,
Month = __CurrentCulture_DateTimeFormat_0.GetMonthName(e.Key.Month),
Year = e.Key.Year,
Count = e
.Count(),
OrderAmount = e
.Sum(o => o.OrderTotal)
})
.OrderBy(e0 => e0.Year)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.g__CheckTranslated|8_0(ShapedQueryExpression translated, <>c__DisplayClass8_0& )
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.b__0()
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
at WebPortal.API.Controllers.PurchaseOrdersController.GetPurchaseOrdersPerMonth() in D:\Angular\GIT\WebPortal\webportal.api\Controllers\PurchaseOrdersController.cs:line 158
at lambda_method(Closure , Object )
at Microsoft.Extensions.Internal.ObjectMethodExecutorAwaitable.Awaiter.GetResult()
at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.AwaitableObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask`1 actionResultValueTask)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)
at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.HandleException(HttpContext context, ExceptionDispatchInfo edi)
at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)
at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
0
Asma Khalid6y ago
Hello Michael,

Can you share you original error?
0
Michael Seery6y ago
Hi Asma,

Thanks for taking the time to help.

Your 1st solution
var purchaseOrderData = await _context.PurchaseOrders
.Where(s => s.SupplierNumber == "86087319")
.GroupBy(o => new
{
SupplierNumber = o.SupplierNumber,
SupplierName = o.Supplier.SupplierName,
Month = o.OrderDate.Month,
Year = o.OrderDate.Year
})
.Select(g => new
{
SupplierNumber = g.Key.SupplierNumber,
SupplierName = g.Key.SupplierName,
MonthOrd = g.Key.Month,
Month = CultureInfo.CurrentCulture.DateTimeFormat.GetMonthName(g.Key.Month),
Year = g.Key.Year,
Count = g.Count(),
OrderAmount= g.Sum(o => o.OrderTotal)
})
.ToListAsync()
.OrderBy(a => a.Year)
.ThenBy(a => a.Month);
provides and error:
'Task>>' does not contain a definition for 'OrderBy' and no accessible extension method 'OrderBy' accepting a first argument of type 'Task>>' could be found (are you missing a using directive or an assembly reference?)

The 2nd solution
.OrderBy(a => a.Key.Year)
.ThenBy(a => a.Key.Month)
.ToListAsync();
produces the following
'' does not contain a definition for 'Key' and no accessible extension method 'Key' accepting a first argument of type '' could be found (are you missing a using directive or an assembly reference?)
0