Xml Query in LinqToSql

I have an XML column in a database and I would like to query this XML using Linq (toSQL) in an efficient way.

MyTable.Select(e => e.XmlObject.Element("Phone").Value)


... Seems to be db requests for XmlObject, but handling Element outside of the database? How do I create a query to use native SQL XML functions? (I want this?)

Updated: Will using sproc be faster than the alternative used above?


source to share

2 answers

I had the same problem with my XML column data - the only solution I had at the time was to get the entire XML column, convert to a string, and do a string search on it. Very kludgy.

However, I now have a solution that I posted on my blog : write an SQLCLR function on the server and expose them in LINQ to SQL as a scalar function - they can be called as part of a LINQ query and will be executed on the server.

For example:

var db = new MyDataContext();
var query = from x in db.MyTable 
            where db.XmlGetElementValue(x.XmlColName, "ElementX") == "somevalue" 
            select x;


Where XmlGetElementValue is added to dbml.



The LINQ to SQL provider does not support translating C # expressions to SQL Server XQuery.

You will need to use plain old ADO.NET with a string query (either in a stored procedure or in a parameterized command text query in code) to achieve the results you want.



All Articles