how to sort varchar column containing numeric valu

问题:

I am using linq lambdas to query the MySql (Note MySql not Sql) with Entity Framwork in MVC. Now i have one table product one of column this table is price with datatype "VARCHAR" (Accept i can't change type to INT as it can hold values like "N/A",etc).

I want to sort price column numerically with linq Lambdas.I have tried bellow.I am using Model values to filter query.

var query = ent.Product.Where(b => b.cp == Model.CodePostal);

if (Model.order_by_flg == 2)
{
    query = query.OrderByDescending(a => a.price.PadLeft(10, '0'));
}

But it will not work and give me bellow error.

LINQ to Entities does not recognize the method 'System.String PadLeft(Int32, Char)' method, and this method cannot be translated into a store expression.

As it cant convert to Sql statement by Entity Framwork.

I also tried bellow.

var query = ent.Product.Where(b => b.cp == Model.CodePostal);

if (Model.order_by_flg == 2)
{
    query = query.OrderByDescending(a => a.price.Length).ThenBy(a => a.price);
}

But i can't do this because it works for List but i cant first make list and then do this as i am using linq Skip() and Take() so first i have to sort it.

So how can i sort price column of type "VARCHAR" in Linq lambda?

EDIT

In table it is :

59,59,400,185,34

Wnen i use OrderBy.ThenBy it gives

34,59,59,106,185,400

It looks right as sorting ascending But when i use OrderByDescending.ThenBy it gives

106,185,400,34,59,59

So i can't use this.

NOTE: Please give reasons before Downvote so i can improve my question...

回答1:

You can simulate fixed PadLeft in LINQ to Entities with the canonical function DbFunctions.Right like this

instead of this

a.price.PadLeft(10, '0')

use this

DbFunctions.Right("000000000" + a.price, 10)

I haven't tested it with MySql provider, but canonical functions defined in the DbFunctions are supposed to be supported by any provider.

回答2:

It looks right as sorting ascending But when i use OrderByDescending.ThenBy it gives

106,185,400,34,59,59

That's because you're ordering by length descending, then value ascending.
What you need is simply to sort both by descending;

query = query.OrderByDescending(a => a.price.Length)
              .ThenByDescending(a => a.price);

This should be faster than prepending numbers to sort, since you don't need to do multiple calculations per row but can instead sort by existing data.