3

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

Mayur Patel
  • 1,741
  • 15
  • 32

2 Answers2

2

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.

Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
  • Thanks it works!!! Can u tell me what exactly DbFunctions.Right("000000000" + a.price, 10) do ? – Mayur Patel Feb 19 '16 at 13:45
  • 1
    Sure. It appends 9 zeroes at the left of the text, and then truncates the result by taking right 10 characters. So for instance "12" -> "00000000012" => "0000000012". while "1234" => "0000000001234" => "0000001234" – Ivan Stoev Feb 19 '16 at 13:58
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.

Community
  • 1
  • 1
Joachim Isaksson
  • 176,943
  • 25
  • 281
  • 294