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

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>

I don't know if there is a mapping for int.TryParse() in LinqToSQL, but you could probably do it in two steps by performing the query, casting to a List, then selecting out of the list with LinqToObjects.

int i;var query = context.Table.ToList();var intQuery = query.Where( t => int.TryParse( t.Column, out i ) );

You might want to look at Dynamic LINQ, too. That would allow you to do something like:

var query = context.Table.Where( "IsNumeric(Column)" );

EDIT Dynamic LINQ is available in the VS2008 Code Samples, linked to from Scott Guthrie's blog, which I've linked above.

SqlFunctions.IsNumeric

http://msdn.microsoft.com/en-us/library/system.data.objects.sqlclient.sqlfunctions.isnumeric.aspx

For example:

    private int GetLastGoodNumber(string PartNum)    {        return (from row in Db.SerialNo                where                   row.PartNum == PartNum                let nullable = System.Data.Objects.SqlClient.SqlFunctions.IsNumeric(row.SerialNumber)                where nullable != null                select nullable.Value).Max();    }