Funny.Bag (1) [Avatar] Offline
I have a SQL Server 2005 table that has and XML datatype column and I was wondering if, since SQL Server 2005 natively supports XPath, it's possible to query the XML from within a LINQ to SQL query.

What I'm trying to do is something like this:

var query = from contact in db.Contacts
where contact.AdditionalInfo.HasElements
select contact;

"AdditionalInfo" is an XML column that is correctly mapped to an XElement by the Visual Studio 2008 designer, but unfortunately if I try to run this i get the following exception:

The member 'System.Xml.Linq.XElement.HasElements' has no supported translation to SQL

Any help would be appreciated.

- Mike

Great book btw!
fabrice.marguerie (224) [Avatar] Offline
Re: Nesting a LINQ to XML query in a LINQ to SQL query
Hi Gianni,

I'm glad that you enjoy the book! Feel free to spread the word smilie

To the best of my knowledge, LINQ to SQL has no support for XML fields and XPath in its first version. The best you can do is to map XML columns as strings and query them in memory using LINQ to XML, and not in SQL Server.

See what Scott Guthrie writes in a comment here: XML datatypes in SQL are represented as strings with LINQ to SQL Entities. You could use XLINQ to query on an XML column within your LINQ to SQL entitiy - but this querying would happen in your middle-tier...