Wriju's BLOG

.NET, Cloud and everything

LINQ to XML : Creating complex XML through LINQ

We can generate hierarchical object graph in our memory though LINQ. To be more realistic we can bring data from relational database. So if we consider Northwind database and use LINQ to SQL to bring all the Customers and their Orders and Order Details the query would look like,


 


 


//LINQ to SQL way to get data from database


var q = from c in db.Customers


        select new


        {


            CId = c.CustomerID,


            Orders = from o in c.Orders


                     select new


                     {


                         OID = o.OrderID,


                         Qty = from od in o.Order_Details


                               select new { Qty = od.Quantity }


                     }


        };


 


 


 


So what I am trying to do here is that, I am trying to fetch CustomerId from Customers table and OrderId from Orders table and Quantity from Order Details table. It is bringing 3 level deep data for me and storing it to memory.


 


By using XElement and XAttribute I will create a single XML stream. Which will look like,


 


<?xml version=1.0 encoding=utf-8?>


<customers>


  <customer id=ALFKI country=Germany contactName=Maria Anders contactTitle=Sales Representative>


    <Orders id=10643 date=1997-08-25T00:00:00>


      <items>


        <item price=45.6000 quantity=15 />


        <item price=18.0000 quantity=21 />


        <item price=12.0000 quantity=2 />


      </items>


    </Orders>


    <Orders id=10692 date=1997-10-03T00:00:00>


      <items>


        <item price=43.9000 quantity=20 />


      </items>


    </Orders>


 


…….


 


To achieve this I have to write a very simple query like syntax based on the query I have written earlier,


 


var query = new XElement(“customers”,


           from c in db.Customers                       


           select


               new XElement(“customer”,


                   new XAttribute(“id”, c.CustomerID),


                   new XAttribute(“country”, c.Country),


                   new XAttribute(“contactName”, c.ContactName),


                   new XAttribute(“contactTitle”, c.ContactTitle),


 


                   from o in c.Orders


                   select new XElement(“Orders”,


                       new XAttribute(“id”, o.OrderID),


                       new XAttribute(“date”, o.OrderDate),


                            new XElement(“items”,


 


                       from od in o.Order_Details


                       select new XElement(“item”,


                               new XAttribute(“price”, od.UnitPrice),


                               new XAttribute(“quantity”, od.Quantity))))));


 


 


It looks complex because it is one liner but actually it is very simple. This will give you the exact XML output mentioned earlier.


 


Namoskar!!!