Comparing String Column with Int column

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 mod开发者_Go百科ify 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.

---

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.

---

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