LINQ to SQL: Working with hierarchical data

LINQ to SQL supports hierarchical data and you can easily create a query and get output from there. Let us take an example of Northwind database. Northwind has Category -> Products -> Order_Details tables. -> indicates one to many relationships here. So ideally for a Category can have multiple products and a product can have multiple Orders (in Order_Details). Now to get the number of Orders given for each Products under a category of id with their total price we have to write sub queries and SQL will become complex.

But if we use LINQ to SQL and drag and drop all the three tables there. The designer will look like,

Now to get the required output we can write a simple LINQ statement,

NorthwindDataContext db = new NorthwindDataContext();

var query =

from p in db.Products

where p.CategoryID == 1

select new

{

p.ProductID,

p.ProductName, 

p.Category.CategoryName,

     NumOrders = p.OrderDetails.Count,

     Revenue = p.OrderDetails.Sum(o=>o.UnitPrice * o.Quantity)

};

TextBox1.Text = db.GetCommand(query).CommandText;

Namoskar!!!