I have a table with a type column.
If the bulk of the rows have values that seem numerical, i.e., each string only comprises the numbers 0 through 9.
+------+ | n | +------+ | 123 | | 234 | | BLAH | -- This row is an exception to the rule. | 456 | | 789 | +------+
This works in MySQL:
SELECT * FROM t WHERE n >= 200 AND n <= 500;
bringing back rows with where
has the values of 234 and 456 for the aforementioned data.
I'd want to be able to do the same thing with LINQ and Entity Framework.
var results = from n in context.t let n_asLong = long.Parse(n) // ...I know, see note below... where ( n != null && n_asLong >= lowVal && n_asLong <= hiVal ) select n_asLong;
NOTE: I am aware of this.
will raise an error when it encounters the incorrect number. My real example data currently only includes strings that can be legally parsed. I included a non-numeric item in the pursuit of a comprehensive, all-inclusive response.
When this code is executed, a LINQ encoding error is raised:
The method "Int64 Parse(System string) method" is not supported by LINQ to Entities and cannot be converted into a store expression.
I can see why it would want to do the conversion in SQL.
In reality, I tried an experiment with MS-SQL and had to export a special string-to-long function that was aware of incorrect data (returning 0 for non-numeric values like "BLAH") and that worked with LINQ to SQL, even though it was a much more difficult workaround than I'd prefer.
But as it happens, I'm stuck with a MySQL fix. As a result, the aforementioned vendor-specific hack is invalid.
I can't help but believe that using LINQ to Entity Frameworks, it would be possible to use a string as a value (much as native SQL does).
Any helpful suggestions from any database, C#,.NET, or LINQ experts?
If required, and if there are no other options, I'm ready to settle with the dishonorable execute-raw-SQL-directly approach.
punted in the end.
It seems that Entity Framework wants to always do strong type checking since it believes it is only dealing with objects, but it lacks a method for converting between types for the sake of comparison inside the SQL.
CONVERT( "1234", UNSIGNED )
solutions that MySQL offers cannot be encoded, much less
Eventually used raw SQL through
The provider can translate "Convert" if you utilize it.
var results = from n in context.t let n_asLong = Convert.ToInt64(n) // -- Here is the magic where ( n != null && n_asLong >= lowVal && n_asLong <= hiVal ) select n_asLong;
(At least with SQL Server Express and LINQPad, it worked for me.)