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?

Is there a typo?... You are sorting the `diameters` variable but are selecting into the `values` variable. Should it read `diameters = values.OrderBy(x => x);`? — Quintin Robinson, Aug 18 '11 at 21:23
Maybe because you're sorting `diameters` instead of `values`? — Allon Guralnek, Aug 18 '11 at 21:24
You're selecting into `values` but ordering `diameters`. Is this just a copy editing problem or is it your actual code? — ChrisF, Aug 18 '11 at 21:24
sorry, wanted to make my example clearer, but forgot to rename everything to values. I updated the question, now its like it should — Tys, Aug 18 '11 at 21:27
Can you see what the generated SQL is with LinqPad? I'm wondering if the string-to-int conversion isn't happening before the sort in the generated query. — cdhowie, Aug 18 '11 at 21:30
I've tried this in LinqPad, and that gives me exactly what i want. But when i do the same thing in C# code.. i get this problem. — Tys, Aug 18 '11 at 21:41
What provider are you using in your actual code? LINQ to SQL? — svick, Aug 18 '11 at 23:14
I can't replicate your issue. You need to provide more detail or your actual source code - not a "clearer" example. — Enigmativity, Aug 19 '11 at 04:58
I've updated my question with the SQL LinqPad and C# generates. Seems like my C# code doesn't do much to produce great SQL. It omits the Convert. — Tys, Aug 20 '11 at 12:14
I repeat my question: What provider are you using in your actual code? LINQ to SQL? — svick, Aug 20 '11 at 13:10
Hi Svick, i'm using the Telerik OpenAccess ORM, is that what you mean? Or could that cause a difference or problem? I wasn't aware of the fact that i'm using some other than standard Linq provider. But now that you're asking about it, i'm thinking, could this be the problem? — Tys, Aug 20 '11 at 17:22
Yeah, that's probably where the problem lies. LINQPad uses LINQ to SQL by default. And the provider is the one generating the SQL. — svick, Aug 20 '11 at 17:33

Ralph · Answer 1 · 2011-08-25T12:04:03.963

[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

score 2 · Accepted Answer · answered Aug 21 '11 at 09:27

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.

score 1 · Answer 3 · answered Aug 19 '11 at 04:03

I can't replicate this problem. But just make sure you're enumerating the collection when you inspect it. How are you checking the result?

values = values.OrderBy(x => x);
foreach (var v in values)
{
    Console.WriteLine(v.ToString());
}

Remember, this won't change the order of the records in the database or anywhere else - only the order that you can retrieve them from the values enumeration.

score -2 · Answer 4 · answered Aug 19 '11 at 03:46

Because your values variable is a result of a Linq expression, so that it doest not really have values until you calling a method such as ToList, ToArray, etc.

Get back to your example, the variable x in OrderBy method, will be treated as p.SearchColumn3 and therefore, it's a string.

To avoid that, you need to let p.SearchColumn3 become integer before OrderBy method. You should add a let statement in to your code as below:

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

In addition, you can combine order by statement with the first, it will be fine.

-1 the `values` variable only contains `Int32` types - it doesn't contain strings. Your suggested code does nothing different to the OPs code. — Kirk Broadhurst, Aug 19 '11 at 03:57

Sort Linq list with one column

4 Answers4