CODEWITHSUNDEEP
c#asp.net-mvclinqlinq-to-sqlsql-order-by
Darcy
Darcy

Reputation: 5368

Orderby() not ordering numbers correctly c#

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.

Upvotes: 37

Views: 30097

Answers (12)

Leniel Maccaferri
Leniel Maccaferri

Reputation: 102408

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)

Upvotes: 6

Sunil Dhanerwal
Sunil Dhanerwal

Reputation: 11

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

Definitely run......

Upvotes: 1

Amy B
Amy B

Reputation: 110111

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

Upvotes: 27

Olivier Dagenais
Olivier Dagenais

Reputation: 1552

Why are you sorting if you only need "the highest version"? It sounds like you could avoid some overhead if you used Max().

Also, you really should change the column type to integer.

Upvotes: 1

Thomas
Thomas

Reputation: 64645

Why are you sorting in a lambda? Why don't you just sort in the query?

var query = from r in items
            orderby int.Parse( r )
            select r;

Now that we know you are using LINQ to SQL, you might consider making a standard SQL call on this one by doing something like:

Select ..., Cast( TextWhichShouldBeIntCol As int ) As IntCol
From ...

Or even

Select ..., Cast( TextWhichShouldBeIntCol As int ) As IntCol
From ...
Order By Cast( TextWhichShouldBeIntCol As int )

That will bleed into your LINQ as an int (and if you use the second iteration, be ordered). That avoids having to go through the resultset twice in LINQ (once for querying, once for ordering).

Upvotes: 5

Steven Williams
Steven Williams

Reputation: 1014

var items = (from v in results
                    select v).ToList().OrderBy(x => int.Parse(x.Version));

Upvotes: 0

Alxandr
Alxandr

Reputation: 12423

var query = from r in items
            let n = int.Parse(r)
            orderby n
            select n;

Upvotes: 0

chris
chris

Reputation: 37460

It sounds like you have a text value instead of a numeric value.

If you need to sort, you can try:

var items = (from r in results
             select r);
return items.OrderBy( v=> Int.Parse(v.Version) );

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

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).

Upvotes: 6

Mike Jacobs
Mike Jacobs

Reputation: 509

try this:

var items = results.(Select(v => v).OrderBy(v => v.PadLeft(4));

that'll work in Linq2Sql

Upvotes: 1

Yuriy Faktorovich
Yuriy Faktorovich

Reputation: 68667

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.

Upvotes: 7

ILya
ILya

Reputation: 2778

I made a test. I have the following code.

string[] versions = { "1", "2", "10", "12", "22", "30" };
foreach (var ver in versions.OrderBy(v => v))
{
     Console.WriteLine(ver);
}

As expected the result is 1, 10, 12, 2, 22, 30 Then lets change versions.OrderBy(v => v)) to versions.OrderBy(v => int.Parse(v))). And it works fine: 1, 2, 10, 12, 22, 30

I think your problem is that you have nondigit chars in your string like '.'. What kind of exception do you get?

Upvotes: 1

Related Questions