c# - How to know if a field is numeric in Linq To SQL

Question

I need to select the rows of a table where a column value is numeric, any Help?

EDIT: I have a varchar column and I need to select the ones that are numbers and the ones that are not.

EDIT 2: Integer.TryParse cannot be use because it cannot be translate to SQL.

Answer 1

Open up your DBML (LINQ-to-SQL) file in an XML editor, go down to the end of the file and paste this just before the '</Database>' node:

<Function Name="ISNUMERIC" IsComposable="true">
    <Parameter Name="Expression" Parameter="Expression" Type="System.String" DbType="NVarChar(4000)" />
    <Return Type="System.Boolean" DbType="BIT NOT NULL"/>
</Function>

Now, you can use the already-in-SQL function called "ISNUMERIC". Here's how:

var blah = myDataContext.Accounts.Where(account=>
    myDataContext.ISNUMERIC(account.ID) == true);

There you go :)

You may also find these functions useful to copy:

<Function Name="RAND" IsComposable="true">
  <Return Type="System.Double" DbType="Float NOT NULL" />
</Function>
<Function Name="NEWID" IsComposable="true">
  <Return Type="System.Guid" DbType="UniqueIdentifier NOT NULL" />
</Function>