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



Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • On-demand Event Event Date: September 10, 2014 Modern mobile applications connect systems-of-engagement (mobile apps) with systems-of-record (traditional IT) to deliver new and innovative business value. But the lifecycle for development of mobile apps is also new and different. Emerging trends in mobile development call for faster delivery of incremental features, coupled with feedback from the users of the app "in the wild." This loop of continuous delivery and continuous feedback is how the best mobile …

  • Java developers know that testing code changes can be a huge pain, and waiting for an application to redeploy after a code fix can take an eternity. Wouldn't it be great if you could see your code changes immediately, fine-tune, debug, explore and deploy code without waiting for ages? In this white paper, find out how that's possible with a Java plugin that drastically changes the way you develop, test and run Java applications. Discover the advantages of this plugin, and the changes you can expect to see …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds