T-SQL IsNumeric () и Linq-SQL
Я должен найти самое высокое значение от базы данных, которая удовлетворяет определенную конвенцию форматирования. А именно, я хотел бы найти самое высокое значение, которое похоже
EU999999 ('9' являющийся любой цифрой)
выбор, макс. (седло), возвратит что-то как 'EUZ...', например, что я хочу исключить. Следующий запрос добивается цели, но я не могу произвести это через Linq-SQL. Кажется, нет никакого перевода для isnumeric () функции в SQL Server.
select max(col) from table where col like 'EU%'
and 1=isnumeric(replace(col, 'EU', ''))
Пишущий функцию базы данных, хранимую процедуру, или что-либо еще той природы далеко снижается на список моих предпочтительных решений, потому что эта таблица является центральной к моему приложению, и я не могу легко заменить объект таблицы чем-то еще.
Каково следующее лучшее решение?
4 ответа
Хотя 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;
Как вы сказали, нет перевода 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: измените схему базы данных :-)
Вы можете использовать функцию 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", чтобы избежать второго запроса, который получает имя столбца.
EDIT: как упоминалось в комментариях, недостатком этого подхода является то, что упорядочивание производится по тексту, а не по числовым значениям, и в настоящее время нет перевода для Int32.Parse на SQL.
Я предлагаю вернуться к встроенному SQL и использовать метод DataContext.ExecuteQuery (). Вы должны использовать SQL-запрос, который вы разместили в начале.
Это то, что я делал в подобных ситуациях. Не идеально, конечно, из-за отсутствия проверки типов и возможных синтаксических ошибок, но просто убедитесь, что он включен в любые модульные тесты. Не каждый возможный запрос покрывается синтаксисом Linq, поэтому в первую очередь существует ExecuteQuery.