Convert String to Int in LINQ to Entities

Question

I am trying to duplicate the following SQL statement as a LINQ to Entities query (where "PRODUCTS" is the table mapped to the entity) ... NOTE IQueryable ... most of what I have seen posted as solutions convert either the search parameters, or the dump the results into an IEnumerable and then proceed to convert from there. I am dealing with 100's of millions of records and cannot afford to load 200 million records into memory, only to have to filter through them again. I would like, if possible to do this in a single query to the databse.

select *
from PRODUCTS
where
    MODEL_CODE = '65' and
    CAST(SERIAL_NUMBER as int) > 927000 and
    CAST(SERIAL_NUMBER as int) < 928000

I have tried the following ...

int startSN, endSN;
startSN = 9500
endSN = 9500

if (!int.TryParse(startSerialNumber, out startSN))
    throw new InvalidCastException("The start serial number was not a valid value");

if (!int.TryParse(endSerialNumber, out endSN))
    throw new InvalidCastException("The end serial number was not a valid value");

    IQueryable<PRODUCT> resultList = base.Context.PRODUCTS.Where(b => 
        (Convert.ToInt32(b.SERIAL_NUMBER) > startSN) &&
        (Convert.ToInt32(b.SERIAL_NUMBER) < endSN)).AsQueryable();

I have tried a couple of other version of things similiar to this with no luck. I have looked at the following posts also with no luck.

Convert string to int in an Entity Framework linq query and handling the parsing exception - the solution converts query to a list before converting the entity properties.

Convert string to Int in LINQ to Entities ? - This problem was just with converting the parameters which can be easily done outside the LINQ to Entities statement. I am already doing this for the parameters.

LINQ to Entities StringConvert(double)' cannot be translated to convert int to string - This problem is actually the reverse of mine, trying to convert an int to a string. 1) SqlFunctions does not provide a function for converting TO an int. 2) Ultimately the solution is to, again convert to an IEnumerable before converting/casting the values.

Anybody got any other ideas? I am little stumped on this one!

Thank you, G

That's a really nasty limitation of LTE. Of course, you can't convert to `IEnumerable` and filter in memory with 100 million records. If you don't use code-first, but an EDMX based approach model defined functions are probably the best solution (http://stackoverflow.com/questions/5754218/convert-string-to-int-in-ef-4-0). Or use `base.Context.PRODUCTS.SqlQuery(string sql, params object[] parameters)` and pass in your SQL statement above. — Slauma, Mar 12 '13 at 23:47
You could try using a view to do the conversion (just select all rows and convert to int those columns). EF doesn't like views much, but something this simple might work for you. — Erik Funkenbusch, Mar 13 '13 at 03:03
@Slauma sorry it took me o long to back on this. The suggestion to use the "SqlQuery()" method worked! While it opens up some new considerations such as the need to populate Navigation properties of the resulting entity set it gets me past this step. I am not sure if there is another way to do this but this is the most promising so far! Anyway I would like to mark this as the answer but since it's only a comment I cannot. If you post it as an "answer" I will be happy to mark it as such. Otherwise I will "answer my own question" (with proper accreditation of course). Thanks again! — Gary O. Stenstrom, Mar 20 '13 at 22:04
@GaryO.Stenstrom: I have written the comment as an answer. I also don't know any other way than the mentioned two options. — Slauma, Mar 20 '13 at 22:36

score 0 · Accepted Answer · edited May 23 '17 at 11:58

If you don't use code-first, but an EDMX based approach model defined functions are probably the best solution: Convert String to Int in EF 4.0

Alternatively you can use...

base.Context.PRODUCTS.SqlQuery(string sql, params object[] parameters)

...and then pass in the raw SQL statement from your question.

DbSet<T>.SqlQuery(...) returns a DbSqlQuery<T> as result. It is important to keep in mind that this type does not implement IQueryable<T>, but only IEnumerable<T>. Its signature is:

public class DbSqlQuery<TEntity> : IEnumerable<TEntity>, IEnumerable, IListSource
    where TEntity : class

So you can extend this result with further LINQ methods, but it is only LINQ to Objects that will be executed in memory with the returned result set from the SQL query. You can not extend it with LINQ to Entities that would be executed in the database. Hence, adding .Where filters to DbSqlQuery<T> does not have any influence on the database query and the set of data that is loaded from the DB into memory.

That's actually not surprising as it would mean otherwise that a partial expression tree (from a Where method) had to be translated into SQL and then merged into a hand-written SQL statement so that a correct new composed SQL statement results and could be sent to the database. Sounds like a pretty hard task to me.

Convert String to Int in LINQ to Entities

1 Answers1