Converting a string to number DB2 Entity Framework

Question

I'm migrating a legacy DB2 Application to a web application and I'm using ASP.NET WebAPI with entity framework.

This is the first time I'm working with DB2.

I have installed the necessary drivers and fix packs for DB2 database.I have generated the EDMX for existing DB2 database tables.

Everything works fine. I have encountered a strange issue that, In one of the DB2 SQL query, a string column is compared with a number value.

Consider the below query

SELECT DISTINCT DB23.EMPLOYEES.FIRST_NAME,   
    DB23.EMPLOYEES.LAST_NAME,   
    STRIP("DB23"."EMPLOYEES"."STATE_NAME") STATE_NAME, 
    DB23.CONTACT.PHONE_NO
FROM DB23.EMPLOYEES,   
     DB23.CONTACT 
WHERE ( DB23.EMPLOYEES.EMP_ID = DB23.CONTACT.EMP_ID ) and     
     ( DB23.CONTACT.EMP_REG_NO < '900000' )   
ORDER BY DB23.EMPLOYEES.FIRST_NAME ASC

here the column EMP_REG_NO is a string type and it is compared with a number using less than '<' operator and the strange thing is that the same query runs perfectly in IBM DB2 Server Client.

The below one is the Linq expression that I have written

from emp in _DB2Context.EMPLOYEES
join cont in _DB2Context.CONTACT on emp.EMP_ID equals cont.EMP_ID
where cont.EMP_REG_ID < 900000
select new {
   emp.FIRST_NAME
   emp.LAST_NAME
   emp.STATE_NAME
   cont.PHONE_NO
};

Here we cannot compare the number 900000 with string type column.

I have tried like this

from emp in _DB2Context.EMPLOYEES
join cont in _DB2Context.CONTACT on emp.EMP_ID equals cont.EMP_ID
where Convert.ToInt64(cont.EMP_REG_ID) < 900000
select new {
   emp.FIRST_NAME
   emp.LAST_NAME
   emp.STATE_NAME
   cont.PHONE_NO
};

This gave me an error that LINQ doesn't recognize method Convert.ToInt64() and I understood that There is no equivalent method to achieve the conversion in T-SQL statement.

Any Ideas?

Update:

The below is the table structure

TABLE - EMPLOYEES
***************************************************************************

Name            Data Type     Type schema   Length   Scale   Nulls Hidden
--------------- ------------- ------------- -------- ------- ----- --------
EMP_ID          CHARACTER     SYSIBM               2       0 N     Not 
FIRST_NAME      CHARACTER     SYSIBM              30       0 N     Not     
LAST_NAME       CHARACTER     SYSIBM              30       0 N     Not     
STATE_NAME      CHARACTER     SYSIBM              25       0 N     Not     
FIPS_CTRY_CD    CHARACTER     SYSIBM               2       0 N     Not    
POLK_STATE_CD   CHARACTER     SYSIBM               2       0 N     Not     
MAIL_STATE      CHARACTER     SYSIBM              15       0 N     Not    

****************************************************************************

TABLE - CONTACT
****************************************************************************

Name            Data Type     Type schema   Length   Scale   Nulls Hidden
--------------- ------------- ------------- -------- ------- ----- --------
EMP_ID          CHARACTER     SYSIBM               2       0 N     Not 
REG_NO          CHARACTER     SYSIBM               6       0 N     Not     
REG_STATE_CD    CHARACTER     SYSIBM               2       0 Y     Not     
PHONE_NO        CHARACTER     SYSIBM              15       0 N     Not     
REG_STATE       CHARACTER     SYSIBM               2       0 N     Not     
CORP_NAME       CHARACTER     SYSIBM              30       0 N     Not     

***************************************************************************

The below is the POCO classes generated by the entity framework

public partial class EMPLOYEES
{
    public string EMP_ID { get; set; }
    public string FIRST_NAME { get; set; }
    public string LAST_NAME { get; set; }
    public string STATE_NAME { get; set; }
    public string FIPS_CTRY_CD { get; set; }
    public string POLK_STATE_CD { get; set; }
    public string MAIL_STATE { get; set; }
}

public partial class CONTACT
{
    public string EMP_ID { get; set; }
    public string REG_NO { get; set; }
    public string REG_STATE_CD { get; set; }
    public string PHONE_NO { get; set; }
    public string REG_STATE { get; set; }
    public string CORP_NAME { get; set; }
}

moreover, I can able to run the below simple query in DB2 command line processor

SELECT
 DB23.CONTACT.EMP_ID
FROM DB23.CONTACT 
WHERE DB23.CONTACT.EMP_ID < '900000'

SELECT
 DB23.CONTACT.EMP_ID
FROM DB23.CONTACT 
WHERE DB23.CONTACT.EMP_ID < 900000

Both DB2 Query returns result

EMP_ID
------
123   
234

But I cannot do the same with LINQ like,

from cont in _DB2Context.CONTACT
where cont.DLR_NO < 90000  // here getting an error says that Operator '<' cannot be applied to operands of type 'string' and 'int'
select new {
  cont.DLR_NO
}

from cont in _DB2Context.CONTACT
where cont.DLR_NO < '90000'  // here getting an error says that Operator '<' cannot be applied to operands of type 'string' and 'char'
select new {
  cont.DLR_NO
}

Thanks, Gowrish.

Possible duplicate http://stackoverflow.com/questions/16694716/entity-framework-linq-expression-converting-from-string-to-int — Kevin D, Feb 16 '15 at 14:48
I have posted this because the confusion with linq and actual DB2 query..Is DB2 can able to stores numbers as strings and perform numerical operations on it? if so how to get it working in LINQ? — Gowrish, Feb 16 '15 at 15:53
Please edit your question to include the DB2 DDL for the table, or at least the output of `DESCRIBE TABLE db23.employees` and `DESCRIBE TABLE db23.contact` — Fred Sobotka, Feb 16 '15 at 19:20

score 1 · Accepted Answer · answered Feb 18 '15 at 05:06

Assuming that it will be translated into its SQL equivalent, try using something like

where cont.DLR_NO.CompareTo("90000") < 0

As for string to integer conversion, you have no guarantee that a CHARACTER column in the database will only ever contain digits, which makes any program that insists on casting that column to an integer a risky bet. In those situations, it's safer to convert your numeric literal to a string first (from 90000 to "90000") so you're simply comparing two strings.

Converting a string to number DB2 Entity Framework

1 Answers1