none
Convert String to Int in Where/OrderBy - Is it possible? RRS feed

  • Question

  • Hello,

    What I essentially need to do in LINQ to Entities is this (I know this won't work, but I'm doing it this way to illustrate):

    from c in context.Table
    where key
    == int.Parse(c.KeyAsString)
    order
    by int.Parse(c.KeyAsString)
    select c

    I wasn't sure if this was possible... anybody know of a way?

    Thanks.


    http://linkedin.com/in/brianmains
    Monday, August 15, 2011 4:29 PM

Answers

All replies

  • Hi bmains;

    If you are using Entity Framework 4.0 or better you can do it as follows. You will need to cast the int to a double and finally use the String Trim on it.

    var query = from c in Table
          where Key == SqlFunctions.StringConvert((double)c.Key).Trim()
          orderby SqlFunctions.StringConvert((double)c.Key).Trim()
          select c;  
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Proposed as answer by Cristi Lupascu Saturday, December 8, 2012 8:05 AM
    Monday, August 15, 2011 6:42 PM
  • Hi bmains,

    Welcome! 

    The EF provider couldn't recognize the Parse method as it is IQueryable Type, it is hard to translate the method to T-sql. If we convert the IQueryable to IEnumerable, it works as a delegate. 

    from c in context.Table.ToList()
    where key == int.Parse(c.KeyAsString)
    order by int.Parse(c.KeyAsString)
    select c
    
    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 16, 2011 8:17 AM
    Moderator
  • I had the same need and found this link very helpful:

    http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-meth

    Essentially you have to create an EDM Function by manually editing the EDMX file (in the CSDL; I put them just ahead of the <EntityContainer> tag) and then create a public static method that Linq can use to invoke them.  It works really well and thankfully, EF doesn't wipe out these added functions if you update the model from a database.

    Tuesday, August 16, 2011 8:01 PM
  • I think it'd be simpler to just add a local integer variable and parse it there before the query.

    However one thing in the example query bugs me. What is the use of "Order by" statement with an integer (let's pretend the function did run without error)? Is column name that IS number even allowed?

    Wednesday, August 17, 2011 7:17 AM
  • The idea behind this is to allow the query to be composed on the SQL server to reduce the data being returned to the client process.  Using a local variable means you have an unfiltered query which can potentially bring more data back than you need.  Same thing if you convert the query to use AsEnumerable; the SQL server won't see the where clause; it will be executed on the client process after all the data is returned.

    As far as ordering, if you have a string column that actually holds numeric data, you'd want to order them based on their numeric value; not their character value (you want for example 1,5,9,10 not 1,10,5,9) so unless you want to pad the string data with zeros on the left (not a good idea), you want them converted to their numeric type. 

    Wednesday, August 17, 2011 2:46 PM
  • Oh I see. Thank you for your explanation.
    Wednesday, August 17, 2011 3:24 PM
  • Hi,

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions?

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

    Have a nice day. 


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, August 26, 2011 3:13 AM
    Moderator