Entity Framework/Linq EXpression converting from string to int

Question

I have an Expression like so:

var values = Enumerable.Range(1,2);

return message => message.Properties.Any(
    p => p.Key == name 
    && int.Parse(p.Value) >= values[0] 
    && int.Parse(p.Value) <= values[1]);

This compiles fine but when it hits the database it throws the exception 'LINQ to Entities does not recognize the method 'Int32 Parse(System.String)' method, and this method cannot be translated into a store expression '

If I don't do the parse and have values be a string[] I can't then use the >= and <= operators on strings.

p.Value is a string which holds various values but in this case it is int

Is there a way I can query the database to do this sort of between statement?

Answer 1

As pointed out by others in the comments, the fact that you're having to parse this value should be a red flag that you should be using a different data type in your database.

Fortunately, there is a workaround by forcing the query to be executed by LINQ to Objects rather than LINQ to Entities. Unfortunately, it means potentially reading a large amount of data into memory

EDIT

Based on your other comments, the value in the Value column ins't guaranteed to be a number. Therefore, you'll have to try converting the value to a number and then handling things based on the failure/success of that conversion:

return message
       .Properties
       .AsEnumerable()
       .Any(p => 
            {
                var val = 0;
                if(int.TryParse(p.Value, out val))
                {
                    return p.Key == name &&
                           val >= values[0] &&
                           val <= values[1])
                }
                else
                {
                    return false;
                }
           );

EDIT 2

You might actually be able to get away with this in the database. I'm not sure if this will work or not for you but give it a shot:

return message.Properties
              .Where(p => p.Key == name && SqlFunctions.IsNumeric(p.Value) > 0)
              .Any(p => Convert.ToInt32(p.Value) >= values[0] &&
                        Convert.ToInt32(p.Value) <= values[1]);