LINQ to SQL: Working with hierarchical data

LINQ to SQL: Working with hierarchical data

  • Comments 7

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,

 

clip_image001

 

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

Leave a Comment
  • Please add 2 and 4 and type the answer here:
  • Post
  • LINQ to SQL supports hierarchical data and you can easily create a query and get output from there. Let

  • Welcome to the thirty-sixth issue of Community Convergence. This is the big day, with Visual Studio 2008

  • Where is the hierarchy? I was expecting "real" hierachial data having unfixed depth (e.g. Parent-Child Relationships or more complicated)?

  • 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

  • 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

  • Last week I have trying to load hierarchical data from the same table (Parent/Child). Although I have

  • Thank you very much. This LINQ is also way how to make left jon on some data

Page 1 of 1 (7 items)