Casting string to int?

Question

Hi,

I am having a problem writing a LINQ for entities query that must compare two values that are stored in the database as text.

Problem is that I cannot seem to convert these two values to int (like i would in t-sql)?

					from grp in Result.DefaultIfEmpty()
					where Int32.Parse(asr.LowerBound) <= Int32.Parse(grp.NaturalCode)
					&& Int32.Parse(asr.UpperBound) >= Int32.Parse(grp.NaturalCode)

I have tried a couple of variants but always receive an error message similar to this one:

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

---

I.W Coetzer

Answer 1

Hi I.W Coetzer,

Unfortunately there's no way to convert from a string to an int directly within a LINQ to Entities query. You can either pull the necessary data from the database and then parse/process it in memory or you can use Entity SQL, where you can make use of the CAST expression: http://msdn.microsoft.com/en-us/library/bb399172.aspx

Regards,

Tyler

Answer 2

Hi Tyler

Thank you for the reply.

However I stubbornly do not agree, since I want there to be some way in which one can do this.It is a fairly common expression when writing std. t-sql - hard to believe the designers of LINQ did not introduce something like this as well.

Maybe in the next iteration of LINQ for Entities will we see support for something like this? I hope...

---

I.W Coetzer

Answer 3

There is one way which you can work around the limitation, this is by implementing your own custom method on your ObjectContext with its translation to T-SQL. Check out this link for an example of implementing a "ParseDouble" method: http://stackoverflow.com/questions/5971521/linq-to-entities-does-not-recognize-the-method-double-parsesystem-string-met

Regards,

Tyler

Answer 4

Managed to get it working, had to however define a new function in the .edmx file + create a function stub for this to work:

1. In the .EDMX file

        <Function Name="ConvertToInt32" ReturnType="Edm.Int32">
          <Parameter Name="v" Type="Edm.String" />
          <DefiningExpression>
            CAST(v AS Edm.Int32)
          </DefiningExpression>
        </Function>
      </Schema>
    </edmx:ConceptualModels>

2. In my model's code file: In my case "Model" is the name of the .edmx file as well...

            [EdmFunction("Model", "ConvertToInt32")]
            public static int ConvertToInt32(string v)
            {
                throw new NotSupportedException("Direct calls are not supported.");
            }

Ensure that I have these two using statements for the function attribute to work properly:

using System.Data.Metadata.Edm;
using System.Data.Objects.DataClasses;

---

I.W Coetzer

Answer 5

Hi I.W Coetzer,

Welcome to MSDN Forum.

Entity Framework is still on the way and I believe it will become better and better. The feature you said is really convenient for developing in the daily work. Could you please submit the feature suggestion here? This is, so Microsoft will take more attention on this feature and may support it in the next version. If the feature is added in the next version, obviously you do an outstanding contribution to the Entity Framework's development, it sounds greate.

Best Regards

---

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