Sort Linq list with one column

Question

I guess it should be really simple, but i cannot find how to do it. I have a linq query, that selects one column, of type int, and i need it sorted.

var values = (from p in context.Products
              where p.LockedSince == null
              select Convert.ToInt32(p.SearchColumn3)).Distinct();
values = values.OrderBy(x => x);

SearchColumn3 is op type string, but i only contains integers. So i thought, converting to Int32 and ordering would definitely give me a nice 1,2,3 sorted list of values. But instead, the list stays ordered like it were strings.

199 20 201

Update: I've done some tests with C# code and LinqPad. LinqPad generates the following SQL:

SELECT [t2].[value]
FROM (
    SELECT DISTINCT [t1].[value]
    FROM (
        SELECT CONVERT(Int,[t0].[SearchColumn3]) AS [value], [t0].[LockedSince], [t0].[SearchColumn3]
        FROM [Product] AS [t0]
        ) AS [t1]
    WHERE ([t1].[LockedSince] IS NULL)
    ) AS [t2]
ORDER BY [t2].[value]

And my SQL profiler says that my C# code generates this piece of SQL:

SELECT DISTINCT a.[SearchColumn3] AS COL1                  
FROM [Product] a 
WHERE a.[LockedSince] IS NULL 
ORDER BY a.[SearchColumn3] 

So it look like C# Linq code just omits the Convert.ToInt32. Can anyone say something useful about this?

Answer 1

[Disclaimer - I work at Telerik]

You can solve this problem with Telerik OpenAccess ORM too. Here is what i would suggest in this case.

var values = (from p in context.Products
              where p.LockedSince == null
              orderby "cast({0} as integer)".SQL<int>(p.SearchColumn3)
              select "cast({0} as integer)".SQL<int>(p.SearchColumn3)).ToList().Distinct();

OpenAccess provides the SQL extension method, which gives you the ability to add some specific sql code to the generated sql statement. We have started working on improving this behavior. Thank you for pointing this out.

Regards

Ralph

Answer 2

Same answer as one my other questions, it turns out that the Linq provider i'm using, the one that comes with Telerik OpenAccess ORM does things different than the standard Linq to SQL provider! See the SQL i've posted in my opening post! I totally wasn't expecting something like this, but i seem that the Telerik OpenAccess thing still needs a lot of improvement. So be careful before you start using it. It looks nice, but it has some serious shortcomings.