Comparing String Column with Int column

Question

I'm working on EF CTP5 Code First development with an existing database. I need to get the data from two tables by comparing columns of different types.

For example - Here p.ColumnA is varchar as q.ColumnA is int but the values might be the same for few records. So, I'm trying to do Convert.ToInt32 which does not work. I do not have complete control over the database to modify the table.

from p in context.TableA
from q in context.TableB
where p.ColumnZ == "ABC" &&
  (p.ColumnA == null || Convert.ToInt32(p.ColumnA) == q.ColumnA) &&
  (p.ColumnB == null || p.ColumnB == q.ColumnB)
select p.ColumnC

Can someone suggest a solution? Thanks.

Need more information. Is Convert.ToInt32 throwing and exception? Are you getting other errors? What is happening exactly? — alexD, May 27 '11 at 21:24
I'm getting an exception when using Convert.ToInt32. Exception: _"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression."_ — MNIK, May 27 '11 at 21:26

score 3 · Accepted Answer · edited May 23 '17 at 11:46

3

When you write a linq statement that interacts with the entityframework it trys to convert all the commands to SQL. Because there is no sql command for Convert.ToInt32 it is throwing an error. This post describes a way to cal the sql functions for converting types. It should help you.

edited May 23 '17 at 11:46

Community

1
1

answered May 27 '11 at 21:32

Jay

6,224
4
20
23

Thanks for your reply. I understand that the LINQ statement would be converted to SQL expression, that's why my statement would fail. I knew about SqlFunctions but was always reluctant to use those methods 'coz I'm targeting my app towards both Sql Server & Oracle eventually, so will these SqlFunctions methods get translated to Oracle when used with their provider? – MNIK May 27 '11 at 22:28
I'm not sure, but my guess would be no. `SqlFunctions` exits under `System.Data.SqlClient` namespace which is spacific to Sql Server. You could get lucky though. The only other option would be to load the dataset into memory and do the type conversion on the .Net side. – Jay May 27 '11 at 22:35

StriplingWarrior · Answer 2 · 2011-05-27T22:23:05.113

As the other posters have explained, LINQ to ~~SQL~~ Entities doesn't know how to translate Convert.ToInt32 into a SQL expression (LINQ to SQL can apparently handle this). From what I can tell, it doesn't support int.Parse, either. But since you're just doing an equality comparison (rather than greater/less than), you should be able to achieve the same result by converting the int to a string, rather than converting the string to an int.

from p in context.TableA
from q in context.TableB
where p.ColumnZ == "ABC" &&
  (p.ColumnA == null || p.ColumnA == q.ColumnA.ToString()) &&
  (p.ColumnB == null || p.ColumnB == q.ColumnB)
select p.ColumnC

Comparing String Column with Int column

2 Answers2