通过

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,

 

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

Comments

  • Anonymous
    November 09, 2007
    LINQ to SQL supports hierarchical data and you can easily create a query and get output from there. Let

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

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

  • Anonymous
    May 30, 2008
    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

  • Anonymous
    June 05, 2008
    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

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

  • Anonymous
    July 06, 2009
    Thank you very much. This LINQ is also way how to make left jon on some data