Converting String to Long ...in LINQ to Entity Framework using a MySQL adaptor.

Question

I have a table with a column of type varchar where the majority of rows have numerical looking values, that is, each string contains nothing but the digits 0 through 9.

+------+
|  n   |
+------+
| 123  |
| 234  |
| BLAH |  -- This row is an exception to the rule.
| 456  |
| 789  |
+------+

In MySQL, this works: SELECT * FROM t WHERE n >= 200 AND n <= 500;
returning rows with where n has the value of 234 and also 456 for the data above.

I'd like to be able to use LINQ with Entity Framework do the same thing.

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: Yes, I'm aware that long.Parse() will throw an exception when it gets to the invalid numeric. At the moment my actual sample data contains only legally parsable strings. I included a non-numeric item in the hopes of a complete, all-encompassing answer.

Running this code results in an exception pertaining to LINQ encoding:
LINQ to Entities does not recognize the method 'Int64 Parse(System string) method, and this method cannot be translated into a store expression.

I understand why, that it wants to do the conversion in SQL.

In fact, I conducted an experiment with MS-SQL and had to export a custom string-to-long function that was aware of bad data (returning 0 for non-numeric values like 'BLAH'), and that worked with LINQ to SQL, though it was a far more complicated workaround than I'd like.

As it turns out though, I'm locked into a MySQL solution. So the above vendor-specific hack won't work.

I can't help but thinking there has to be a way to treat a string as a value (as even the native SQL allows) and do so with LINQ to Entity Frameworks.

Any good approaches from some database / C# / .NET / LINQ experts?

If necessary, I'm willing to punt for a shameful execute-raw-SQL-directly solution if none other exists.

Answer 1

Ended up punting.

Seems that Entity Framework, thinking it's working with objects only, wants to always do strong type checking, while at the same time has no mechanism for converting between types for the purposes of comparison within the SQL.

Shockingly, even CONVERT( "1234", UNSIGNED ), which MySQL provides, can't be encoded, much less solutions with Int64.Parse().

Ended up accessing raw SQL via MySqlConnection / MySqlCommand / MySqlDataReader.

Answer 2

You can use "Convert", the provider can translate that.

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 it worked for me using SQL Server express and LINQPad)