Combining Linq-to-SQL and Linq-to-XML

Question

Is it possible to combine Linq-to-SQL and Linq-to-XML if a column of a table is XML?

Answer 1

Yes it is certainly possible. You can combine any number of IEnumerable sources in a single query. For example, here is an article showing how you can combine L2S with Linq to Excel:

http://blogs.msdn.com/ericwhite/archive/2008/12/03/joining-linq-to-sql-and-linq-to-excel.aspx

Randy

Answer 2

If by "combine" you mean "have Linq to SQL translate the query into an actual SQL Server XML query" then I believe the answer is no. But there's nothing stopping you from pulling out the XML data and running a local XML query on it.

To answer the more general question of "how do I work with an XML column in this scenario", the best approach would be to create a User-Defined Function to handle the XML, if you can. Then you can get Linq to SQL to execute the UDF.

Failing that, I would go the Stored Procedure route.

Answer 3

Yes it is very possible here is an example from Pro Linq by Joeseph Rattz:

using (NorthwindDataContext DB = new NorthwindDataContext())
        {
            string StatesXml =
                @"<States>
                    <State ID=""OR"" Description=""Oregon"" />
                    <State ID=""WA"" Description=""Washington"" />
                    <State ID=""CA"" Description=""California"" />
                    <State ID=""ID"" Description=""Idaho"" />
                    <State ID=""MT"" Description=""Montana"" />
                    <State ID=""NM"" Description=""New Mexico"" />
                </States>";

            XElement States = XElement.Parse(StatesXml);

            var Custs = (from c in DB.Customers
                         where c.Country == "USA"
                         select c).AsEnumerable().Join(
                         States.Elements("State"),
                         c => c.Region,
                         s => (string)s.Attribute("ID"),
                         (c, s) => new
                         {
                             Customer = c,
                             State = (string)s.Attribute("Description")
                         });

            foreach (var Cust in Custs)
            {
                Console.WriteLine("Customer = {0} : {1} : {2}",
                    Cust.Customer.CompanyName,
                    Cust.Customer.Region,
                    Cust.State);
            }

And here is what that would output:

Great Lakes Food Market : OR : Oregon

Hungry Coyote Import Store : OR : Oregon

Lazy K Kountry Store : WA : Washington

Let's Stop N Shop : CA : California

Lonesome Pine Restaurant : OR : Oregon

Rattlesnake Canyon Grocery : NM : New Mexico

Save-a-lot Markets : ID : Idaho

The Big Cheese : OR : Oregon

The Cracker Box : MT : Montana

Trail's Head Gourmet Provisioners : WA : Washington

White Clover Markets : WA : Washington