Clever Geek Handbook

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?

+2


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.

+1


source


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.

+2


source






More articles:


All Articles