Linq to SQL - How to order numbers as strings?

I have a SQL varchar column that has 开发者_运维问答values like 100, 2000 and S5000. I want them ordered numerically rather than alphabetically. If there is a string character I am happy for the character to be either ignored or appear after the numeric values in the sort order.

In T-SQL I could this this with:

SELECT * FROM tbl
ORDER BY
  CASE
    WHEN ISNUMERIC(fld) = 1 THEN CONVERT(INT, fld)
    ELSE 2147483647
    END ASC,
  fld ASC

I am wondering if there is a way to do this in Linq to SQL?

Otherwise I guess my alternatives are to execute the query directly, or create a calculated column with values like 00000100, 00002000,000S5000, etc.

Edit: I have found a solution although I am not sure how efficient it is.

from s in q
orderby (SqlMethods.Like(s.fld, "%[^0-9]%") ? Int32.MaxValue : Convert.ToInt32(s.fld)) ascending, s.fld ascending
select s;

I have found a solution although I am not sure how efficient it is.

from s in q orderby (SqlMethods.Like(s.fld, "%[^0-9]%") ? Int32.MaxValue : Convert.ToInt32(s.fld)) ascending, s.fld ascending select s; 

If you've already returned the results from the database and you're happy to do the sorting in memory then here's a way to go.

First, define a tryParse function that can be used in a linq-to-objects query:

Func<string, int> tryParse = s =>
{
    int i;
    if (!int.TryParse(s, out i))
    {
        i = int.MaxValue;
    }
    return i;
};

Then the actual query to do the sort is simple:

var query =
    from t in tbls.ToArray() // force the linq-to-sql query to execute
    orderby t.fld
    orderby tryParse(t.fld)
    select t;

Easy, huh?

I know the question was about Linq-to-SQL, but I thought I would provide an Entity Framework 4 answer for any EF users that stumbled over this question looking for a way to do this within that framework. I have no experience with Linq-to-SQL, so, for all I know this would actually work there as well (but I doubt it).

Dim results = (From item in ctx.tbl
               Let sortValue = If(SqlFunctions.IsNumeric(item.fld) = 1, CInt(item.fld), 2147483647)
               Order By sortValue).ToList()

This turns roughly into this store query:

SELECT [Project1].*
FROM ( SELECT 
    [Extent1].*
    CASE WHEN (1 = (ISNUMERIC([Extent1].[Value]))) THEN  CAST( [Extent1].[Value] AS int) ELSE 99999999 END AS [C2]
    FROM [dbo].[tbl] AS [Extent1]
)  AS [Project1]
ORDER BY [Project1].[C2] ASC

This solution leverages System.Data.Objects.SqlClient.SqlFunctions to basiclly directly invoke the SQL ISNUMERIC function in the query. I do want to point out that IsNumeric will return 1 even for decimals, and casting a "decimal" varchar into an int will cause a problem. It might be safest to CDbl() instead of CInt()ing the value, unless you know your data will never be anything but an "int" varchar.