This post is the latest in a series of posts on LINQ as it appears in pre-release versions of Visual Studio Orcas. I'm going to show three different LINQ to SQL queries in this post. The first will demonstrate how to create a simple one-to-many, the second how to create a one-to-many that spans three tables, and the third will show how to create a simple join.

LINQ to SQL provides developers with native access to relational databases from inside a CSharp program. By supporting both IntelliSense and type checking LINQ elevates querying to the status of first class citizen in C# programs.

ObjectDumper

The code found in these samples makes use of a utility class called ObjectDumper. This console-based class helps display the often complex relationship between the various rows retrieved from a database. ObjectDumper plays the same role in a console-based application that a grid plays in a GUI application. I will not discuss ObjectDumper further in this post since it does not help us query the data in a database. It's only purpose is to help us display data to the user. The code for the ObjectDumper program is included in the sample source attached to this post.

Relationships in Northwind

Tables in databases are often linked in what are called master-detail or parent-child relationships. There is a one-to-many relationship between records in a master table and records in a detail table. In the Northwind database, for each record in the Customers table, there can be zero or more related rows in the Orders table. The Orders table is therefore designated as a detail, or child table for the Customers table. As shown in Figure 1, the Order_Details table is a child table to both the Products and Orders tables. When looking at Figure 1, follow the arrows to see the relationships between the tables.

Figure 01: Following the arrows, you can see that the Customers table has a detail table called Orders, which in turn has a detail table called Order_Details. The Order_Details table is also the detail table for the Products table.

The link between the Customers table and the Orders table is based on the CustomerId field of each table. If the CustomerId for a particular record in the Customers table is 5, then all the rows in the Orders table that have a CustomerId of 5 will be related to that row in the Customers table. The ProductId field plays the same role in the relationship between the Products table and Order_Details table.

One to Many Relationships with LINQ

The code in Listing 1 shows one way to display the parent-child relationship between the Customers and Orders tables. The first part of the query, shown on lines 3 through 8, asks for the City and CompanyName fields from the records in the Customer table where the Country field is equal to "France." The second part of the query, shown on lines 9 through 10, retrieves the ShipName field from all the records in the Orders table that are related to the selected rows from the Customers table. 

Listing 1: A simple query demonstrating how to compose a master-detail query using LINQ to SQL.

   1:  public void ShowOrders01()
   2:  {
   3:      var query = from c in db.Customers
   4:                  where c.Country == "France"
   5:                  select new
   6:                         {
   7:                             c.City,
   8:                             c.CompanyName,
   9:                             Orders = from o in c.Orders
  10:                                      select new { o.ShipName }
  11:                         };
  12:   
  13:      ObjectDumper.Write(query, 1);
  14:  }

Listing 2 shows a sampling from the output of this query shown in listing 1. The master table is shown in the first line, while the associated records from the detail table are shown in the next four lines. The sixth line is a gain from the master table, and data from the detail table are shown in the next five lines, and so on.

Listing 2: The output from the query shown in listing 1 displays the relationship between the master and detail tables.

City=Nantes     CompanyName=Du monde entier     Orders=...
  Orders: ShipName=Du monde entier
  Orders: ShipName=Du monde entier
  Orders: ShipName=Du monde entier
  Orders: ShipName=Du monde entier
City=Lille      CompanyName=Folies gourmandes   Orders=...
  Orders: ShipName=Folies gourmandes
  Orders: ShipName=Folies gourmandes
  Orders: ShipName=Folies gourmandes
  Orders: ShipName=Folies gourmandes
  Orders: ShipName=Folies gourmandes
City=Nantes     CompanyName=France restauration Orders=...
  Orders: ShipName=France restauration
  Orders: ShipName=France restauration
  Orders: ShipName=France restauration

Three Levels Deep

You are welcome to push this logic a bit further if you wish. Listing 3, for instance, asks first for "the CompanyName from all the rows in the Customers table where the Country is 'USA.'" It then asks "for all the associated records from the Orders table where the UnitPrice, as found in the Order_Details table, is less than $20." Finally, the query asks the computer to "show the City from the Orders table, and the ProductId and UnitPrice from the Order_Details table."

Listing 3: Retrieving data from Customers, Orders, and Orders_Details table with a single query.

   1:  public void ShowOrders02()
   2:  {
   3:      var query = from c in db.Customers
   4:         where c.Country == "USA"
   5:         select new
   6:           {                                   
   7:              c.CompanyName,                                   
   8:              Orders = from o in c.Orders                                            
   9:                 select new
  10:                    {                                                       
  11:                        o.ShipCity,
  12:                        details = from d in o.Order_Details
  13:                           where d.UnitPrice < 20
  14:                           select new
  15:                              {
  16:                                  d.ProductID, d.UnitPrice
  17:                              }
  18:                    }
  19:           };
  20:   
  21:      ObjectDumper.Write(query, 2);
  22:  }

The code that pulls data from the Customers table is found in lines 3 through 7. Data from Orders table is pulled in lines 8 through 11. Finally, the details table is queried in lines 12 through 17.

A few rows retrieved from the query in Listing 3 are shown in Listing 4. Notice that the data shown here goes three levels deep. There is data displayed from the Customers table, the Orders table, and the Order_Details table.

Listing 4: Here is a query that is three levels deep. The CompanyName is pulled form the Customers table, the ShipCity from Orders table, and the UnitPrice from the Order_Details table.

CompanyName=The Big Cheese      Orders=...
  Orders: ShipCity=Portland       details=...
    details: ProductID=16   UnitPrice=13.9000
  Orders: ShipCity=Portland       details=...
    details: ProductID=36   UnitPrice=19.0000
  Orders: ShipCity=Portland       details=...
    details: ProductID=34   UnitPrice=14.0000
  Orders: ShipCity=Portland       details=...
CompanyName=The Cracker Box     Orders=...
  Orders: ShipCity=Butte  details=...
    details: ProductID=44   UnitPrice=19.4500
  Orders: ShipCity=Butte  details=...
    details: ProductID=67   UnitPrice=14.0000
  Orders: ShipCity=Butte  details=...
    details: ProductID=1    UnitPrice=18.0000
    details: ProductID=40   UnitPrice=18.4000
    details: ProductID=52   UnitPrice=7.0000
CompanyName=Trail's Head Gourmet Provisioners   Orders=...
  Orders: ShipCity=Kirkland       details=...
    details: ProductID=33   UnitPrice=2.5000
    details: ProductID=40   UnitPrice=18.4000
  Orders: ShipCity=Kirkland       details=...
    details: ProductID=39   UnitPrice=18.0000
    details: ProductID=75   UnitPrice=7.7500
    details: ProductID=77   UnitPrice=13.0000
  Orders: ShipCity=Kirkland       details=...
    details: ProductID=70   UnitPrice=15.0000

Joins with LINQ

Joins take the data from two tables and binds them into a single table. For instance, the query shown in Listing 5 draws data from Products table and the Order_Details table. These two tables are related in a parent-child relationship. But here they are not shown in a one-to-many relationship like those found in the previous examples. Instead, the two tables are joined together into one new table that has selected fields from both tables. In particular, the new table has the UnitPrice field from the Order_Details table and the ProductName field from the Products table. The query itself makes the following request: "Show me the UnitPrice and the ProductName from the records in the Order_Details and Products table where the ProductId of each table is equal to 3." Or, to put the question in more practical terms, "Look across all the orders and show me the different prices that we charge for Aniseed Syrup."

 

   1:  public void ShowProduct01()
   2:  {
   3:      var query = from details in db.Order_Details
   4:                  where details.ProductID == 3
   5:                  join products in db.Products on 
   6:                    details.ProductID equals products.ProductID 
   7:                  select new {details.UnitPrice, products.ProductName};
   8:   
   9:      ObjectDumper.Write(query);
  10:  }

The syntax for joins in LINQ to SQL look very much like the familiar syntax for joins found in SQL code. Notice in particular lines 5 and 6, which form the heart of the query. In line 6, for instance, we can see that the join is formed on the ProductId field shared by each table.

Listing 6: The Output from the query shown in Listing 5.

UnitPrice=8.0000        ProductName=Aniseed Syrup
UnitPrice=8.0000        ProductName=Aniseed Syrup
UnitPrice=8.0000        ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup
UnitPrice=10.0000       ProductName=Aniseed Syrup

When looking at this query, it is important to understand that line 4 is optional. I include it simply so as to limit the amount of output produced by the query. If that line were omitted then we would see the prices charged across all the different orders for all the different products.

Summary

Joins and One to Many relationships are two of the most important types of queries found in relational database programming. In this post you have seen how to write both types of queries using LINQ to SQL. You have seen that the syntax for writing these queries is very similar to the syntax found in traditional SQL queries. The difference, of course, is that the code you write using LINQ is fully type checked C# code and it fully supports the powerful IntelliSense features found in the Visual Studio IDE. For more details on LINQ, see the other posts in this series.

kick it on DotNetKicks.com