LINQ to SQL - problem with the Xml field - how low can you intercept the generated TSQL?

Question

Our development has come across a major roadblock in terms of Linq to Sql and Sql 2005+ Xml Fields. We have an xml blob field containing fields ...

<Profile name-first="Terry" name-last="Aney" [...more]/> 

To use LINQ to SQL, we created UDF in SQL and added it to our DataContext and named it (based on LINQ to SQL (Part 6 - Retrieving data using stored procedures) ). However, when we use these functions, indexing XML by SQL seems futile. Filtered and ordered query with a population of 14,000 rows, and it expires with a 30 second CommandTimeout by default. But if we take the command text (sniffed by SQL Profiler) and replace the UDF with direct XQuery, the query will take less than a second (obviously what we want). I saw similar questions about Xml Fields (i.e. this question ), but the general answer is to use UDF, but we have proven that they are ineffective if you use them extensively.

We hope that there is some low-level point where we can capture command text and change the UDF to the appropriate XQuery syntax (via RegEx). Not perfect, but this is the only solution that we see practically possible. We are open to anything - command translation of text, CLR integration with SQL, etc.

In certain situations, we could do this already. For example, if we always have IQueryable, where T is not an anonymous and / or complex / nested type, we could call GetCommandText and then call DataContext.Translate (). However, for anonymous / complex types and / or scalar queries, we do not see a place to connect.

Any suggestions are welcome.

Answer 1

FYI, my colleague "solved" the problem. A bit "evil", but doing this work for us. http://chriscavanagh.wordpress.com/2011/03/12/manipulating-linq-to-sql-command-text/

So, basically, now that we can change, we have created some “filling” functions in our L2S DataContext, which are obviously UDF. But we intercept the command and swap them for the correct XQuery value / exists () syntax to make sure it is as efficient as possible.

Answer 2

So, here are a few thoughts, the function in this will be called for each of the returned rows, which will lead to its performance degradation, but, apparently, Linq to SQL is not a big fan of XML fields, as you probably noticed. So a possible solution is to try to really oppose the function, and the best way to do it is with SQLCLR, so take a look at this link and see how it did it.

http://conficient.wordpress.com/2011/01/20/querying-xml-fields-in-linq-to-sql/