Cast xml to string in LINQ query

Question

Is there a way to cast an xml to a string in a Linq query. Something like this, but in LINQ:

select TOP(10) * from PackageSessionNodes
where CAST(Interactions as nvarchar(max)) like '%asset id%'
order by PackageSessionNodeId desc

This doesn't work:

var packageSessionNodes = from psn in db.PackageSessionNodes
                          where psn.Interactions.Contains("asset")
                          select psn;

Interactions is a XML Column in SQL2008 Server.

Answer 1

Wait, you mean like:

var packageSessionNodes = from psn in db.PackageSessionNodes
                          where psn.Interactions.Contains("asset")
                          select psn.ToString();

XML in Linq is represented by the XNode family of types. XNode.ToString() returns the xml representation of the node and its children.

Or, do you mean

var packageSessionNodes = from psn in db.PackageSessionNodes
                          where ((string)psn.Interactions).Contains("asset")
                          select psn;

assuming that psn is a type that has a public property Interactions which is an XElement. Of course, if it is, you should be parsing the XML instead of seeing if the xml text contains a specific string.

Answer 2

Have you tried:

var packageSessionNodes = from psn in db.PackageSessionNodes
                          where psn.Interactions.ToString().Contains("asset")
                          select psn

Depending on what Data Type SqlMetal decided to use for your XML Column (typically either XNode or XElement I believe), ToString() will return the XML as a String which should work for you.