Efcore: Sort by decimal no longer working in 3.0

[bobcat1506]

Sorting by a decimal column is causing System.NotSupportedException: SQLite cannot order by expressions of type 'decimal'.

This was working in 2.2.

To Reproduce

Execute a query and specify .OrderBy(c=> c.DecimalColumn)

Stack Trace

2019/10/25 10:53:08.049|INFO|18|Executed endpoint 'SpinDrift.Web.Controllers.CaliberController.GetAll (SpinDrift.Web)' |Microsoft.AspNetCore.Routing.EndpointMiddleware|EndpointName=SpinDrift.Web.Controllers.CaliberController.GetAll (SpinDrift.Web), EventId_Id=1, EventId_Name=ExecutedEndpoint, EventId=ExecutedEndpoint
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware: Error: An unhandled exception has occurred while executing the request.

System.NotSupportedException: SQLite cannot order by expressions of type 'decimal'. Convert the values to a supported type or use LINQ to Objects to order the results.
at Microsoft.EntityFrameworkCore.Sqlite.Query.Internal.SqliteQueryableMethodTranslatingExpressionVisitor.TranslateOrderBy(ShapedQueryExpression source, LambdaExpression keySelector, Boolean ascending)
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 Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
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 Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutorTResult
at Microsoft.EntityFrameworkCore.Storage.Database.CompileQueryTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCoreTResult
at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_01.<Execute>b__0() at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func1 compiler)
at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryTResult
at SpinDrift.Core.Infrastructure.DataAccess.Repositories.ReadOnlyDbRepository3.ExecutePagedQuery(IQueryable1 query, PagedSearchContext context) in d:SourceReposSpinDriftsrcSpinDrift.Core.InfrastructureDataAccessRepositoriesReadOnlyDbRepository.cs:line 95
at SpinDrift.Core.Infrastructure.DataAccess.Repositories.ReadOnlyDbRepository3.PagedSearchAsync(String userId, PagedSearchContext context) in d:\Source\Repos\SpinDrift\src\SpinDrift.Core.Infrastructure\DataAccess\Repositories\ReadOnlyDbRepository.cs:line 77 at SpinDrift.Core.ApplicationServices.EntityApplicationService5.PagedSearchAsync(String userId, PagedSearchContext context) in d:SourceReposSpinDriftsrcSpinDrift.Core.ApplicationServicesEntityApplicationService.cs:line 39
at SpinDrift.Web.Controllers.Base.ApiControllerBase3.GetAll(PagedRequest queryParameters) in d:\Source\Repos\SpinDrift\src\SpinDrift.Web\Controllers\Base\ApiEntityControllerBase.cs:line 36 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 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|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync()
--- End of stack trace from previous location where exception was thrown ---
at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Logged|17_1(ResourceInvoker invoker)
at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
at IdentityServer4.Hosting.IdentityServerMiddleware.Invoke(HttpContext context, IEndpointRouter router, IUserSession session, IEventService events)
at IdentityServer4.Hosting.MutualTlsTokenEndpointMiddleware.Invoke(HttpContext context, IAuthenticationSchemeProvider schemes)
at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
at IdentityServer4.Hosting.BaseUrlMiddleware.Invoke(HttpContext context)
at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Additional context

Microsoft.Data.Sqlite version: 3.0
Target framework: (e.g. .NET Core 3.0)
Operating system: Win 10

[smitpatel]

https://docs.microsoft.com/en-us/ef/core/providers/sqlite/limitations#query-limitations

[ajcvickers]

@bobcat1506 Some comments in addition to the link @smitpatel posted. This was working in 2.2 through automatic client evaluation--that is, all the results were being brought back from the database and the ordering was done in memory. EF Core 3.0 specifically doesn't do this automatically, but the breaking change documentation shows how to change your code to get the same behavior if you want it.

[BrightSoul]

I don't understand. An OrderBy(c=> c.DecimalColumn) should just append "ORDER BY DecimalColumn" to the SQL query. Why does it matter it's a decimal?
The following SQL query runs just fine on a Sqlite database.

SELECT * FROM Table ORDER BY DecimalColumn;

@ajcvickers

EF Core 3.0 specifically doesn't do this automatically, but the breaking change documentation shows how to change your code

Please clarify. Is this what you were referring to?

Mitigations
If a query can't be fully translated, then either rewrite the query in a form that can be translated, or use AsEnumerable(), ToList(), or similar to explicitly bring data back to the client where it can then be further processed using LINQ-to-Objects.

Suppose there's 100k rows and I just need to fetch the first page of 20 results sorted by that decimal column. How is this a mitigation? It would force me to retrieve the whole dataset first.

Moreno

[ajcvickers]

@BrightSoul It matters because SQLite can't store decimal values natively. They must either go through a non-lossy conversion to a text value or a potentially lossy conversion to floating point value. When using the non-lossy conversion, the sort order of those strings is different from the sort order of the resulting decimals, so performing the sort on the server may give the wrong results.

If the data in the column is actually stored as a floating point numbers, then telling EF this explicitly should allow it to run the ordering on the server. Something like:
C# modelBuilder.Entity<Foo>().Property(e => e.Bar).HasConversion<double>();

[BrightSoul]

@ajcvickers Thank you so much for the solution, it worked perfectly.

the sort order of those strings is different from the sort order of the resulting decimals, so performing the sort on the server may give the wrong results.

Indeed, but _why_ does EFCore care about this?
The .HasConversion<double>(); is, of course, useful for code-first since the migration has know whether it should generate a NUMERIC or a TEXT column. But suppose I'm not using migrations. I just mapped my conceptual model by hand for an existing database. When I use .OrderBy(x => x.DecimalColumn) in a LINQ query, shouldn't the LINQ provider just produce a ORDER BY t0.DecimalColumn statement? Then, if the column is a TEXT, it will be sorted server side alphabetically. If it's a NUMERIC, it will be sorted numerically. In the end, it's a decison made by the person who defined that column, who took into account Sqlite quirks. EFCore shouldn't care about how rows are sorted server side, that was my point. We're not converting values in an OrderBy.

Again, thanks.
Moreno