How to OrderBy an integer in a string field in a Linq query

Question

I have some data coming out of an DB that I can't readily change the schema of. I want to sort it and bind it to a control based on a numerical ID. The problem is that the API stores the number in a string field instead of as an int and Linq barfs on the conversion attempt.

myControl.DataSource = dataFromDB.OrderBy(o => int.Parse(o.StringHoldingAnInt));

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

Convert.ToInt32 doesn't work either.

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

Sorting as a string isn't suitable because the values aren't all the same length and it would order them like this: 1, 10, 11, 2, 3..

How much data is there? Would it be feasible to sort on the client side? — dlev, Nov 10 '11 at 20:36
You could try to pad the string with '0' so that they all have the same length... but I'm not sure String.PadLeft is supported by Linq. — Thomas Levesque, Nov 10 '11 at 20:36
@dlev several hundred rows in the DB, only a few dozen rows after the Where() query I omitted from my question. — Dan Is Fiddling By Firelight, Nov 10 '11 at 20:43
@ThomasLevesque Linq-to-Entities doesn't like String.PadLeft either. — Dan Is Fiddling By Firelight, Nov 10 '11 at 21:30

score 19 · Accepted Answer · answered Nov 10 '11 at 20:36

19

This won't be nearly as efficient because you're not harnessing the database query to filter your results, but this would basically query for all data, then filter on the client.

myControl.DataSource = dataFromDB.ToList().OrderBy(o => int.Parse(o.StringHoldingAnInt));

answered Nov 10 '11 at 20:36

Steve Danner

21,818
7
41
51

My unabridged query does a Where() prior to the order by command to cut the amount of data down to a reasonable size, so the client side processing burden shouldn't be excessive. – Dan Is Fiddling By Firelight Nov 10 '11 at 20:48
3

For those reading this: This will load the entire database table in memory. – Laoujin Oct 26 '18 at 11:09

score 16 · Answer 2 · answered Jul 08 '20 at 13:57

16

Came up with a simple trick to fix this: First order by length and then normaly.

dataFromDB.OrderBy(o => o.StringHoldingAnInt.Length).ThenBy(o => o.StringHoldingAnInt)

This is all done in DB and doesn't load into memory.

answered Jul 08 '20 at 13:57

Pops

161
1
2

you'd also need to add additional logic to handle negative numbers. – Dan Is Fiddling By Firelight Jul 08 '20 at 16:52
1

@LukeTBrooks I don't believe the docs you've linked back that up. `OrderBy` is not a conversion operator, and will not cause immediate execution. In fact, the `OrderBy`/`ThenBy` combo is specifically designed to take advantage of deferred execution. – bug Aug 26 '20 at 19:21
1

I actually prefer this solution because it is not dependent on any magic constants and will work for string-encoded whole numbers of any size (assuming no leading zeroes) – bug Aug 26 '20 at 19:24
1

This seems like it should be the go-to answer in general for this problem – Andrew Apr 23 '21 at 23:02

score 9 · Answer 3 · answered Dec 08 '17 at 12:33

9

It's possible to do it on the DB side. The idea is taken from here. So it does sorting by properly formatted string(zero chars are added to the left and then needed amount of chars is taken from the right)

myControl.DataSource = dataFromDB
    .OrderBy(o =>  DbFunctions.Right("00000" + o.StringHoldingAnInt, 7));

answered Dec 08 '17 at 12:33

Roman Koliada

4,286
2
30
59

+1 for a solution using DBFunctions instead of provider specific SqlFunctions. This is a much simpler solution than replicating a variable number of zeros based on the length of the original value. – Chris Schaller Jun 18 '18 at 01:49
Hmmm, so what if you added a convert db func, so you could order by the result.... – Jeremy Lakeman Jul 08 '20 at 14:02

score 1 · Answer 4 · answered Nov 10 '11 at 20:36

1

You could convert the query to a list:

var query = ...;
var list = query.ToList();

Now you can order by the values by converting it:

list.OrderBy(o => int.Parse(o.StringHoldingAnInt));

The problem with this is that you are retrieving a lot of values they all come from the database and then you order in memory.

answered Nov 10 '11 at 20:36

Dismissile

32,564
38
174
263

4

Just so no one is confused, `.OrderBy()` returns an `IEnumerable`. The `list.OrderBy()` line above will currently do nothing, since you aren't assigning the return value from `.OrderBy()` to a variable. – John Washam Feb 01 '16 at 23:20

How to OrderBy an integer in a string field in a Linq query

4 Answers4