Orderby() not ordering numbers correctly c#

Question

I am writing an app for my company and am currently working on the search functionality. When a user searches for an item, I want to display the highest version (which is stored in a database).

The problem is, the version is stored as a string instead of int, and when I do an OrderBy(q=>q.Version) on the results, they are returned like

1
10
11
2
3
...

Obviously 2 comes before 10.

Is there a way for me to cast the version as an integer or is there a simple IComparer out there? I couldn't find anything substantial thus far.

I tried doing this:

var items = (from r in results
             select r).OrderBy(q => Int32.Parse(q.Version));

This compiles but doesn't work.

Answer 1

Int32.Parse is not supported by the LinqToSql translator. Convert.ToInt32 is supported.

http://msdn.microsoft.com/en-us/library/sf1aw27b.aspx

http://msdn.microsoft.com/en-us/library/bb882655.aspx

Answer 2

Your problem is somewhere else, the following works:

new[] { "1", "10", "2", "3", "11" }
    .OrderBy(i => int.Parse(i))
    .ToList()
    .ForEach(Console.WriteLine);

If your problem is LINQ to SQL, then what is happening is CLR is trying to create SQL out of your LINQ and doesn't understand int.Parse. What you can do is first get the data from SQL then order it once all data is loaded:

var items = (from r in results
             select r)
            .ToList()
            .OrderBy(q => Int32.Parse(q.Version));

Should do it.

Answer 3

If you're unable to change your table definition (so the version is a numeric type), and your query really is as listed (your not using skip, or take, or otherwise reducing the number of results), the best you can do is call "ToList" on the unsorted results, which when you then apply an OrderBY lambda to it will take place in your code, rather than trying to do it at the SQL Server end (and which should now work).

Answer 4

There's an awesome piece of code that does a great job when it comes to natural sorting. Its name is AlphanumComparator.

Sample code:

var ordered = Database.Cars.ToList().OrderBy(c => c.ModelString, new AlphanumComparator());

Note that the list must be in memory.

If you get the C# version, do this:

AlphanumComparator : IComparer<string>

and

public int Compare(string x, string y)