Natural Sorting in OData $orderby Query

Question

I'm using [EnableQuery] (System.Web.Http.OData) in ASP.NET API 2 controllers to enable OData v3 filtering/sorting/paging. I've noticed that using the $orderby clause returns data that is sorted as follows (here are some examples - they are strings and don't necessarily have a pattern to them):

LoadTest1000_1
LoadTest1000_10
LoadTest1000_1000
LoadTest1000_2
LoadTest1000_20
[etc]

When I need natural sorting:

LoadTest1000_1
LoadTest1000_2
LoadTest1000_10
LoadTest1000_20
LoadTest1000_1000
LoadTest1000_2000
[etc]

How can I enable this kind of sorting? Are there any extension points that I could use to provide my own sort logic?

Answer 1

I don't think there is a nice way to do this. SQL Server doesn't have built in support for natural sorts in the first place.

That said, you might be able to use a Custom oData Function/Action. For v3 take a look at https://learn.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v3/odata-actions

Otherwise, for v4 https://learn.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/odata-actions-and-functions

I wish you success whoever you are random internet stranger!