Search code examples
c#linq-to-sqlfloating-pointsql-order-byvarchar

Order by String as Float

I have a table with a field called 'length', of type varchar(10). These fields contains "real numbers" (fraction-numbers with a decimal point. e.g. 647.45224) - expressed as string. I want to call a Linq-to-SQL query that will order the results of a SELECT, by this field (in its numerical representation), like with this "native" SQL query:

SELECT * FROM [Table] ORDER BY CAST(length AS float) DESC

I've tried several methods of telling LinqToSQL to convert the string into a float, but when running it - it returns a conversion error (note that there are no intellisense errors, nor errors during compilation, only when executing).

I've tried:

Results = dbx.[Items].Where([conditions]).OrderByDescending(b => float.Parse(b.length))

I will also settle for a rounding and conversion to Int and sorting by that (though I'd prefer to use the real numbers of-course), I tried:

Results = dbx.[Items].Where([conditions]).OrderByDescending(b => Convert.ToInt32(b.length))

But this too gives an error.

Is there any way?

Solution

  • You need to use Convert.ToDouble

    dbx.[Items].Where([conditions]).OrderByDescending(b => Convert.ToDouble(b.length))

    It will generate the following SQL

    ORDER BY CONVERT(Float,[t0].[length])

    Note that SQL server type float is the same as .NET type double (not float)