4

i wana convert string to int in linq to entities but Convert.ToInt32 and int.Parse can not translate to sql And my database is BIG and can not get all of them to memory (over 60k record and get bigger) my query is something like

int myNumber = 1390;
var result = from dr in myEntitiy.TableX
             where (Convert.ToInt32(dr.stringYear) > myNumber) // <== error
             select dr;

error say cannot translate convert method

LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression.

whats solution

UPDATE : If there is no way to do this please make me sure about this, then i have to change field type in database but it will be hard :(

mX64
  • 388
  • 1
  • 7
  • 24
  • 1
    Why do you keep the year as a string? – Jon May 09 '12 at 14:09
  • 2
    a stupid one did that :/ and i cant change that now – mX64 May 09 '12 at 14:10
  • can you try to do the conversion first? Store the results in a list and then do your comparision – haydnD May 09 '12 at 14:21
  • 1
    as i said its a big database and cant do this like (.ToList or .AsEnumerable) also database is on the web – mX64 May 09 '12 at 14:23
  • can you use a dictionary? When you say database is on the web do you mean the cloud? – haydnD May 09 '12 at 14:30
  • no , my mean , database is in the internet not a ethernet network or local network – mX64 May 09 '12 at 14:34
  • if you can change the field in your database I would suggest that. It will be beneficial in the long run. if you ever have to make updates in the future you don't want to have to do this again. :) – haydnD May 09 '12 at 14:44

2 Answers2

4

I think it is quite safe to do the comparison as string, unless you have years < 1000 or > 9999:

... dr.stringYear.CompareTo(myNumberString) > 0

EF translates this into a SQL predicate like

WHERE [alias].[stringYear] > @p

which is possible in SQL but not in C#.

An advantage would be that any index on stringYear may be usable in an execution plan. Converting stringYear to number eliminates any index.

This method is still useful when the string column contains jagged string values. In such a case the predicate should be combined with Length. For example, to find all entities where any numeric string as integer is greater than some reference value

var len = myNumberString.Lenght;

var query = 
    from row in context.LegacyTable
    where row.NumericString.CompareTo(myNumberString) > 0
       && row.NumericString.Length >= len
    select row;

Then the query engine can't use indexes for the length comparison but it might be able to do so for the > comparison.

Gert Arnold
  • 105,341
  • 31
  • 202
  • 291
  • ty , its work and solve my problem, i knew , '>' could use in sql to compare to string like int , but i dint know how it could use in EF . but as you mention its work just if length of field's data be same .let's see if there is a 100% solution for this, anyway ty – mX64 May 10 '12 at 09:54
  • With years you'll be fine. The 100% solution is suggested by @ironman99 above: change the database field. I would make a very strong case for that! – Gert Arnold May 10 '12 at 09:57
  • yeah , if i could change database ,but i guess there is no better way than your suggested way, because there is no other way in sql to do that . – mX64 May 10 '12 at 10:11
  • _Someone_ can change the database I suppose. Maybe it's hard to pull the right strings. Well, sometimes you have to settle with second best. – Gert Arnold May 10 '12 at 10:17
2

This works, but the generated SQL will be messy

int myNumber = 1390;
var result = from dr in myEntitiy.TableX
             let num = myEntitiy.TableX.Take(1).Select(x => dr.stringYear).Cast<int>().FirstOrDefault()
             where num > myNumber
             select dr;
Aducci
  • 26,101
  • 8
  • 63
  • 67