Converting string to int in linq to entities on bi

问题:

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 :(

回答1:

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.

回答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;