LINQ to SQL 可以查询 XML 字段 DB-serverside 吗? [英] Can LINQ to SQL query an XML field DB-serverside?

问题描述

.NET 3.5，C#

.NET 3.5, C#

我有一个带有搜索"功能的网络应用.一些可搜索的字段是表中的第一类列，但其中一些实际上是 XML 数据类型中的嵌套字段.

I have a web app with a "search" feature. Some of the fields that are searchable are first-class columns in the table, but some of them are in fact nested fields inside an XML data type.

以前，我构建了一个系统来动态构建 SQL 以进行搜索.我有一个很好的类层次结构，可以构建 SQL 表达式和条件语句.唯一的问题是它对 SQL 注入攻击不安全.

Previously, I built a system for dynamically constructing the SQL for my search. I had a nice class hierarchy that built SQL expressions and conditional statements. The only problem was it was not safe from SQL injection attacks.

我正在阅读 RobConery 的优秀文章 指出，如果从未枚举 IQueryable 结果，则可以将多个查询组合为服务器的单个 TSQL 查询.这让我觉得我的动态搜索构造太复杂了 - 我只需要组合多个 LINQ 表达式.

I was reading Rob Conery's excellent article which pointed out that multiple queries can combined into a single TSQL query for the server if the IQueryable result is never enumerated. This got me to thinking that my dynamic search construction was much too complicated - I just needed to combine multiple LINQ expressions.

例如(人为的):

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 结果并执行额外的条件表达式以获得我的最终单执行表达式.

I realized this might be the perfect solution for a simple dynamic query generation that's safe from SQL injection - I'd just loop over my IQueryable result and execute additional conditionals expressions to get my final single-execution expression.

但是，我找不到任何对 XML 数据评估的支持.在 TSQL 中，要从 XML 节点获取值，我们将执行类似

However, I can't find any support for evaluation of XML data. In TSQL, to get a value from an XML node, we would do something like

XMLField.value('(*:Root/*:CreatedAt)[1]', 'datetime') = getdate() 

但我找不到与创建此评估等效的 LINQ to SQL.一个存在吗?我知道我可以评估所有非 XML 条件 DB 端，然后执行我的 XML 评估代码端，但我的数据足够大 A) 大量网络流量会拖累性能 B) 我会退出-如果我无法评估 XML First DB 端以排除某些结果集，则会出现内存异常.

But I can't find the LINQ to SQL equivalent of creating this evaluation. Does one exist? I know I can evaluate all non-XML conditions DB side, and then do my XML evaluations code side, but my data are large enough that A) that's a lot of network traffic to drag on performance and B) I'll get out-of-memory exceptions if I can't evaluate the XML first DB side to exclude certain result sets.

想法?建议?

额外问题 - 如果 XML 评估实际上是可能的 DB 端，那么 FLWOR 支持怎么样?

Bonus question - If XML evaluation is in fact possible DB side, what about FLWOR support?

推荐答案

现在这是一个有趣的问题.

Now that is an interesting question.

目前，您无法直接从 Linq 指示 SQL Server 执行 XML 函数.但是，您可以让 Linq 使用用户定义的函数...因此，您可以设置一个 udf 来处理 xml、获取正确的数据等，然后在您的 Linq 表达式中使用它.这将在服务器上执行，应该做你想做的.但是，有一个重要的限制:您要查找的 XML 路径(xmlColumn.value 的第一个参数或类似参数)必须内置到函数中，因为它必须是一个字符串 文字，它不能从输入参数(例如)构建.因此，您可以使用 UDF 来获取您在编写 UDF 时了解的字段，但不能作为从 XML 列获取数据的通用方法.

Right now, you cannot instruct SQL Server to perform XML functions directly from Linq. However, you can get Linq to use user defined functions... so, you could setup a udf to process the xml, get the right data, etc, and then use that in your Linq expresion. This will execute on the server and should do what you want. There's an important limitation, though: The XML path you're looking for (the first parameter to xmlColumn.value or similar) has to be built into the function because it has to be a string literal, it can't be built from an input parameter (for instance). So you can use UDFs for getting fields you know about when writing the UDF, but not as a general-purpose way to get data from XML columns.

查看Scott Gutherie 关于 Linq to SQL 的优秀博客系列，了解更多实现信息.

Check out the Supporting User Defined Functions (UDFs) section of Scott Gutherie's excellent Blog series on Linq to SQL for more info on implementation.

希望这会有所帮助.

这篇关于LINQ to SQL 可以查询 XML 字段 DB-serverside 吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！