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

Question

Мне нужно найти самое высокое значение из базы данных, которое удовлетворяет определенному соглашению о форматировании. В частности, я хотел бы найти самое высокое значение, которое выглядит как

EU999999 ("9" - любая цифра)

select max (col) вернет что-то вроде 'EUZ...', например, которое я хочу исключить. Следующий запрос делает свое дело, но я не могу произвести это через Linq-to-SQL. Кажется, в SQL Server нет перевода для функции isnumeric ().

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

Написание функции базы данных, хранимой процедуры или чего-либо еще такого рода находится далеко в списке моих предпочтительных решений, потому что эта таблица является центральной для моего приложения, и я не могу легко заменить объект таблицы чем-то другим.

Какое будет лучшее решение?

Answer 1

Хотя ISNUMERIC отсутствует, вы всегда можете попробовать почти эквивалентный NOT LIKE '%[^0-9]%т.е. в строке нет нецифровых символов, или, наоборот, строка пуста или состоит только из цифр:

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;

Конечно, если вы знаете, что число цифр является фиксированным, это можно упростить до

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

Вы могли бы использовать ISNUMERIC функция путем добавления метода к частичному классу для DataContext. Это было бы похоже на использование UDF.

В частичном классе вашего DataContext добавьте это:

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

Тогда ваш код будет использовать его следующим образом:

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);

Или вы можете использовать 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);

В зависимости от вашей конечной цели можно остановиться на max переменной и добавьте к ней текст "EU", чтобы избежать второго запроса, который получает имя столбца.

РЕДАКТИРОВАТЬ: как уже упоминалось в комментариях, недостаток этого подхода заключается в том, что упорядочение выполняется по тексту, а не по числовым значениям, и в настоящее время нет перевода для Int32.Parse на SQL.

Answer 3

Как вы сказали, IsNumeric не переводит с LINQ на SQL. Есть несколько вариантов, вы уже написали функцию базы данных и хранимую процедуру. Я хотел бы добавить еще два.

Вариант 1: Вы можете сделать это, смешав LINQ to SQL с LINQ to Objects, но когда у вас большая база данных, не ожидайте отличной производительности:

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();

Вариант 2: изменить схему базы данных:-)

Answer 4

Я предлагаю вернуться к встроенному SQL и использовать метод DataContext.ExecuteQuery(). Вы бы использовали SQL-запрос, который вы разместили в начале.

Это то, что я сделал в подобных ситуациях. Не идеальный, предоставленный, из-за отсутствия проверки типов и возможных синтаксических ошибок, но просто убедитесь, что он включен в любые модульные тесты. Не каждый возможный запрос покрывается синтаксисом Linq, поэтому в первую очередь существует существование ExecuteQuery.