How to check numeric value in linq to entities

Question

I'm trying to translate the following SQL-Select to LINQ to Entities and have a problem with an isnumeric check and hope that the community can give me a hint how to resolve it.

select top(1) column1
from myDataTable with (nolock)
where id = '123456789'
and ISNUMERIC(column1) = 0

The tipping point is column1 which is from type varchar and can contain either alphanumeric values as well as numeric values.

I tried the following in LINQ to Entities to get the whole row from the table:

int column1Value;
int paramId = 123456789;
var entry = myDataTable.FirstOrDefault(
x => x.Id == paramId && !(int.TryParse(x.column1, out column1Value))
);

In the next step I wanted to check entry for null and then get the value from column1.

But when I debug it, I get the following error

LINQ to Entities does not recognize the method 'Boolean TryParse(System.String, Int32 ByRef)'.

How can I implement the isnumeric check in LINQ?

Answer 1

var value1 = (from d in myDataTable 
        where d.id == "123456789"
        && SqlFunctions.IsNumeric(d.column1) == 0
        select d.Column1).FirstOrDefault();

IsNumeric and, in fact, all of SqlFunction exist merely to tell Linq to use the same-named function in SQL. It's defined like this:

[System.Data.Objects.DataClasses.EdmFunction("SqlServer", "ISNUMERIC")]
public static int? IsNumeric (string arg);

SqlFunctions defines many other SQL functions you can use, including CurrentUser, DataLength, IsDate and Checksum.

You can use the EdmFunctionAttribute on C# methods allow using your user-defined SQL function in LINQ statements.