StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCan LINQ to SQL query an XML field DB-serverside?
text
Body
copied!<p>.NET 3.5, C#</p> <p>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. </p> <p>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.</p> <p>I was reading <a href="http://blog.wekeroad.com/2008/02/27/creating-in-queries-with-linq-to-sql/" rel="nofollow noreferrer">Rob Conery's excellent article</a> 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.</p> <p>For example (contrived):</p> <pre><code>Author: ID (int), LastName (varchar(32)), FirstName (varchar(32)) context.Author.Where(xx => xx.LastName == "Smith").Where(xx => xx.FirstName == "John") </code></pre> <p>Results in the following query:</p> <pre><code>SELECT [t0].[ID], [t0].[LastName], [t0].[FirstName] FROM [dbo].[Author] AS [t0] WHERE ([t0].[LastName] = Smith) AND ([t0].[FirstName] = John) </code></pre> <p>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.</p> <p>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</p> <pre><code>XMLField.value('(*:Root/*:CreatedAt)[1]', 'datetime') = getdate() </code></pre> <p>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.</p> <p>Ideas? Suggestions? </p> <p>Bonus question - If XML evaluation is in fact possible DB side, what about FLWOR support?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload