none
How to convert a string value to an int value in Linq to Entities? RRS feed

  • Question

  • Hello,

    Linq to entities doesnt recognize the int.parse() method, or Convert.ToInt32() method

    The exception that i get is:

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

    To use the AsEnumerable() function is bad because there are a large amount of data

    Can anyone help?

    Thank you

    Tami

    Thursday, May 17, 2012 6:31 AM

All replies

  • Please show a code piece where problem.
    Thursday, May 17, 2012 8:57 AM
  • Thanks for your relpy,

    here is the code:

    from item in BillOfQuantityItem_Entities 
    let itemCode=Convert.ToInt32( item.U_Z_Subchapter_Entity.U_Z_Chapter_Entity.U_Z_Code + item.U_Z_Subchapter_Entity.U_Z_Code + item.U_Z_Code)
    where itemCode>= Convert.ToInt32("0101002") && itemCode <= Convert.ToInt32("0101006")
    select new {recordkey=item.Code,chapter=item.U_Z_Chapter_Entity.U_Z_Code,subchapter=item.U_Z_Subchapter_Entity.U_Z_Code,item=item.U_Z_Code , code=itemCode}

    Thursday, May 17, 2012 9:31 AM
  • i will give a simple code piece:

    from item in BillOfQuantityItem_Entities
    where Convert.ToInt32(item.U_Z_Code)>1
    select item.U_Z_Code

    please, i need help

    thank you,

    tami

    Thursday, May 17, 2012 7:35 PM
  • Hi, Linq2Entities is not able to convert all C# syntax.

    There is a couple of way to solve that problem

    1) In the query filter on the right type

    2) Create a Store Procedure for that pourpose

    3) Wrap IQueryable to extend it to provide support for Convert operator see this

    Hope it help.

    Max


    Thursday, May 17, 2012 7:56 PM
  • On 5/17/2012 3:35 PM, tami_b wrote:
    > i will give a simple code piece:
    >
    > from item in BillOfQuantityItem_Entities
    > where Convert.ToInt32(item.U_Z_Code)>1
    > select item.U_Z_Code
    >
    > please, i need help
    >
     
    Because you have the Convert in the query, Linq will not be able to
    generate T-SQL statements for the query, and it most likely will blow-up.
     
    Thursday, May 17, 2012 10:09 PM
  • Hi Max,

    Is this official Microsoft response?

    It seems rather trivial, especially now that you have SqlFunctions. Do you have plans providing this?

    Best,

    Asher

    Sunday, May 20, 2012 12:31 PM
  • Hi Asher_barak,

    Welcome to MSDN Forum.

    Every Linq to Entities query statement will be translated to T-SQL at the end, but the Convert.ToInt32() method couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. So, based on this issue, you can modify the code as below.

    int code = Convert.ToInt32(item.U_Z_Code);
    
    var query = from item in BillOfQuantityItem_Entities
     where code>1
     select item.U_Z_Code;

    Best Regards

    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 21, 2012 7:32 AM
    Moderator
  • Hi Allen,

    Thanks for you feedback.

    The proposed solution is valid for very limited scenarios. I have a table where I have an nvarchar field I know to hold only values convertible to int. I need to order the table based on the converted numeric values of that field.

    (This is my scenario. I am sure there are a million others - converting a string to an int is hardly a bizarre requirement)

    It would seem parsing Covert.ToInt32 or Int32.Parse or whatever framework conversion function in the query expression tree would be simple enough (it has been implemented by other ORM solutions). If not, it could be included in the SqlFunctions class.

    What is the formal Microsoft stand on this?

    Asher

    Tuesday, May 22, 2012 10:37 AM
  • Hi Asher_barak,

    I'm afraind Linq to Entities couldn't support this method. But there's a workaround, the class which EF generated is a partial class, so you can write another partial class and add your own business logic in it, such as type conversion.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 23, 2012 6:46 AM
    Moderator
  • Hi Allen,

    Thanks again for replying. 

    The proposed workaround will not e useful since my aim is to move the work off to the SQL server in one query. Please consider this example by Tami:

    > from item in BillOfQuantityItem_Entities
    > where Convert.ToInt32(item.U_Z_Code)>1

    > select item.U_Z_Code

    providing my own conversion on the object would not result in the query I need:

    SELECT U_Z_Code FROM BillOfQuantityItem WHERE CAST(U_Z_Code AS int)>1

    The user may have tens of thousands of records in the table, getting them all to the client just in order to filter (on the AsEnumrable) is simply not acceptable.

    It seems from your post that this situation is not going to be resolved in the future too. As mentioned, I do not see the technical problem in providing a method for this conversion that the EF parser would be able to parse from the LINQ tree to SQL.

    One way or another, it this  the formal MS EF Team (We do not have it and do not intend to have it)?

    Best,

    Asher


    Monday, May 28, 2012 9:01 AM
  • Hi Asher_barak,

    EF doesn't suport it now, but it may be supported in the future. Entity Framework is a relative new ORM, so it may has some shortage now, please be patient with it, I believe it will become better and better. I will unmark the answers, sorry for little help. Based on this case, I suggest you to create a stored procedure to complete the conversion task. If you need further help, please feel free to let me know, I will be more than happy to be of assistance. :)

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 28, 2012 9:13 AM
    Moderator
  • Hi Asher_barak,

    Any update about this question? If you need further help, please feel free to let me know, I will be more than happy to be of assistance. :)

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, May 30, 2012 2:55 AM
    Moderator
  • Hi Allen,

    Thanks for replying.

    Due to some other restrictions, our system cannot use SP's.

    I am afraid we are going to have a segment of string SQL in our code after putting thousands of men hours into incorporating Microsoft's best and brightest ORM into it.

    That's the way life is, you also need some luck...

    Is there a way to know where this requirement is in the dev. team's backlog?

    Asher

    Thursday, May 31, 2012 1:18 PM
  • Hi Asher_barak,

    I'm not sure about the backlog, it's really a necessary requirement, I think product team is working hard on it and it will be supported in the future. : )

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 1, 2012 3:43 AM
    Moderator
  • Hi,

    any progress with this issue?

    regards,

    M.

    Thursday, October 16, 2014 9:35 PM
  • It has been almost 4 years and this problem has not been even addressed nor fixed on EF7
    Tuesday, June 30, 2015 4:43 PM