4.3. Lesson 2: Executing Queries Using LINQ to SQL

In the previous lesson, you were introduced to the LINQ to SQL designer, the DataContext class, and an example entity class. In addition, a couple of LINQ to SQL queries were presented to demonstrate the operation of the DataContext class and lazy loading. You also saw how the LINQ to SQL provider queries only for the required data when where clauses are provided.

This lesson examines some of the more common types of LINQ to SQL queries you might perform.

After this lesson, you will be able to: Perform LINQ to SQL queries with filtering and sorting. Write LINQ to SQL statements that implement projections. Perform inner joins with LINQ to SQL. Perform outer joins with LINQ to SQL. Create and execute grouping and aggregation with LINQ to SQL. Estimated lesson time: 45 minutes

4.3.1. Basic Query with Filter and Sort

Basic queries using the LINQ to SQL classes are very clean and readable. In addition, remember that LINQ to SQL classes retrieve only the data you request. The following code sample queries for a list of customers that contain the word “Restaurant” in the company name, sorted on postal code.

Sample of Visual Basic Code

Private Sub mnuBasicQuery_Click(ByVal sender As System.Object, _
    ByVal e As System.Windows.RoutedEventArgs)       
    Dim ctx = New NorthwindDataContext()       
    Dim sw = New StringWriter()       
    ctx.Log = sw
    Dim customers = From c In ctx.Customers                       
                    Where c.CompanyName.Contains("Restaurant")                       
                    Order By c.PostalCode
                    Select c       
    dg.ItemsSource = customers
    MessageBox.Show(sw.GetStringBuilder().ToString())    
End Sub 

Sample of C# Code

private void mnuBasicQuery_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = from c in ctx.Customers
                   where c.CompanyName.Contains("Restaurant")
                   orderby c.PostalCode
                   select c;
   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle],
[t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], [t0].
[Phone], [t0].[Fax]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[PostalCode]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%Restaurant%]

					  

The LINQ to SQL query sends a query to SQL Server that includes a where clause and an order by clause. Looking at the sample code, this looks like a very basic LINQ query, using the Customers property on the NorthwindDataContext object. The key here is that the LINQ to SQL provider is capable of constructing an efficient query to send to SQL Server.

4.3.2. Projections

One of the potential problems with the previous query is that all the column values from the Customers table are returned, but you might have needed to retrieve only CustomerID, CompanyName, and PostalCode. You can use a projection to limit the column values returned from SQL Server. The following code example demonstrates the use of projections to limit the returned column values from the Customers table.

Sample of Visual Basic Code

Private Sub mnuProjection_Click(ByVal sender As System.Object, _
                             ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim customers = From c In ctx.Customers
                   Where c.CompanyName.Contains("Restaurant")
                   Order By c.PostalCode
                   Select New With {c.CustomerID, c.CompanyName, c.PostalCode}
   dg.ItemsSource = customers
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuProjection_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = from c in ctx.Customers
                   where c.CompanyName.Contains("Restaurant")
                   orderby c.PostalCode
                   select new
                             {
                                c.CustomerID,
                                c.CompanyName,
                                c.PostalCode
                             };
   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[PostalCode]
FROM [dbo].[Customers] AS [t0]
WHERE [t0].[CompanyName] LIKE @p0
ORDER BY [t0].[PostalCode]
-- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [%Restaurant%]

					  

This code instantiates an anonymous type that filters out columns. Chapter 3, covers anonymous types and projections in more detail.

4.3.3. Inner Joins

An inner join produces output only when the two tables you are joining match on the unique key to foreign key. Inner joins can be implemented easily with LINQ to SQL by using the standard LINQ query syntax. The following LINQ query produces an inner join of the Customers table to the Orders table and retrieves CustomerID, CompanyName, OrderID, and OrderDate by using query extension methods.

Sample of Visual Basic Code

Private Sub mnuInnerJoin1_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim customers = ctx.Customers.Join(ctx.Orders,
                                      Function(c) c.CustomerID,
                                      Function(o) o.CustomerID,
                                      Function(c, o) New With
                                                    {
                                                       c.CustomerID,
                                                       c.CompanyName,
                                                       o.OrderID,
                                                       o.OrderDate
                                                      }) _
                                 .OrderBy(Function(r) r.CustomerID) _
                                 .ThenBy(Function(r) r.OrderID)
   dg.ItemsSource = customers
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuInnerJoin1_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = ctx.Customers.Join(
      ctx.Orders,
      c => c.CustomerID,
      o => o.CustomerID,
      (c, o) => new
                   {
                      c.CustomerID,
                      c.CompanyName,
                      o.OrderID,
                      o.OrderDate
                   })
          .OrderBy(r=>r.CustomerID)
          .ThenBy((r=>r.OrderID));
   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

					  

Using query extension methods to perform the join produced a nice, clean SQL query. Could this query be written as a LINQ query? It can, and the following code sample produces the same result.

Sample of Visual Basic Code

Private Sub mnuInnerJoin2_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim customers = From c In ctx.Customers
                   Join o In ctx.Orders
                   On c.CustomerID Equals o.CustomerID
                   Order By c.CustomerID, o.OrderID
                   Select New With
                          {
                             c.CustomerID,
                             c.CompanyName,
                             o.OrderID,
                             o.OrderDate
                          }
   dg.ItemsSource = customers
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuInnerJoin_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = from c in ctx.Customers
                   join o in ctx.Orders
                   on c.CustomerID equals o.CustomerID
                   orderby  c.CustomerID, o.OrderID
                   select new
                   {
                      c.CustomerID,
                      c.CompanyName,
                      o.OrderID,
                      o.OrderDate
                   };
   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t0].[CustomerID], [t0].[CompanyName], [t1].[OrderID], [t1].[OrderDate]
FROM [dbo].[Customers] AS [t0]
INNER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

					  

This is a clean-looking LINQ query, and it produced a nice, efficient SQL query. If you look through the results carefully, you might find that there are two customers who have not placed any orders. How would you know that? These two customers are missing from the output because they don’t match up to any orders. The missing customer IDs are FISSA and PARIS. To see all customers, you need to write an outer join.

4.3.4. Outer Joins

An outer join produces output of the outer table, even if the outer table element doesn’t match the inner table. To perform an outer join, you must provide code to indicate that you still want the outer table row, even if there is no match to the inner table. You can perform outer joins by using the GroupJoin extension method, as shown in the following sample code:

Sample of Visual Basic Code

Private Sub mnuOuterJoin1_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim customers = ctx.Customers.GroupJoin(ctx.Orders, _
            Function(c) c.CustomerID, _
            Function(o) o.CustomerID, _
            Function(c, o) New With
                           {
                              c.CustomerID,
                              c.CompanyName,
                              .Orders = o
                           }) _
         .SelectMany(Function(t) t.Orders.DefaultIfEmpty().Select( _
                  Function(ord) New With
                        {
                           t.CompanyName,
                           t.CustomerID,
                           .OrderID = CType(ord.OrderID, Nullable(Of Integer)),
                           .OrderDate = CType(ord.OrderDate, Nullable(Of DateTime))
                        })) _
         .OrderBy(Function(r) r.CustomerID) _
         .ThenBy(Function(r) r.OrderID)
   dg.ItemsSource = customers
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuOuterJoin1_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = ctx.Customers.GroupJoin(
      ctx.Orders,
      c => c.CustomerID,
      o => o.CustomerID,
      (c, o) => new
                   {
                      c.CustomerID,
                      c.CompanyName,
                      Orders = o
                   })
      .SelectMany(t=>t.Orders.DefaultIfEmpty().Select(ord=>
         new
         {
            t.CompanyName,
            t.CustomerID,
            OrderID=(int?)ord.OrderID,
            OrderDate=(DateTime?) ord.OrderDate}))
      .OrderBy(r => r.CustomerID).ThenBy((r => r.OrderID));

   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t2].[CompanyName], [t2].[CustomerID],
[t2].[value] AS [OrderID2], [t2].[value2] AS [OrderDate]
FROM (
SELECT [t1].[OrderID] AS [value], [t1].[OrderDate] AS [value2],
[t0].[CompanyName], [t0].[CustomerID]
    FROM [dbo].[Customers] AS [t0]
    LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
    ) AS [t2]
ORDER BY [t2].[CustomerID], [t2].[value]

					  

This code sample turned out to be ugly, primarily because the goal was to bind this to the data grid and see the same results as the inner join but with an extra row for FISSA and PARIS. In the SQL query, although a left outer join was performed, it was nested in a subquery, and the only result the outer query provides is a reordering of the fields.

You can also perform an outer join by using a LINQ query with the into keyword with the join. The following is a rewrite of the previous query, done as a LINQ query.

Sample of Visual Basic Code

Private Sub mnuOuterJoin2_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim customers = From c In ctx.Customers
                   Group Join o In ctx.Orders
                   On c.CustomerID Equals o.CustomerID Into InJoin = Group
                   From outJoin In InJoin.DefaultIfEmpty()
                   Order By c.CustomerID, outJoin.OrderID
                   Select New With
                     {
                        c.CustomerID,
                        c.CompanyName,
                        .OrderID = CType(outJoin.OrderID, Nullable(Of Integer)),
                        .OrderDate = CType(outJoin.OrderDate, Nullable(Of DateTime))
                     }
   dg.ItemsSource = customers
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuOuterJoin2_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var customers = from c in ctx.Customers
                   join o in ctx.Orders
                   on c.CustomerID equals o.CustomerID into inJoin
                   from outJoin in inJoin.DefaultIfEmpty()
                   orderby c.CustomerID, outJoin.OrderID
                   select new
                   {
                      c.CustomerID,
                      c.CompanyName,
                      OrderID = (int?)outJoin.OrderID,
                      OrderDate = (DateTime?)outJoin.OrderDate
                   };w
   dg.ItemsSource = customers;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT [t0].[CustomerID], [t0].[CompanyName],
[t1].[OrderID] AS [OrderID2], [t1].[OrderDate] AS [OrderDate]
FROM [dbo].[Customers] AS [t0]
LEFT OUTER JOIN [dbo].[Orders] AS [t1] ON [t0].[CustomerID] = [t1].[CustomerID]
ORDER BY [t0].[CustomerID], [t1].[OrderID]

					  

The LINQ query is much neater than the previous code example, which was implemented by extension methods. In addition, the SQL query is a nice, clean left outer join.

4.3.5. Grouping and Aggregation

LINQ to SQL also enables you to perform grouping operations to retrieve aggregate results. For example, you might want to retrieve the total amount of each order. To get the total of each order, get the sum of each order item in the Order_Details table. The following code sample shows how grouping and aggregation can solve this problem.

Sample of Visual Basic Code

Private Sub mnuAggregates_Click(ByVal sender As System.Object, _
                                ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   Dim sw = New StringWriter()
   ctx.Log = sw
   Dim orders = From o In ctx.Order_Details
                   Group o By OrderID = o.OrderID Into grouped = Group
                   Select New With
                          {
                             .OrderID = OrderID,
                             .Total = grouped.Sum(Function(line) _
                                          line.Quantity * line.UnitPrice * _
                                          (1 - CType(line.Discount, Decimal)))
                             }
   dg.ItemsSource = orders
   MessageBox.Show(sw.GetStringBuilder().ToString())
End Sub

Sample of C# Code

private void mnuAggregates_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   var sw = new StringWriter();
   ctx.Log = sw;
   var orders = from o in ctx.Order_Details
                group o by o.OrderID
                into grouped
                select new
                {
                OrderID = grouped.Key,
                Total = grouped.Sum(
                   line=>line.Quantity * line.UnitPrice *
                      (1 - (decimal)line.Discount))
                };
   dg.ItemsSource = orders;
   MessageBox.Show(sw.GetStringBuilder().ToString());
}

SQL Query

SELECT SUM([t1].[value]) AS [Total], [t1].[OrderID]
FROM (
    SELECT (CONVERT(Decimal(29,4),[t0].[Quantity])) * [t0].[UnitPrice] *
    (@p0 - (CONVERT(Decimal(33,4),[t0].[Discount]))) AS [value], [t0].[OrderID]
    FROM [dbo].[Order Details] AS [t0]
    ) AS [t1]
GROUP BY [t1].[OrderID]
-- @p0: Input Decimal (Size = -1; Prec = 33; Scale = 4) [1]

					  

This code sample grouped the Order_Details rows by OrderID and then calculated the total of each order by calculating the sum of the line items of the order. To calculate the sum, each line had to be calculated by multiplying the quantity by the unit price and then multiplying by one minus the discount.

4.3.6. Paging

When writing an application that queries thousands or millions of rows of data, you will often run into problems when a query returns many more rows of data than you could possibly display. Having said that, what’s the sense of waiting for all that data to be shipped from SQL Server to your application? For example, maybe you queried for the customers whose names begin with the letter A, but you didn’t realize that this would return ten thousand rows of data.

Paging can be a useful way to minimize the amount of data returned from a query so you can see the first part of the data quickly and decide whether you want to continue viewing more data. To implement paging, you can use the Skip and Take extension methods with your LINQ query. In the following sample code, a scrollbar has been added to the WPF form, and its settings are configured to match the quantity of pages of customers. When scrolling, the Scroll event is triggered, and you see the previous or next page of customers. This code sample uses the Skip and Take methods:

Sample of Visual Basic Code

Private Const pageSize As Integer = 25
Private pageCount As Integer
Private customerCount As Integer
Private customers As IQueryable(Of Tuple(Of String, String))
Private sw As New StringWriter()

Private Sub mnuPaging_Click(ByVal sender As System.Object, _
         ByVal e As System.Windows.RoutedEventArgs)
   Dim ctx = New NorthwindDataContext()
   ctx.Log = sw
   customers = From c In ctx.Customers
               Order By c.CompanyName
               Select New Tuple(Of String, String)(c.CustomerID, c.CompanyName)

   customerCount = customers.Count()
   pageCount = customerCount / pageSize
   If (pageCount * pageSize < customerCount) Then pageCount += 1

   scrData.Minimum = 0
   scrData.Maximum = pageCount
   scrData.Visibility = Visibility.Visible
   scrData.SmallChange = 1
   scrData_Scroll(Nothing, Nothing)
End Sub

Private Sub scrData_Scroll(ByVal sender As System.Object, _
      ByVal e As System.Windows.Controls.Primitives.ScrollEventArgs)
   Dim customersDisplay = From c In customers
                          Select New With {.ID = c.Item1, .Name = c.Item2}
   dg.ItemsSource = customersDisplay.Skip(CInt(scrData.Value) * pageSize).Take(pageSize)
End Sub

Sample of C# Code

private const int pageSize = 25;
private int pageCount;
private int customerCount;
private IQueryable<Tuple<string,string>> customers;
StringWriter sw = new StringWriter();

private void mnuPaging_Click(object sender, RoutedEventArgs e)
{
   var ctx = new NorthwindDataContext();
   ctx.Log = sw;
   customers = from c in ctx.Customers
               orderby c.CompanyName
               select
                  new Tuple>string,string>(c.CustomerID,c.CompanyName);

   customerCount = customers.Count();
   pageCount = customerCount / pageSize;
   if (pageCount * pageSize < customerCount) pageCount++;

   scrData.Minimum = 0;
   scrData.Maximum = pageCount;
   scrData.Visibility = Visibility.Visible;
   scrData.SmallChange = 1;
   scrData_Scroll(null, null);
}

private void scrData_Scroll(object sender,
                System.Windows.Controls.Primitives.ScrollEventArgs e)
{
   var customersDisplay = from c in customers
                          select new {ID = c.Item1, Name = c.Item2};
   dg.ItemsSource = customersDisplay.Skip((int)scrData.Value * pageSize).Take(pageSize);
}

SQL Query

SELECT COUNT(*) AS [value]
FROM [dbo].[Customers] AS [t0]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

SELECT [t1].[CustomerID] AS [item1], [t1].[CompanyName] AS [item2]
FROM (
    SELECT ROW_NUMBER() OVER (
           ORDER BY [t0].[CompanyName]) AS [ROW_NUMBER],
                                 [t0].[CustomerID], [t0].[CompanyName]
    FROM [dbo].[Customers] AS [t0]
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [0]
-- @p1: Input Int (Size = -1; Prec = 0; Scale = 0) [25]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

					  

Of the SQL queries that were run, the first query was to get the count of customers, which was executed when the customers.Count() call was made. The next SQL query was to retrieve the first page of customers. In this query, the two parameters are @p0, the current page, and @p1, the page size. These parameters are set to 0 and 25, respectively. The query itself uses the ROW_NUMBER function available in SQL Server 2005 and later, which is why using LINQ to SQL on SQL Server 2000 has limitations. As you page up and down, the second query executes but will substitute a different value for the current page (@p0).

In the Visual Basic 2010 and C# code, variables and constants are being defined outside the method for these variables to be accessible in all methods. The first method, mnuPaging_Click, executes when you select the Paging menu option. This method retrieves the count of customers and sets the LINQ to SQL query; it also configures the scroll bar and sets it to be visible. The next method, scrData_Scroll, is executed each time you click the scroll bar. This method retrieves the current page value from the scroll bar and uses the Skip and Take methods to retrieve a single page of data.

4.3.6.1. Practice Writing LINQ Queries to Display Data

4.3.6.1.1. Practice Writing LINQ Queries to Display Data

In this practice, you continue the order entry application from Section 4.2 by adding a GUI and then LINQ queries to populate the GUI with data.

If you encounter a problem completing an exercise, the completed projects can be installed from the Code folder on the companion CD.

EXERCISE 1 Add the GUI

In this exercise, you modify the WPF application you created in Lesson 1 by creating the GUI. In the next exercise, you add the LINQ queries to populate the screen.

Figure 4-12. This is the completed GUI that will display customers and orders.

EXERCISE 2 Creating LINQ Queries to Display Data

In this exercise, you add code that includes LINQ queries to the WPF application you worked on in Exercise 1, “Add the GUI,” of this lesson. This produces a running application for viewing the data, but in the next lesson, you’ll add code to add an order to a customer and save it.

4.3.7. Lesson Summary

This lesson provided detailed information about how to run LINQ to SQL queries.

• You can use the table properties on the DataContext object to run LINQ queries.

• If you provide a where clause, LINQ to SQL will create a SQL query that includes the where clause to limit the rows returned to the application.

• If you provide a projection in your select clause to limit the properties that are selected, LINQ to SQL will create a SQL query that includes a matching column filter to limit the data returned to the application.

• LINQ to SQL supports both inner and outer joins.

• LINQ to SQL supports grouping and aggregation.

• You can use the Skip and Take extension methods to implement paging over data.

4.3.8. Lesson Review

You can use the following questions to test your knowledge of the information in Section 4.3. The questions are also available on the companion CD if you prefer to review them in electronic form.

---

Note:

ANSWERS

Answers to these questions and explanations of why each answer choice is correct or incorrect are located in the Appendix A section at the end of the book.

---