Opertor '==' cannot be applied to operands of type 'int?' and 'string' on where clause

Question

While executing below linq query on controller, I got this error:

Opertor '==' cannot be applied to operands of type 'int?' and 'string'

Controller:

from t in db.ASN_ITEM
    join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
    where
         t.REGION_ID == RegionId && 
         (Status == "" || Status == "-1" || t.SCAN_STAT == Status)

Here type of t.SCAN_STAT is int? and type of variable status is string.

I have tried this one:

(Status == "" || Status == "-1" || SqlFunctions.StringConvert((double)t.SCAN_STAT) == Status)

But not fetching the desired data based on the condition.

When I tried (Status == "" || Status == "-1" || t.SCAN_STAT.ToString() == Status), I got this error:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression.

These are the solutions I got from other sites. Now how can I solve this error?

This might help. http://stackoverflow.com/questions/992189/c-sharp-linq-to-sql-how-to-express-convert-as-int You may go the other way round with this solution however, i.e., convert status to int and check. — Victor Mukherjee, Feb 19 '16 at 08:35
If `Status`variable can contain only int values (or null value), consider declaring it as `int?`. — Martin Staufcik, Feb 19 '16 at 08:40

score 1 · Answer 1 · answered Feb 19 '16 at 08:38

First of all, when you use EntityFramework, it will try to convert the System.String ToString() method to a SQL and it fails to do that as it cannot convert to SQL statement/ 'store expression'.

You should just move the ToString() method out of store expression. For this, You can first execute the EF expression using ToList() or ToEumerable() method. Then use your condition on that (Status == "" || Status == "-1" || t.SCAN_STAT == Status)'

Or you can try this also :

from t in db.ASN_ITEM
join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
let SCAN_STAT = t.SCAN_STAT.ToString()
where
     t.REGION_ID == RegionId && 
     (Status == "" || Status == "-1" || SCAN_STAT == Status)

tede24 · Accepted Answer · 2016-02-19T09:08:47.870

1

Simply convert Status to int before linq query, then use it in the query as int:

var intStatus = (int)null;
int.TryParse(Status,out intStatus);

from t in db.ASN_ITEM
    join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
    where
         t.REGION_ID == RegionId && 
         (intStatus == null || intStatus == -1 || t.SCAN_STAT == intStatus;

edited Feb 19 '16 at 09:08

answered Feb 19 '16 at 08:38

tede24

2,304
11
14

1

@LimnaD'silva but you should do this outside the linq query. It's impossible you get this error if you di before – tede24 Feb 19 '16 at 09:04

Ian · Answer 3 · 2016-02-19T08:49:12.560

Consider of making intermediate variable for checking by using LINQ let:

from t in db.ASN_ITEM
join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
let test = t.SCAN_STAT.ToString()
where
     t.REGION_ID == RegionId && 
     (Status == "" || Status == "-1" || test == Status)

This way, you could compare string to string

Note: beware of the string format for each though. If needed be, you could even ensure that they have the same format, for instance:

from t in db.ASN_ITEM
join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
let test = t.SCAN_STAT.ToString().ToUpper().Trim()
let test2 = Status.ToUpper().Trim()
where
     t.REGION_ID == RegionId && 
     (Status == "" || Status == "-1" || test == test2)

Alternatively, you could also do the reverse:

int? test = string.IsNullOrWhiteSpace(Status) ? null : Convert.ToInt32(Status); //or, adjust the null as you wish

Then use it like this:

from t in db.ASN_ITEM
join t0 in db.ITEM_MASTER on t.ITEM_MASTER.ITEM_CODE equals t0.ITEM_CODE        
where
     t.REGION_ID == RegionId && 
     (Status == "" || Status == "-1" || t.SCAN_STAT == test)

This way, you compare int? with int?

Linq to entities won't recognize ToString function – tede24 Feb 19 '16 at 08:42 — tede24, Feb 19 '16 at 08:42

score 0 · Answer 4 · answered Feb 19 '16 at 08:39

You need to get the value in your int?. To do this you need to use .Value but before you need to check if t.SCAN_STAT has value to prevent argument null exception:

(Status == "" || Status == "-1" || (t.SCAN_STAT.HasValue && t.SCAN_STAT.Value.ToString() == Status))

Opertor '==' cannot be applied to operands of type 'int?' and 'string' on where clause

4 Answers4