Convert string to int for ordering using LINQ

Question

I'd like to order my list by a string converted into an int:

var orderedListOfRfidTags = uow.RfidTags.OrderBy(t => Convert.ToInt32(t.Number)).ToList();

but get: The method 'ToInt32' is not supported.

Marianomaribel

Do you mean LINQ to SQL or EF? – Hampton

Am using Lightspeed as my ORM.. but am guessing this may be agnostic to all linq providers? – Marianomaribel

no, it does depend on the provider. Some providers might support that method. – Ornis

One option would be to try using int.Parse() instead, but it's likely that won't work either. – Ornis

Answer 1

I am one of the developers of LightSpeed.

The LINQ provider in LightSpeed 3.11 RTM doesn't support Convert.ToInt32. However we have now added support via a nightly release which is available for download now.

If you don't want to use the nightly release, you can achieve the result you want by dropping down to the Query Objects API and invoking the SQL CAST function directly. This will look something like:

Query query = new Query
{
  Order = Order.By(Entity.Attribute("Number")
                         .Function("CAST", new LiteralExpression("INTEGER") { EmitInline = true }))
};

uow.Find<RfidTag>(query);

The reason for the rather verbose LiteralExpression for the cast type is that by default LightSpeed sends values to the database through parameters (to avoid SQL injection attacks). But for the CAST function the SQL engine needs to see CAST(Number, INTEGER) rather than CAST(Number, @p0) where p0 has the value "INTEGER". So you have to use an EmitInline expression, which bypasses parameterisation, rather than the more natural string literal.

Once again, though, the nightly release does support Convert.ToInt32 in LINQ so you only need to drop down to this level if you want to avoid taking a nightly build.

Engracia

Answer 2

What about:

var orderedListOfRfidTags = uow.RfidTags.OrderBy(t => t.Number).ToList();

remove any CLR method so ORM can transform it to a known SQL query

EDIT: I just read want to convert it first so:

var orderedListOfRfidTags = uow.RfidTags.ToList().OrderBy(t => Convert.ToInt32(t.Number));

either to get all from DB then order it on the client (linq to object) as I mentioned before or find a method on your ORM to cast to int the order it. Before you order Select a new list with a Number converted then order by it.

Edit2:

What about the direct cast is it working with this ORM?

var orderedListOfRfidTags = uow.RfidTags.OrderBy(t => (int)t.Number).ToList();

Planimeter

Thanks @PrOfess0rX the problem is that the t.Number is acutally stored in the db as a string. In this case I know the t.Numbers being returned are actually all numbers. If I use the above query the ordered list comes back as ordered as a string ie 1, 11, 111 etc.. – Marianomaribel

Answer 3

Try to use int.Parse instead of Convert. It's likely that Lightspeed supports one without supporting the other.

var orderedListOfRfidTags = uow.RfidTags
    .OrderBy(t => int.Parse(t.Number))
    .ToList();

Raff

Answer 4

var orderedListOfRfidTags = (uow.RfidTags.ToList()).OrderBy(t => int.Parse(t.Number));

Assuasive

Answer 5

I'm not sure what kind of type "RfidTags" is, nor am I familiar with the Lightspeed ORM, but I know that when I have had similar troubles with Linq to Sql telling me that a particular method I'm trying to invoke in a Where or OrderBy clause is not supported, then I just change things around so that I'm dealing with plain old Linq instead.

For example, could you try this?

var listOfRfidTags = uow.RfidTags.ToList();
var orderedListOfRfidTags = listOfRfidTags.OrderBy(t => Convert.ToInt32(t.Number));

(yes it is possible to combine this into one line, but shown here on two lines for clarity.)

Good luck!

Schliemann

This will fetch the whole list of rfidTags then order by on client side. It may be Ok if the list is small. But if the op wanted to order by then fetch the first 10, because the list is huge, this will basically do a SELECT * FROM RFIDTAGS... and give a big performance issue. – Circumscissile

Answer 6

So, here's my solution to this problem:

var query = (from q in query select q).ToList().Where(x => Convert.ToInt32(x.col_string) > 0);

I first casted the IQueryable to a list, and then converted the column of data type string to int32 for use in mathematical operations.

I hope this helps.

Probity

That is a terrible approach: it brings unfiltered data from the backend and filters it at the caller. – Photomural

If the column needs to be converted to a string in order to be applied to a filter on a result set, this is the only way I found in LINQ to perform the operation. One could imagine a situation where you pull your results: var x = _db.Where(x => x.filt_col == somevalue).ToList() but are left with data that needs t be broken down and filtered only after it has been pulled from the back end and converted: var a = x.Where(x => Convert.ToInt32(x.col_string) > 0 && Convert.ToInt32(x.col_string) < 100)), var b = x.Where(x => Convert.ToInt32(x.col_string) > 100 && Convert.ToInt32(x.col_string) < 200)) – Probity

Answer 7

Solution 1: ToList (Large table will have proformace issue)

_db.users.orderby(u=>u.strBarCode)  << Error
_db.users.ToList().orderby(u=>u.strBarcode) << OK

Solution 2: Length()

The problem is ["99","100","98", "234"].order() => ["100","234","98","99"], if the string is same length, order string as number works.

If you need get max value:

1. Find max length, in our example is 3
2. ["99","100","98", "234"].Where(o=>o.length==3).OrderByDescending().First()

If you need get top(n):

1. Get list of length
2. For each length, get a order list.
3. Append to return value until you reach (n).

Reincarnate