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

Question

I need to find the highest value from the database that satisfies a certain formatting convention. Specifically, I would like to fund the highest value that looks like

EU999999 ('9' being any digit)

select max(col) will return something like 'EUZ...' for instance that I want to exclude. The following query does the trick, but I can't produce this via Linq-to-SQL. There seems to be no translation for the isnumeric() function in SQL Server.

select max(col) from table where col like 'EU%' 
    and 1=isnumeric(replace(col, 'EU', ''))

Writing a database function, stored procedure, or anything else of that nature is far down the list of my preferred solutions, because this table is central to my app and I cannot easily replace the table object with something else.

What's the next-best solution?

Answer 1

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-digits
select 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;

Answer 2

As you said, there is no translation for IsNumeric from LINQ to SQL 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 :-)

Answer 3

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.

Answer 4

My suggestion is to fall back to in-line SQL and use the DataContext.ExecuteQuery() method. You would use the SQL query you posted in the beginning.

This is what I have done in similar situations. Not ideal, granted, due to the lack of the type-checking and possible syntax errors, but simply make sure it is included in any unit tests. Not every possible query is covered by the Linq syntax, hence the existence of ExecuteQuery in the first place.