Odata produces invalid SQL Statement 'Invalid column name 'c'.'

huf1 commented 5 years ago

When calling odata endpoint with $orderby and $top parameters, the generated SQL Statement is invalid. Invalid column name 'c'.

Assemblies affected

Microsoft.AspNetCore.OData 7.1.0

Reproduce steps

I'm calling for example: http://localhost:5000/odata/Test/Test()?$skip=0&$orderby=BusinessUnit%20desc&$top=50 to get a 'BusinessUnit' ordered list

The result SQL Statement on SQL Server Database is:

exec sp_executesql N'SELECT TOP(@__p_4) [t].*
FROM (
    SELECT CAST([dtoProductGroup.BusinessUnit].[Type] AS bigint) AS [BusinessUnit], [dtoProductGroup].[DataSource], [dtoProductGroup].[Id], [dtoProductGroup].[Key], [dtoProductGroup.BusinessUnit].[Type]
    FROM [ProductGroup] AS [dtoProductGroup]
    LEFT JOIN [BusinessUnit] AS [dtoProductGroup.BusinessUnit] ON [dtoProductGroup].[BusinessUnitId] = [dtoProductGroup.BusinessUnit].[Id]
    ORDER BY [c] DESC, [dtoProductGroup].[Id]
    OFFSET @__TypedProperty_2 ROWS FETCH NEXT @__TypedProperty_3 ROWS ONLY
) AS [t]
ORDER BY [t].[Type] DESC, [t].[Id]',N'@__p_4 int,@__TypedProperty_2 int,@__TypedProperty_3 int',@__p_4=1001,@__TypedProperty_2=0,@__TypedProperty_3=50

The statement orders by a column [c], which is not present

If the $top parameter is removed, it works.

Expected result

Resulting SQL Statement should be valid and return a ordered list by 'BusinessUnit'

Actual result

SQL Exception 'Invalid column name 'c'.' is thrown.

xuzhg commented 5 years ago

@hrf1 It looks it is related to the stable sort strategy by design. Is 'c' your entity key? Does it work if you set 'EnsureStableOrdering = false'? Thanks.

pmispel commented 5 years ago

@hrf1 It looks it is related to the stable sort strategy by design. Is 'c' your entity key? Does it work if you set 'EnsureStableOrdering = false'? Thanks.

Same issue here. It does work when 'EnsureStableOrdering = false'. But we would like this option enabled. 'c' is not part of any entity key.

using Microsoft.AspNetCore.App 2.2.0 Microsoft.AspNetCore.OData 7.2.1 Microsoft.AspNetCore.OData.Versioning 3.2.4