Может ли LINQ to SQL запрашивать XML-поле на стороне сервера базы данных?
.NET 3.5, C#
У меня есть веб-приложение с функцией поиска. Некоторые поля, доступные для поиска, являются столбцами первого класса в таблице, но некоторые из них фактически являются вложенными полями внутри типа данных XML.
Раньше я создавал систему для динамического построения SQL для моего поиска. У меня была хорошая иерархия классов, в которой строились выражения SQL и условные операторы. Единственная проблема заключалась в том, что он был небезопасен от атак с использованием SQL-инъекций.
Я читал Отличная статья Роба Конери, в котором указывалось, что несколько запросов можно объединить в один запрос TSQL для сервера, если результат IQueryable никогда не перечисляется. Это заставило меня подумать, что моя конструкция динамического поиска была слишком сложной - мне просто нужно было объединить несколько выражений LINQ.
Например (надуманный):
Author:
ID (int),
LastName (varchar(32)),
FirstName (varchar(32))
context.Author.Where(xx => xx.LastName == "Smith").Where(xx => xx.FirstName == "John")
Результат в следующем запросе:
SELECT [t0].[ID], [t0].[LastName], [t0].[FirstName]
FROM [dbo].[Author] AS [t0]
WHERE ([t0].[LastName] = Smith) AND ([t0].[FirstName] = John)
Я понял, что это может быть идеальным решением для генерации простого динамического запроса, безопасного от SQL-инъекции - я бы просто перебрал свой результат IQueryable и выполнил дополнительные условные выражения, чтобы получить мое окончательное выражение для однократного выполнения.
Однако я не могу найти никакой поддержки для оценки данных XML. В TSQL, чтобы получить значение из узла XML, мы должны сделать что-то вроде
XMLField.value('(*:Root/*:CreatedAt)[1]', 'datetime') = getdate()
Но я не могу найти эквивалент LINQ to SQL для создания этой оценки. Есть ли такой? Я знаю, что могу оценить все условия, не относящиеся к XML, на стороне БД, а затем выполнить свою часть кода оценки XML, но мои данные достаточно велики, чтобы A) это большой сетевой трафик, чтобы снизить производительность и B) Я выйду - исключений памяти, если я не могу оценить первую сторону XML БД, чтобы исключить определенные наборы результатов.
Идеи? Предложения?
Дополнительный вопрос - если оценка XML на самом деле возможна на стороне БД, как насчет поддержки FLWOR?
@BenjaminGruenbaum Я давно не думал над этим вопросом. Мой вариант использования исчез, когда я сменил работу между тем и сейчас. Насколько я могу припомнить, я использовал подход, рекомендованный DanialM ниже, который заключался в вызове пользовательской функции. Не могу сказать, поскольку с тех пор я заметил какую-либо прямую поддержку в LINQ (но я тоже не искал)
Интересно - я попробую выловить новые ответы через 5 лет с наградой.
Ответы 3
Это интересный вопрос.
Прямо сейчас вы не можете указать SQL Server выполнять функции XML непосредственно из Linq.
Однако вы можете заставить Linq использовать пользовательские функции ...
Итак, вы можете настроить udf для обработки xml, получения правильных данных и т. д., а затем использовать это в своем выражении Linq. Это будет выполняться на сервере и должно делать то, что вы хотите. Однако есть важное ограничение: путь XML, который вы ищете (первый параметр для xmlColumn.value или аналогичного), должен быть встроен в функцию, потому что он должен быть строкой буквальный, он не может быть построен из входного параметра. (например). Таким образом, вы можете использовать UDF для получения полей, о которых вы знаете при написании UDF, но не как универсальный способ получения данных из столбцов XML.
Ознакомьтесь с разделом «Поддерживающие функции, определяемые пользователем» (UDF) в Отличная серия блогов Скотта Гутери о Linq to SQL для получения дополнительной информации о реализации.
Надеюсь это поможет.
Спасибо, Даниэль, это сработало! Я читал эту статью некоторое время назад, но она меня не впечатлила, когда я прочитал упомянутую выше статью об объединении нескольких запросов перед выполнением.
Чтобы прояснить ответ Даниэля - вы не можете использовать функцию для этого, если часть запроса XPath не исправлена. См. Запись в моем блоге по этому поводу: http://conficient.wordpress.com/2008/08/11/linq-to-sql-faq-xml-columns-in-sql/
По сути, вы не можете запросить столбец xml через LINQ to SQL. Несмотря на то, что он возвращает тип XElement, вы не можете выполнять какие-либо переводы SQL при попытке фильтрации по этому столбцу.
LINQ to SQL поддерживает использование UDF, но сам SQL не позволяет использовать строку параметров в XML-запросе xpath - он должен быть строковым литералом. Это означает, что он может работать, если XPath зафиксирован во время разработки, но не в том случае, если вы хотите иметь возможность передавать переменный оператор XPath.
Это приводит к появлению только двух других альтернативных способов сделать это: встроенного оператора SQL (который отрицает значение наличия LINQ) и написания для этого функции библиотеки SQL в .NET CLR.
См. Также обновление в этом блоге: conficient.wordpress.com/2011/01/20/…
Лучший способ уточнить ответ - это сделать именно это: отредактировать ответ, чтобы прояснить его, или, если у вас (в то время) не было прав на редактирование, прокомментируйте, чтобы отметить проблему. (Так работала SO в декабре '08? Понятия не имею, я присоединилась к ней восемь месяцев спустя. :-) Это было тогда, когда я присоединился, но SO в то время быстро росла и менялась.)
Это не лучший вариант, не для всех запросов и не полностью linq, но работает и работает быстро:
поле xml sql принимает ".Нанизывать", поэтому вы можете:
Dim txt as String = "<File>3</File>"
Return (From P In DC.LPlanningRefs Where P.Details.ToString.Contains(txt) Select P).FirstOrDefault
Я использую его для ограничения возвращаемых строк, а затем ищу каждую возвращаемую строку
Конец 2013 года - обновления есть?