Entity Framework/Linq EXpression converting from string to int

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?

Solution 1:

As much as I hate this answer, the actual answer is you can't do it easily. It will be a real pain. I've seen lots of wrong answers and lots of answers with people saying you should just have your database fields be the correct type in the first place, which is not helpful.

Your question is similar to this question on MSDN.

There are several possibilities depending on what sort of EF you are using.

1. You are using EDMX files.

(Not code first or reverse engineered code first).

You can use something like this (from this answer):

[EdmFunction("PlusDomain", "ParseDouble")]
public static double ParseDouble(string stringvalue)
{
    // This method exists for use in LINQ queries,
    // as a stub that will be converted to a SQL CAST statement.
    return System.Double.Parse(stringvalue);
}

and map it in your EDMX like this:

<Function Name="ParseDouble" ReturnType="Edm.Double">
    <Parameter Name="stringvalue" Type="Edm.String" />
    <DefiningExpression>
        cast(stringvalue as Edm.Double)
    </DefiningExpression>
</Function>

2. If you are using code first in EF >= 4.1.

You are screwed. Microsoft didn't see fit to add any such function to SqlFunctions. The best you can hope for is to add a scalar SQL function into your database and (maybe) try to map it into your context. Microsoft didn't see any point in doing anything like this in code first. Fortunately they didn't totally block such things either. The Fluent API is powerful.

You can call the functions or stored procedures like this (reference):

var outParam = new SqlParameter("overHours", SqlDbType.Int);
outParam.Direction = ParameterDirection.Output;

Or like this (reference):

var data = context.Database.ExecuteSqlCommand("dbo.sp_getNumberJobs @overHours OUT", outParam);
int numJobs = (int)outParam.Value;

But to make them actually integrate into LINQ to Entities, you need something like CodeFirstFunctions, by using the EntityFramework.CodeFirstStoreFunctions NuGet package. It maps the SQL functions into the context, but it uses an external library only made for .NET 4.5 (see here).

Instead, you can attempt to do the same thing manually like in this question.

The quicker solution I've settled on for my needs is to just create a view with the converted types. This avoids the whole problem.

Solution 2:

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]);