How to convert string to int for SQL?

Zukoh

Brand spanking new to LINQ, enjoy it so far but having hard time with what seems like simple task. Tried googling all different types with no success. Tried int.Parse, Convert.ToInt32,

Converting to C# Programs (with this have no idea how to point to my table)
Thanks for any help

from p in ProviderInfo

where "056428" == p.Provnum
//where 056428 == int.Parse( p.Provnum ) // Method 'Int32 Parse(System.String)' has no supported translation to SQL.
//where 056428 == Convert.ToInt32( p.Provnum ) // SqlException: Conversion failed when converting the nvarchar value '37E024' to data type int.

select new{ 

	p.Provnum 
	,p.PROVNAME
	,p.PHONE		
}

sgmoore

Your simple task is made more complicated by the fact that you have non-integer values in the column, eg 37E024

I don't think Linq2Sql supports isNumeric, so you can either :

fix your table structure and your data,

use TSQL and IsNumeric or

try to use not like to exclude the non-numeric data.

For example, to exclude using a not like you could try

     from p in ProviderInfo
     where 
        !SqlMethods.Like(p.Provnum,"%[^0-9,.]%")        
          && 056428 == Convert.ToInt32( p.Provnum ) 
...

The . and , are included in case you have values like "123,456" or "123.", but this can also return values like 123,456.78 and may throw errors with values like ",," , so this isn't really recommended if you can not control the sql data.

Zukoh

Awesome, thanks so much

JeffMercado

Many of the methods you'd typically use in the Convert class can be converted to corresponding sql function calls. So you would use `Convert.ToInt32` or other conversion functions.

You can also map out the sql functions that you want to use in your query that are not directly supported. You don't have to implement them at all, it would be translated to the appropriate sql calls.

Since your linqpad script code is actually inserted into the data context code, you could just add this anywhere in your script. Just make sure you use C# Program for your language.

void Main()
{
    var provNum = 56428;
    var query =
        from p in ProviderInfo
        where IsNumeric(p.Provnum) == 1
        where Convert.ToInt32(p.Provnum) == provNum
        select new
        {
            p.Provnum,
            p.PROVNAME,
            p.PHONE,
        };
    query.Dump();
}

[FunctionAttribute(Name="ISNUMERIC", IsComposable=true)]
public int IsNumeric(string str) => throw new NotImplementedException();

sgmoore

That's a good trick to know.

One minor issue

        where IsNumeric(p.Provnum) == 1
        where Convert.ToInt32(p.Provnum) == provNum

seems to try the convert before the IsNumeric test and hence can throw the Sql Conversion error.

It should be

        where IsNumeric(p.Provnum) == 1
        &&  Convert.ToInt32(p.Provnum) == provNum

or

        where Convert.ToInt32(p.Provnum) == provNum
        where IsNumeric(p.Provnum) == 1

Zukoh

Hmmm I get error compiling with this line:
where IsNumeric(p.Provnum) == 1
Linq does not recognize IsNumeric

JeffMercado

You must add this in your script to be able to use it.

[FunctionAttribute(Name="ISNUMERIC", IsComposable=true)]
public int IsNumeric(string str) => throw new NotImplementedException();

dm_at_dv

In SQL comparing ints and strings (nchar,char,nvarchar,...) is usually best done by converting the int to the appropriate string type and not by conditionally converting the string to an int.

/** prepare (on tempdb)
create table Demo(id int not null primary key, val nvarchar(10) not null);
insert Demo values(0,'zero'),(1,'1'),(2,'1+1'),(333,'333');
**/
//C# Expression
from d in Demo 
where d.Id.ToString() == d.Val
select new{ d.Id, d.Val }
/** generated SQL
SELECT [t0].[id] AS [Id], [t0].[val] AS [Val]
FROM [Demo] AS [t0]
WHERE (CONVERT(NVarChar,[t0].[id])) = [t0].[val]
**/

Note that even guarding the WHERE clause by an ISNUMERIC or LIKE ... condition is not guaranteed to suppress SQL conversion errors from string to int! And by the way the TSQL ISNUMERIC() built in function is rather useless, e.g. ISNUMERIC('.') returns 1 (= true).