How to convert string to int for SQL?

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		
}

Comments

  • 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.
  • Awesome, thanks so much
  • edited August 30
    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();
  • 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
  • edited August 31
    Hmmm I get error compiling with this line:
    where IsNumeric(p.Provnum) == 1
    Linq does not recognize IsNumeric
  • 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();
  • 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).
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!