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.

Answer 1

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.