entity framework - Convert String to Int in LINQ to Entities -

i trying duplicate following sql statement linq entities query (where "products" table mapped entity) ... note iqueryable ... of have seen posted solutions convert either search parameters, or dump results ienumerable , proceed convert there. dealing 100's of millions of records , cannot afford load 200 million records memory, have filter through them again. like, if possible in single query databse.

select * products     model_code = '65' ,     cast(serial_number int) > 927000 ,     cast(serial_number int) < 928000 

i have tried following ...

int startsn, endsn; startsn = 9500 endsn = 9500  if (!int.tryparse(startserialnumber, out startsn))     throw new invalidcastexception("the start serial number not valid value");  if (!int.tryparse(endserialnumber, out endsn))     throw new invalidcastexception("the end serial number not 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 couple of other version of things similiar no luck. have looked @ following posts no luck.

convert string int in entity framework linq query , handling parsing exception - solution converts query list before converting entity properties.

convert string int in linq entities ? - problem converting parameters can done outside linq entities statement. doing parameters.

linq entities stringconvert(double)' cannot translated convert int string - problem reverse of mine, trying convert int string. 1) sqlfunctions not provide function converting int. 2) solution to, again convert ienumerable before converting/casting values.

anybody got other ideas? little stumped on one!

thank you, g

if don't use code-first, edmx based approach model defined functions best solution: convert string int in ef 4.0

alternatively can use...

base.context.products.sqlquery(string sql, params object[] parameters) 

...and pass in raw sql statement question.

dbset<t>.sqlquery(...) returns dbsqlquery<t> result. important keep in mind type not implement iqueryable<t>, ienumerable<t>. signature is:

public class dbsqlquery<tentity> : ienumerable<tentity>, ienumerable, ilistsource     tentity : class 

so can extend result further linq methods, linq objects executed in memory returned result set sql query. can not extend linq entities executed in database. hence, adding .where filters dbsqlquery<t> not have influence on database query , set of data loaded db memory.

that's not surprising mean otherwise partial expression tree (from where method) had translated sql , merged hand-written sql statement correct new composed sql statement results , sent database. sounds pretty hard task me.