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?

Isn't that a red flag that your Value should be an int in your database? — Mathew Thompson
– Mathew Thompson, Commented May 22, 2013 at 14:40
ah, gotta love the "inner-platform effect"... building a database table (columns/rows) using a database table... — Marc Gravell
– Marc Gravell, Commented May 22, 2013 at 14:41

CarenRose · Accepted Answer · 2019-11-18 20:08:35Z

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.

I know this is an old answer, but it was really helpful in opening my eyes on a recent issue. The answer is good, but there is also a way to use model-defined functions with code first! It is a bit more work, though. See stackoverflow.com/questions/29503962/… Also, the MS code seems to throw NotImplementedException("not for direct calls") in the actual C# code, and I think that is wise. That call is meant to be translated from an expression tree into actual SQL, not called directly!

Justin Niessner · Accepted Answer · 2013-05-22 15:03:21Z

9

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

edited May 22, 2013 at 15:03

answered May 22, 2013 at 14:41

Justin Niessner

246k40 gold badges416 silver badges546 bronze badges

9 Comments

Jon Over a year ago

I'm trying not to do that as this is part of a filtering method that is supposed to hit the db and return the filtered data that way

Justin Niessner Over a year ago

@Jon - Since the data doesn't necessarily have to be a number, there's no good way to handle the conversion in the database. Your options are to use code similar to that above or to use strings for comparison.

Jon Over a year ago

how can i use strings for comparison using the greater/less than operators though

Jon Over a year ago

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

Karel Křesťan Over a year ago

Your second edit saved my life 7 years after. Thanks!

|

Adi · Accepted Answer · 2017-07-22 04:14:13Z

2

I recently had to convert a string (numeric representation) to an enumeration value in a LINQ query, without materializing the query. My enumeration used int values between 0 and 9, so I ended up doing something like this:

    .Select(str => (MyEnum?)(SqlFunctions.Unicode(str) - 48))

I know this is not providing a full solution for the problem, but it definitely works in a situation like mine. Maybe some will find it useful.

answered Jul 22, 2017 at 4:14

Adi

1161 silver badge6 bronze badges

Comments

Jaume · Accepted Answer · 2021-02-03 14:13:50Z

0

You can use the SqlFunctions.IsNumeric() method and, for example, store it into a list.

var resultsArray = context.TableData_DB.Where(x => SqlFunctions.IsNumeric(x.stringField) >= -1).ToList();

answered Feb 3, 2021 at 14:13

Jaume

3,83020 gold badges65 silver badges125 bronze badges

1 Comment

Gert Arnold Over a year ago

Already answered here. And, besides, >= -1 isn't correct.

colinD · Accepted Answer · 2025-10-13 14:47:01Z

0

In EF Core 8 (not tested in other versions, and only with the SQL provider), you can cast the string to object then to int.

This will generate a SQL Server statement CAST x AS INT.

return message => message.Properties.Any((int)(object)p.Value >= values[0]);

answered Oct 13 at 14:47

colinD

2,1011 gold badge24 silver badges29 bronze badges

Comments

user2399760 · Accepted Answer · 2013-05-22 14:49:53Z

-5

You can try Convert.ToInt32(input); If you are not sure, you can TryParse. It will not throw exception but false is cannot Parse. But Convert.ToInt32(input); should work. You can read about it on http://msdn.microsoft.com/en-us/library/vstudio/bb397679.aspx.

answered May 22, 2013 at 14:49

user2399760

251 silver badge6 bronze badges

2 Comments

Eren Ersönmez Over a year ago

you cannot use TryParse in an query EF expression.

user2399760 Over a year ago

Didn't had chance to check it right away, but thank you for that! Thought it could worked.

Collectives™ on Stack Overflow

Entity Framework/Linq EXpression converting from string to int

6 Answers 6

1. You are using EDMX files.

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

1 Comment

9 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1. You are using EDMX files.

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

1 Comment

9 Comments

Comments

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related