Compare varchar to int in ASP.NET

Question

I'm trying to find all entries that have a SALARIO greater than 10,000. The SALARIO column in my SQL Server table is of type varchar and the query I'm trying to write goes like this

from e in db.ASALARIADOS 
where int.Parse(e.SALARIO) > 10000 
select e;

But I get a not supported exception. How should I handle this situation?

possible duplicate of [C# Linq to SQL: How to express "CONVERT(\[...\] AS INT)"?](http://stackoverflow.com/questions/992189/c-sharp-linq-to-sql-how-to-express-convert-as-int) — Andrei, Oct 08 '14 at 16:17
What is the type variable to store the result of your linq query? — Bonomi, Oct 08 '14 at 16:31
I tested your query but instead of a varchar I have a bool and it worked. In .net you don't have the varchar, you will have a string. Are you sure the exception is thrown in that code? — Bonomi, Oct 08 '14 at 16:37
The **real** solution would be to use the **most appropriate** datatype - in the database, to begin with! So if this is a salary (a numeric value) - ***why on earth*** is it stored as `varchar`?!?!?!? Fix this, make it a `DECIMAL(20,4)` or whatever makes sense to you - then all your problems with this go away automagically ..... — marc_s, Oct 08 '14 at 16:40

score 1 · Answer 1 · answered Oct 08 '14 at 16:48

1

from e in db.ASALARIADOS 
where Convert.ToInt32(e.SALARIO) > 10000 
select e;

answered Oct 08 '14 at 16:48

Hogan

69,564
10
76
117

that doesn't work for me, i get this message `LINQ to Entities does not recognize the method 'Int64 ToInt64(System.String)' method, and this method cannot be translated into a store expression.` – DelGiudice Oct 08 '14 at 17:38
@DelGiudice I used `ToInt32` – Hogan Oct 08 '14 at 17:43
@Hogan You cannot use Convert.ToInt32 or any Convert.To... inside a linq query, it will always return an error. – Ricardo Sanchez Oct 08 '14 at 20:04

score 0 · Answer 2 · answered Oct 08 '14 at 19:55

you can't do this because in the end, this is converted to a SQL query and it cannot translate your int.Parse to SQL.

What you can try however, is this.

Create a small function that will return true or false depending on whether the value passed in is more than 10000.

    public bool salarioCheck(string salario)
    {
        if (Convert.ToInt32(salario) > 10000)
        { return true; }
        return false;
    }

    public void yourMethod()
    {
        from e in db.ASALARIADOS 
        where salarioCheck(e)
        select e;
    }

So you are basically doing your conversion inside salarioCheck().

score 0 · Answer 3 · answered Oct 08 '14 at 20:09

0

Try this, I haven't tested it but I remember using "let" clause and Int32.Parse inside Linq to accomplish something similar in the past.

from e in db.ASALARIADOS 
let x = Int32.Parse(e.SALAIO.Trim()) 
where x > 10000 
select e;

answered Oct 08 '14 at 20:09

Ricardo Sanchez

6,079
3
24
31

score 0 · Answer 4 · answered Oct 09 '14 at 03:42

0

Try this as a workaround: haven't tried this one yet but this should work

db.ASALARIADOS.toList().Where(m => Convert.ToInt32(m.SALARIO) > 10000)

answered Oct 09 '14 at 03:42

Louis Michael

398
1
12

Compare varchar to int in ASP.NET

4 Answers4