Mike
Tips, tricks and solutions for software developers.
 

Getting hierarchical (tree-like) data from LINQ

By Mike Gledhill

In this walkthrough, we're going to use LINQ to retrieve data from four Northwind tables in an XML hierarchical tree. I've used this trick numerous times, and it lets me grab related data, from several tables, using just one call to the Data Layer.

In the Northwind database, there is an Orders table, each linked to a particular Customer, but which could contain zero or more Products.

Northwind tables

Take a deep breath...

private XElement GetOrdersForCustomer(string CUSTOMER_ID)
{
  NorthwindDataContext dc = new NorthwindDataContext();

  XElement xml = new XElement("Orders",
    from ord in dc.Orders
    where ord.CustomerID == CUSTOMER_ID
    join cust in dc.Customers
    on ord.CustomerID equals cust.CustomerID
    select new XElement("Order",
      new XAttribute("OrderID", ord.OrderID),
      new XAttribute("OrderDate", ord.OrderDate),
      new XAttribute("CustomerID", cust.CustomerID),
      new XAttribute("CustomerName", cust.CompanyName),
      new XAttribute("ProductCount", (from ods
                      in dc.Order_Details
                      where ods.OrderID == ord.OrderID
                      select ods).Count()),
      from od in dc.Order_Details
      where od.OrderID == ord.OrderID
      join prod in dc.Products
      on od.ProductID equals prod.ProductID

      select new XElement("Product",
        new XAttribute("ProductName", prod.ProductName),
        new XAttribute("Qty", od.Quantity)
      )
    )
  );
  return xml;
}

This functions returns one hierarchical XElement element, containing all of the data we need.

Notice how I've even asked the LINQ to slip in a "SELECT COUNT(*)" value (for the ProductCount attribute) in with the results... all from the same one LINQ statement.

Northwind tables

I find this method much cleaner than performing separate calls to the Data Layer to:

  • load a list of all [Order] records for customer ALFKI
  • load details about the [Product] records in the first [Order]
  • load details about the [Product] records in the second [Order]
  • load details about the [Product] records in the third [Order]
  • ...etc..


 

Comments