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.
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.
I find this method much cleaner than performing separate calls to the Data Layer to:
Comments