Our development stumbled onto 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 leverage LINQ to SQL, we created a UDF in SQL and added that to our DataContext and call that (loosely based on LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures) ). However, when we use these functions, the XML Indexing by SQL is rendered useless. A filtered and ordered query against a population of 14,000 rows and it times out with the default 30 second CommandTimeout. But if we take the command text (sniffed by SQL Profiler) and swap out the UDF with direct XQuery, the query takes less than a second (obviously what we want). I've seen similar questions about Xml Fields (i.e. this question) but the common answer is to use UDFs - but we've proven those are inefficient if used extensively.
We are hoping there is some low level point where we can grab the command text and swap out the UDF with proper XQuery syntax (via a RegEx). Not ideal, but it's the only solution we can see feasible. We are open to anything - command text translation, CLR integration with SQL, etc.
In certain situations, we could do this already. For instance if we always have an 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 aren't seeing an place to hook into.
Any suggestions are greatly appreciated.
FYI, my coworker has 'solved' the problem. A bit 'evil' but doing the job for us. http://chriscavanagh.wordpress.com/2011/03/12/manipulating-linq-to-sql-command-text/
So basically, now that we can modify, we created some 'placeholder' functions in our L2S DataContext that are obviously UDFs. But we intercept command, and swap them out for proper XQuery value/exists() syntax to make sure it is as performant as possible.