none
Convert String column to Int (ObjectQuery)

    Question

  • Hello there fellows!

    A few moments ago, i needed to get the top 10 "Persons" from a Person table, and i did:

    ObjectQuery x = new ObjectQuery<object>("Person", myObjectContext).Where("it.PERSONID< 1000").OrderBy("it.PERSONID").Top("10");

    and then i noticed... the PERSONID field is varchar (string) but only has numbers so, the order by returned persons like

    1

    10

    11

    2

    22.....

    in sql language  its only needed to:

    select * ,
    CASE WHEN ISNUMERIC(PERSONID) = 1 THEN CAST(PERSONID AS INT) ELSE 99 END as COLUMNINT
    from PERSON order by COLUMNINT ASC

    My question is how to do something like this in the ObjectQuery .


    -- Jorge_M_P

    Thursday, July 03, 2014 2:18 PM

Answers

  • >>My question is how to do something like this in the ObjectQuery .

    You can't.

    But you could sort the the resultset from the query in memory after the query has been executed:

    ObjectQuery<Person> x = new ObjectQuery<Person>("Person", myObjectContext).Where("it.PERSONID < 1000").Top("10");
    var ordered = x.OrderBy(p => Convert.ToInt32(p.PERSONID));

    You could also use LINQ provided that the context contains an ObjectSet of Person entites. Something like:

       var persons = (from p in context.Persons
                  let id = Convert.ToInt32(p.PERSONID)
                  orderby id
                  select p);

    The third, and possibly the best, option is of course to change the datatype of the column in the database.


    • Marked as answer by Jorge_M_P Friday, July 04, 2014 8:06 AM
    Thursday, July 03, 2014 3:18 PM