如何使用linq将包含数值的varchar列排序为实体

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).

我正在使用linq lambdas用MVC中的实体框架查询MySql(注意，MySql不是Sql)。现在我有一个表产品列的其中一个表是带有数据类型“VARCHAR”的price(接受我不能将类型更改为INT，因为它可以保存“N/A”等值)。

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

我想用linq Lambdas对price列进行数值排序。我试图拉风箱。我正在使用模型值来过滤查询。

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.

LINQ to实体不承认方法的系统。字符串PadLeft(Int32, Char)方法，此方法不能转换为存储表达式。

As it cant convert to Sql statement by Entity Framwork.

因为它不能通过实体框架转换为Sql语句。

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.

但我不能这样做，因为它适用于列表，但我不能先创建列表，然后像使用linq Skip()和Take()那样做，所以首先我必须对它进行排序。

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

那么，如何对Linq lambda中的“VARCHAR”类型的价格列进行排序呢?

EDIT

编辑

In table it is :

在表中是:

59,59,400,185,34

Wnen i use OrderBy.ThenBy it gives

当我使用OrderBy。ThenBy它给

34,59,59,106,185,400

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

它看起来像是升序排序，但当我使用orderby降序排序时。ThenBy它给

106,185,400,34,59,59

So i can't use this.

所以我不能用这个。

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

注意:请在投票前给出原因，以便我可以改进我的问题……

2 个解决方案

#1

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

您可以在LINQ中对具有规范函数dbfunction的实体模拟固定的PadLeft。这样的权利

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.

我还没有对MySql提供程序进行测试，但是dbfunction中定义的规范函数应该得到任何提供程序的支持。

#2

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

它看起来像是升序排序，但当我使用orderby降序排序时。ThenBy它给

106,185,400,34,59,59

106185400年,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.

这应该比预挂数字排序要快，因为您不需要对每一行进行多次计算，而是可以根据现有数据进行排序。

#1