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.

Answer 1

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();

Answer 2

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.

Answer 3

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

Answer 4

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();

Answer 5

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.

Answer 6

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!