15 June 2011

Mixing Linq-to-SQL and Linq-to-XMl

I have been trying to select a column containing XML text from a database and then selecting nodes from the XML using Linq. It didn't work. My guess is that deferred execution causes the queries to be mixed and taken as Linq-to-SQL. As a result the XML stuff causes problems.

My solution was to force an intermediate result so that there is a break between SQL and XML queries. My code for LinqPad is as follows.

var all = from bm in mytable
          select new { name=bm.SPP_USER_ID, xml=(string)bm.SPP_BOOKMARKS_XML};

var docs = from x in all
           select XDocument.Parse(x.xml);
var docs2 = docs.ToList(); // FORCE RESULT

XNamespace ns = "http://acme/bookmarks";
var href = from h in docs2.Descendants(ns + "href")
           select h.Value;