Linq to SQL - how to sort a varchar field that contains numbers and hex values

I have a client-supplied datatable that has a set of group codes in it. In group 1 I have 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 and further on 1A, 2A and so on.开发者_Python百科

The datatype is currently varchar(10) and the client wants the sort order to come out as: 1 2 3 4 5 6 7 8 9 10 1A 2A ...

but instead it comes out: 1 10 1A 2 2A 3 4 5 6 7 8 9 ...

Is there any way I can get this to come out in the correct order please?

Are you sure that you will have only numbers in this column (in the hexadecimal format but it doesn't matter)? If yes then it would be better to change table structure and store number itself (and format it only for viewing/editing). In such case sorting can be performed easily on the server side (I mean DB server).

Generally you can achieve this by parsing the strings to a number and specifying System.Globalization.NumberStyles.AllowHexSpecifier.

Since this is LINQ to SQL there won't be a translation for this. Therefore you'll need to apply sorting after the rest of your query is done and execute it immediately via AsEnumerable or ToList. Bear in mind that by doing so all the matching data requested by the query will be sent to the client, so it is important to filter up front then apply the sorting on the client side.

Your query would be similar to this:

dc.Table
  .Select(o => o.Number) // where number is the property/column name
  .AsEnumerable()
  .OrderBy(s => Int32.Parse(s, System.Globalization.NumberStyles.AllowHexSpecifier))

Here's an example of this in action without LINQ to SQL:

string[] numbers = { "1", "10", "1A", "2", "2A", "3", "4", "5", "6", "7", "8", "9" };
var query = numbers.OrderBy(s => Int32.Parse(s, System.Globalization.NumberStyles.AllowHexSpecifier));
foreach (var s in query)
    Console.WriteLine(s);