CODEWITHSUNDEEP
c#xmllinq-to-sql
Niels Bosma
Niels Bosma

Reputation: 11498

Xml query in LinqToSql

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

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

... Seems this queries the db for XmlObject but process Element("Phone") part outside the database? How do I create the query so that native SQL XML functions are used? (Do I want that?)

Updated: Would using a sproc be faster that the alternative used above?

Upvotes: 2

Views: 144

Answers (2)

Quango
Quango

Reputation: 13458

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

However I now have a solution I have posted on my blog: write a SQLCLR function on the server, and expose these to LINQ to SQL as a scalar function - these can be called as part of a LINQ query and will execute 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 has been added to the dbml.

Upvotes: 1

Andrew Hare
Andrew Hare

Reputation: 351516

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

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

Upvotes: 2

Related Questions