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!!!