Can LINQ to SQL query an XML field DB-serverside?

Question

.NET 3.5, C#

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.

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.

I was reading Rob Conery's excellent article (http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/) 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.

For example (contrived):

Author: ID (int), LastName (varchar(32)), FirstName (varchar(32))

context.Author.Where(xx => xx.LastName == "Smith").Where(xx => xx.FirstName == "John")

Results in the following query:

SELECT [t0].[ID], [t0].[LastName], [t0].[FirstName]
FROM [dbo].[Author] AS [t0]
WHERE ([t0].[LastName] = Smith) AND ([t0].[FirstName] = John)

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.

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

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 is 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.

Ideas? Suggestions?

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

Answer 1

Now that is an interesting question.

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.

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.

Hope this helps.

Answer 2

To clarify Daniel's answer - you cannot use a function to do this unless the XPath part of the query is fixed. See my blog entry on this: http://quangouk.spaces.live.com/blog/cns!AC44601E5195662B!160.entry

Basically you cannot query an xml column via LINQ to SQL. Although it returns an XElement type you cannot do any SQL translations when trying to filter on this column.

LINQ to SQL does support using UDFs - but SQL itself will not allow you to use a parameter string in a XML xpath query - it has to be a string literal. That means it can work if the XPath is fixed at design time, but not if you wanted to be able to pass a variable XPath statement.

This leads to only two other alternative ways of doing it: inline SQL statement (which negates the value of having LINQ) and writing a SQL Library function in .NET CLR to do this.

Answer 3

You can use a variable in parts of an XQuery. I use something like this:

DIM @myParameter NVARCHAR(50) SELECT @myParameter = 'Some Value'

SELECT XmlColumn.query('for $step in /element1/element2[@attribName = sql:variable("@myParameter")] return string($step)') FROM SomeTable