.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

  • As mobile devices have pushed their way into the enterprise, they have brought cloud apps along with them. This app explosion means account passwords are multiplying, which exposes corporate data and leads to help desk calls from frustrated users. This paper will discover how IT can improve user productivity, gain visibility and control over SaaS and mobile apps, and stop password sprawl. Download this white paper to learn: How you can leverage your existing AD to manage app access. Key capabilities to …

  • Targeted attacks and advanced threats are customized to infiltrate your unique IT infrastructure, evade conventional defenses, and remain hidden while stealing your corporate data. To detect these criminal intrusions, analysts and security experts agree that organizations should deploy advanced threat protection as part of an expanded security monitoring strategy. For this comparative analysis of breach detection systems, product analysis reports and comparative analysis reports are used to create the security …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds