2

I'm trying to use the linq query, when I try to convert the height value somehow I'm getting error during runtime.

Error:

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

Is there a better way to compare the height value?

case "HeightFrom":
     photosquery = photosquery.Where(x => Convert.ToInt32(x.physical.BodyHeight.TrimEnd()) >= Convert.ToInt32(height));
     break;
ocuenca
  • 38,548
  • 11
  • 89
  • 102
dotNETEngineer
  • 143
  • 2
  • 13
  • Somewhat unrelated, but can you guarentee that `BodyHeight` and `Height` will _always_ be convertable into `int`? You are running a pretty high risk of an exception, why not create `int` properties on your model that have the proper error handling `int.TryParse()` and then just compare the new `int` properties? – maccettura Oct 20 '17 at 18:28
  • Avoid doing manipulations of any kind on the database field (the left-side). That will always result in a nonsargable query. – Matt Johnson-Pint Oct 20 '17 at 18:30
  • What you're trying to do doesn't seem to be well supported in Linq-to-Entities, see this SO answer: https://stackoverflow.com/a/16694769/4276832 – bsinky Oct 20 '17 at 18:31

3 Answers3

1

The issue is the Linq provider (probably from EF) you are using doesn't support Convert.ToInt32. A quick solution to this issue is using AsEnumerable extension method to switch to Linq to Objects

 photosquery = photosquery.AsEnumerale()
                          .Where(x => Convert.ToInt32(x.physical.BodyHeight.TrimEnd()) >= Convert.ToInt32(height));

Now if BodyHeight is a string and you are saving an integer, I strongly suggest to change the type of that column if you can. Your query could be executed entirely in the server side:

int value=Convert.ToInt32(height);
photosquery = photosquery.Where(x => x.physical.BodyHeight >= value);
ocuenca
  • 38,548
  • 11
  • 89
  • 102
  • This is a great suggestion. Let me see if I can change the table design to integer instead of string. I know it's going to be tedious task in changing the data for less than 1000 records, but this should resolve the problem permanently. I have numieric value for display (ex: dropdownlist text provide height - 6' 2", but I may have value as 62, based on user search, I need to compare the value and bring the matched data using linq. That's what I'm trying to do. – dotNETEngineer Oct 20 '17 at 18:52
  • Yes, I think doing that change you will have a permanent solution and a better performance in your queries. Try to validate the user input and do the cast before create the query – ocuenca Oct 20 '17 at 18:56
  • I converted to int for the column, luckily existing data was accepted as is. Now, I'm removing the table from edmx and adding it back with the design change. I may have to modify the code in few aspx.cs files and that should work. It's easier than making a change to the code. Thaks a lot. – dotNETEngineer Oct 20 '17 at 18:59
0
case "HeightFrom":
 var h = Convert.ToInt32(height);
 photosquery = photosquery.Where(x => Convert.ToInt32(x.physical.BodyHeight.TrimEnd()) >= h);
 break;

EF won't know how to translate that last convert. If the first one doesn't work, just try int.parse(x.physical.BodyHeight.TrimEnd()) or a direct cast (int)x.physical.BodyHeight.TrimEnd()

If it were me, and I had the time, I would probably map that model to another model with the correct data types before I started querying.

Ryan Bennett
  • 3,404
  • 19
  • 32
  • As you have correclty pointed out that EF won't know how to translate that last convert, the same holds for the first convert :) – Christos Oct 20 '17 at 18:33
  • THe problem is with physical.BodyHeight value conversion to int, not the height value. I get the same error as below: {"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression."} – dotNETEngineer Oct 20 '17 at 18:40
0

If your data is not a lot you can use

case "HeightFrom": photosquery = photosquery.ToList().Where(x => Convert.ToInt32(x.physical.BodyHeight.TrimEnd()) >= Convert.ToInt32(height)); break;

But for heavy data is not ok and load all data at first then filter it

Amirhossein Yari
  • 2,054
  • 3
  • 26
  • 38