Thursday, August 30, 2007
LINQ to SQL Part 2
In LINQ to SQL Part 1 I used a very simple query, almost too simple.  This time I want to take it a step up and add a few joins into the mix, and show how to handle them. Below is a standard TSQL query that will to pull the employee name and his/her territory and region from the Northwind database.  We start all selecting the four columns we want pulled and join several tables together.  Finally, we order the results by lastname and then by firstname. Nothing odd or strange here in TSQL.
 
   1: select e.lastname, e.firstname, t.territoryDescription,r.regiondescription
   2: from employees e
   3: join employeeterritories et on e.employeeid = et.employeeid 
   4: join territories t on t.territoryid = et.territoryid
   5: join region r on r.regionid = t.regionid
   6: order by lastname,firstname
 
In LINQ we once again write our code in somewhat of a reverse order. We start with the from statement and than do our joins statements , followed by the orderby and then select the four columns we want pulled into our resultset. This resultset will be a enumerable anonymous type that can be used with databinding or use with a simple foreach statement.
 
   1: var EmployeeList = from employees in nw.Employees
   2:                    join employeeTerritories in nw.EmployeeTerritories 
   3:                      on employees.EmployeeID equals employeeTerritories.EmployeeID
   4:                    join territories in nw.Territories 
   5:                      on employeeTerritories.TerritoryID equals territories.TerritoryID                               
   6:                    join region in nw.Region 
   7:                      on territories.RegionID equals region.RegionID  
   8:                    orderby employees.LastName,employees.FirstName 
   9:                    select new
  10:                    {
  11:                        employees.LastName,
  12:                        employees.FirstName,
  13:                        territories.TerritoryDescription,
  14:                        region.RegionDescription 
  15:                    };

The following code shows not only the query above, but how simple it is to loop through your resultset and display the information. Remember, when writing your code, you will get the full use of intellisense to help you. 

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Linq;
   4: using System.Text;
   5:  
   6: namespace ConsoleTest001
   7: {
   8:  
   9:     class Program
  10:     {
  11:         static void Main(string[] args)
  12:         {
  13:             Northwind nw = new Northwind(Properties.Settings.Default.NorthwindConnectString);
  14:  
  15:             var EmployeeList = from employees in nw.Employees
  16:                                join employeeTerritories in nw.EmployeeTerritories 
  17:                                  on employees.EmployeeID equals employeeTerritories.EmployeeID
  18:                                join territories in nw.Territories 
  19:                                  on employeeTerritories.TerritoryID equals territories.TerritoryID                               
  20:                                join region in nw.Region 
  21:                                  on territories.RegionID equals region.RegionID  
  22:                                orderby employees.LastName,employees.FirstName 
  23:                                select new
  24:                                {
  25:                                    employees.LastName,
  26:                                    employees.FirstName,
  27:                                    territories.TerritoryDescription,
  28:                                    region.RegionDescription 
  29:                                };
  30:                           
  31:             foreach(var e in EmployeeList)
  32:             {
  33:                 Console.WriteLine("{0,-10} {1,-10}\t{2}\t{3,20}", 
  34:                                     e.LastName, 
  35:                                     e.FirstName,
  36:                                     e.TerritoryDescription.PadRight(20,' ').Substring(0,20),
  37:                                     e.RegionDescription);
  38:             }
  39:  
  40:             Console.ReadLine();
  41:  
  42:         }
  43:     }
  44: }

Here is the result of of the LINQ to SQL query written out to the console.

sshot-2

 

kick it on DotNetKicks.com
 | 
posted on Thursday, August 30, 2007 2:37:15 PM (Eastern Standard Time, UTC-05:00)  #    Comments [2]
Related posts:
Silverlight 3 Controls - Vectorlight
RIA Services May 2009 Preview
VS 2008 & .NET 3.5 SP1 Beta
Free Software for Students
dnrTV Aggregator Part 2 - LINQ to XML
Heroes Happen Here Comic

Page rendered at Monday, February 08, 2010 5:13:02 PM (Eastern Standard Time, UTC-05:00)