How do I query a VARCHAR as an integer

Question

Hello, I have a column that holds VARCHARs and I have a flag that determines if the value is a string or integer. I would like perform a search on the integers but they are stored as VARCHAR. How do I do this?

I am trying to do this in a where clause.

var q = from x in context.table
        where x.Criteria > 0
        select new
        {
              x
        }

Answer 1

CAST(TheColumn AS INTEGER)

Answer 2

sql

select isnull(Convert(int, varcharColumn),0)
from atable
where isInteger = 1

linq

var q = context.YourCustomView;

Answer 3

You'll probably need to pull down the values from the server as strings and do the conversion in LINQ to Objects.

var q = context.table
    .Where(x => x.Criteria > 0)
    .Select(x => x.IntOrStringValue)
    .AsEnumerable()
    .Select(v => Convert.ToInt32(v));

replace x.Criteria > 0 with your logic that indicates the value is an integer. replace x.IntOrStringValue with your column containing the integers.