.NET Tip: Following Table Relationships with LINQ

LINQ has very robust support for taking advantage of the relationships that are defined in your database. If you like, you can continue to use the same type of SQL syntax that you are used to to join tables based on keys. I much prefer, however, to take advantage of LINQ’s from clause to traverse relationships. To take advantage of this feature, you need to have your primary key/foreign key relationships defined in your database. The first step is to create a DataContext to query against. This example uses the AdventureWorks database for SQL Server. I created the DataContext by adding a new “LINQ to SQL Classes” item to the project and dragging a few tables from my SQL Server instance onto the designer for the DataContext. Figure 1 shows the DataContext designer with the Customer, SalesOrderHeader, and SalesOrderDetail tables included.

Figure 1: Context in the designer

Now that you can see what data is available, you can get down to the LINQ query to get at the data. This example will use the AccountNumber from the Customer table, the SalesOrderNumber and OrderDate from the SalesOrderHeaders table, and the OrderQty, UnitPrice, and LineTotal fields from the SalesOrderDetail table. With the relationships using CustomerID and SalesOrderID already in place, LINQ provides access to tables related to the primary table in your query. First, take a look at the LINQ query to retrieve the desired results.

CSTipsDataContext CSTips = new CSTipsDataContext();
var q = from c in CSTips.Customers
   from soh in c.SalesOrderHeaders
   from sod in soh.SalesOrderDetails
   select new
   {
      c.AccountNumber,
      soh.SalesOrderNumber,
      soh.OrderDate,
      sod.OrderQty,
      sod.UnitPrice,
      sod.LineTotal
   };

gvLINQ.DataSource = q;

The first line creates an instance of the DataContext for the LINQ query. The interesting part of the query is the use of multiple from clauses to get at the desired data. The first from clause provides access to all of the fields in the Customer table. In addition to the fields defined directly in the Customer table, each Customer object has a SalesOrderHeaders object that contains all of the SalesOrderHeader records for each Customer. This is made possible by having the relationship between Customer and SalesOrderHeader defined in the DataContext. Likewise, each SalesOrderHeader object has SalesOrderDetail records associated with it. Using multiple from clauses allows the sources of the desired data to be neatly defined at the beginning of the query. The select new portion of the query projects the result using only the fields of interest from the three tables. The final line of the example sets the DataSource of a GridView to the result of the LINQ query. Figure 2 shows the GridView displaying the results of the query.

Figure 2: GridView display results of the LINQ query

This is just a taste of how LINQ can be used to easily traverse the relationships in your database. Dig deeper yourself into what LINQ has to offer and how you can best take advantage of it in your applications.

About the Author

Jay Miller is a Software Engineer with Electronic Tracking Systems, a company dedicated to robbery prevention, apprehension, and recovery based in Carrollton, Texas. Jay has been working with .NET since the release of the first beta and is co-author of Learn Microsoft Visual Basic.Net In a Weekend. Jay can be reached via email at jmiller@sm-ets.com.

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read