T-SQL IsNumeric() and Linq-to-SQL

Although ISNUMERIC is missing, you could always try the nearly equivalent NOT LIKE '%[^0-9]%, i.e., there is no non-digit in the string, or alternatively, the string is empty or consists only of digits:

from x in table where SqlMethods.Like(x.col, 'EU[0-9]%') // starts with EU and at least one digit  && !SqlMethods.Like(x.col, '__%[^0-9]%') // and no non-digitsselect x;

Of course, if you know that the number of digits is fixed, this can be simplified to

from x in table where SqlMethods.Like(x.col, 'EU[0-9][0-9][0-9][0-9][0-9][0-9]')select x;

You could make use of the ISNUMERIC function by adding a method to a partial class for the DataContext. It would be similar to using a UDF.

In your DataContext's partial class add this:

partial class MyDataContext{    [Function(Name = "ISNUMERIC", IsComposable = true)]    public int IsNumeric(string input)    {        throw new NotImplementedException(); // this won't get called    }}

Then your code would use it in this manner:

var query = dc.TableName              .Select(p => new { p.Col, ReplacedText = p.Col.Replace("EU", "") })              .Where(p => SqlMethods.Like(p.Col, "EU%")                        && dc.IsNumeric(p.ReplacedText) == 1)              .OrderByDescending(p => p.ReplacedText)              .First()              .Col;Console.WriteLine(query);

Or you could make use MAX:

var query = dc.TableName              .Select(p => new { p.Col, ReplacedText = p.Col.Replace("EU", "") })              .Where(p => SqlMethods.Like(p.Col, "EU%")                  && dc.IsNumeric(p.ReplacedText) == 1);var result = query.Where(p => p.ReplacedText == query.Max(p => p.ReplacedText))                  .First()                  .Col;Console.WriteLine("Max: {0}, Result: {1}", max, result);

Depending on your final goal it might be possible to stop at the max variable and prepend it with the "EU" text to avoid the 2nd query that gets the column name.

EDIT: as mentioned in the comments, the shortcoming of this approach is that ordering is done on text rather than numeric values and there's currently no translation for Int32.Parse on SQL.

As you said, there is no translation for IsNumeric from LINQ to SQL. There are a few options, you already wrote database function and stored procedure down. I like to add two more.

Option 1: You can do this by mixing LINQ to SQL with LINQ to Objects, but when you've got a big database, don't expect great performance:

var cols = (from c in db.Table where c.StartsWith("EU") select c).ToList();var stripped = from c in cols select int.Parse(c.Replace("EU", ""));var max = stripped.Max();

Option 2: change your database schema :-)