Query: Order by with condition evaluating to true/false produces invalid SQL

[slubowsky]

Putting a condition inside an order by is causing invalid SQL. I think I see similar issues but the ones I found were closed months ago and any fixes should be present in version 1.1.1 which is the version I am using so opening this in case its not a duplicate

var values = await _values 
                .OrderBy(f =>  search.Field == "code" ? f.Code : f.Description)
                .Skip((currentPage - 1) * currentPageSize)
                .Take(currentPageSize)
                .ProjectTo<TV>()
                .ToListAsync();

Generates:

SELECT [t].[StartDate], [t].[EndDate], [t].[Id], [t].[Code], [t].[Description]
FROM (
    SELECT [f0].*
    FROM [AssessmentType] AS [f0]
    ORDER BY CASE
        WHEN 0
        THEN [f0].[Code] ELSE [f0].[Description]
    END
    OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
) AS [t]

Results in

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory:Error: An exception occurred in the database while iterating the results of a query.
System.Data.SqlClient.SqlException: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
Incorrect syntax near 'OFFSET'.
Invalid usage of the option NEXT in the FETCH statement.
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__107_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
...

[smitpatel]

I tried reproducing issue based on data I could gather from above but I got no error.
Query:

var require = "code";
var currentPage = 2;
var currentPageSize = 3;
var query = db.Blogs
    .OrderBy(f => require == "code" ? f.Code : f.Description)
    .Skip((currentPage - 1) * currentPageSize)
    .Take(currentPageSize)
    .ToList(); //async version creates same result

Generated SQL

SELECT [f].[Id], [f].[Code], [f].[Description]
FROM [Blogs] AS [f]
ORDER BY CASE
  WHEN @__require_0 = N'code'
  THEN [f].[Code] ELSE [f].[Description]
END
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

@slubowsky - Can you share a complete code listing which demonstrate above issue? There are quite a few pieces missing in your post like what is _values & ProjectTo<TV>(). Also based on condition, EF would parametrize the variables (like search.Field) in the query. But in generated SQL it is showing as 0.

[slubowsky]

_values is just a dbset and ProjectTo() is an Automapper projection, I should have removed it - the bug occurs without it as well.
It really is a pretty vanilla app. I was able to reproduce the bug in a small standalone repo - https://github.com/slubowsky/orderby_bug
If you run the Test.API project it should launch swagger, just click the Try It Out button on the only API method and you should see the issue.

SQL (logged in console)

SELECT [f].[Id], [f].[Cid], [f].[Code], [f].[Description], [f].[EndDate], [f].[StartDate]
FROM [AssessmentType] AS [f]
ORDER BY CASE
    WHEN 0
    THEN [f].[Code] ELSE [f].[Description]
END
OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY

Result