LINQ to Entity Framework using a MySQL adapter to convert String to Long

entity-framework linq linq-to-entities mysql type-conversion

Question

I have a table with a type column.varchar 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 wheren 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.long.Parse() 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.

1
1
2/25/2010 2:34:00 PM

Accepted Answer

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.

surprisingly evenCONVERT( "1234", UNSIGNED ) solutions that MySQL offers cannot be encoded, much lessInt64.Parse() .

Eventually used raw SQL throughMySqlConnection / MySqlCommand / MySqlDataReader .

1
3/1/2010 6:18:00 PM

Popular Answer

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.)



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow