cast from string to int doesn't work

[AndriiLesiuk]

OData 8.2.3

I'm trying something like:

http://localhost:5066/odata/Table?$count=true&$top=100&$orderby=cast(source_id,Edm.Int32)

And I've got this error:

{
    "error": {
        "code": "",
        "message": "The query specified in the URI is not valid. Only ordering by properties is supported for non-primitive collections. Expressions are not supported.",
        "details": [],
        "innererror": {
            "message": "Only ordering by properties is supported for non-primitive collections. Expressions are not supported.",
            "type": "Microsoft.OData.ODataException",
            "stacktrace": "   at Microsoft.AspNetCore.OData.Query.OrderByPropertyNode..ctor(OrderByClause orderByClause)\n   at Microsoft.AspNetCore.OData.Query.OrderByNode.CreateCollection(OrderByClause orderByClause)\n   at Microsoft.AspNetCore.OData.Query.OrderByQueryOption.get_OrderByNodes()\n   at Microsoft.AspNetCore.OData.Query.Validator.OrderByQueryValidator.Validate(OrderByQueryOption orderByOption, ODataValidationSettings validationSettings)\n   at Microsoft.AspNetCore.OData.Query.Validator.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.ValidateQuery(HttpRequest request, ODataQueryOptions queryOptions)\n   at Microsoft.AspNetCore.OData.Query.EnableQueryAttribute.OnActionExecuting(ActionExecutingContext actionExecutingContext)"
        }
    }
}

This is my field:

[Key]
[ODataAnnotation(ODataMetadataTerm.Comparable)]
public string source_id { get; set; }

and my OData config as well:

 public static ODataOptions AddODataModel(this ODataOptions? options)
 {
     options ??= new ODataOptions();
     options.EnableQueryFeatures(3000);
     options.SkipToken().AddRouteComponents(routePrefix: ODataConfiguration.RoutePrefix, model: EdmModel, services =>
     {
         services.AddSingleton<ISearchBinder, SearchBinder>();
         services.AddSingleton<ODataBatchHandler>(GetBatchHandler());
         services.AddSingleton<SkipTokenHandler, CustomSkipTokenHandler>();
     });
     options.RouteOptions.EnableNonParenthesisForEmptyParameterFunction = true;
     options.EnableAttributeRouting = true;
     return options;
 }

Is it even possible to do such operations? Thanks!

[julealgon]

Apparently, it should work:

• https://services.odata.org/V4/TripPinService/People?$orderby=cast(Concurrency, Edm.Int64) desc,UserName

In a sense, this is a very similar request to this one:

• https://github.com/OData/AspNetCoreOData/issues/1126#issuecomment-1841005232

It looks like it could be generalized to "supporting functions in the $orderby clause" or something like that.

[AndriiLesiuk]

Apparently, it should work:

• https://services.odata.org/V4/TripPinService/People?$orderby=cast(Concurrency, Edm.Int64) desc,UserName

In a sense, this is a very similar request to this one:

• Support tolower() in $orderby option #1126 (comment)

It looks like it could be generalized to "supporting functions in the $orderby clause" or something like that.

@julealgon well, but we don’t know does a Concurrency field is string in this example.

I found this: OData doesn't support conversion to and from string. It supports conversions between related entity types and between numeric primitive types only. See http://msdn.microsoft.com/en-us/library/dd541472(v=PROT.10).aspx and the castExpression paragraph. It's very similar to the cast operator in C# for example.

https://stackoverflow.com/questions/8456013/casting-int-to-string-in-odata-raises-error

[julealgon]

@julealgon well, but we don’t know does a Concurrency field is string in this example.

I wasn't testing for the behavior @AndriiLesiuk , just that it accepted a cast expression or not. The exception message you got implies that expressions just cannot be used in the $orderby, while the example there proves otherwise, meaning this is just a gap in the .NET implementation but should work from a spec perspective.

I found this: OData doesn't support conversion to and from string. It supports conversions between related entity types and between numeric primitive types only. See http://msdn.microsoft.com/en-us/library/dd541472(v=PROT.10).aspx and the castExpression paragraph. It's very similar to the cast operator in C# for example.

https://stackoverflow.com/questions/8456013/casting-int-to-string-in-odata-raises-error

Fair enough. Again, I wasn't necessarily testing for your specific "string" -> "int" use case there, just that any conversion operation would even potentially work at all.

[gathogojr]

Thanks @AndriiLesiuk for reporting this issue. We currently don't support expressions in the $orderby query option. This is a feature gap. We'll add the issue to our backlog. We also accept pull request contribution if you're in a position to contribute.

[aboryczko]

Why would you want to order by an expression? it doesn't scale for larger datasets. If you need to sort your strings by their number values why wouldn't you just add it as a number column? it takes less bytes to store, number comparisons are much faster than string ones and so on.

[AndriiLesiuk]

Why would you want to order by an expression? it doesn't scale for larger datasets. If you need to sort your strings by their number values why wouldn't you just add it as a number column? it takes less bytes to store, number comparisons are much faster than string ones and so on.

Because there are certain requirements from the customer, which, unfortunately, cannot be influenced.