Chapter 11: An introduction to LINQ

In this chapter, you’ll learn the basic concepts and skills for using a new feature of C# 2008 called LINQ. To illustrate these concepts and skills, I’ll use an implementation of LINQ called LINQ to Objects. You use LINQ to Objects to work with in-memory data structures such as generic lists and arrays.

How to sort the results of a query

If you want the results of a query to be returned in a particular sequence, you can include the orderby clause in the query expression. The syntax of this clause is shown at the top of figure 11-6. This syntax indicates that you can sort by one or more expressions in either ascending or descending sequence.

To understand how this works, the example in this figure shows how you might sort the Invoice objects retrieved from a generic list of invoices. Here, the query expression includes an orderby clause that sorts the invoices by vendor ID in ascending sequence (the default), followed by balance due in descending sequence. To do that, it uses the range variable that's declared by the from clause to refer to each Invoice object just like the where clause does. If you compare the results of this query with the results shown in the previous figure, you'll see how the sequence has changed.

To start, the vendor IDs are listed from smallest to largest. Then, within each vendor ID, the invoices are listed from those with the largest balances due to those with the smallest balances due. For example, the first invoice for vendor ID 123 has a balance due of $67.92, and the second invoice for that vendor ID has a balance due of $59.97.

The syntax of the orderby clause
orderby expression1 [ascending|descending]
[, expression2 [ascending|descending]]...

A LINQ query that sorts the generic list of invoices

A query expression that sorts the invoices by vendor ID and balance due
var invoices = from invoice in invoiceList
              where invoice.InvoiceTotal
              - invoice.PaymentTotal
              - invoice.CreditTotal > 0
              && invoice.DueDate < DateTime.Today.AddDays(15)
              orderby invoice.VendorID,
              invoice.InvoiceTotal
              - invoice.PaymentTotal
              - invoice.CreditTotal descending
              select invoice;

Code that executes the query
string invoiceDisplay = "Vendor ID\tInvoice No.\tBalance Due\n";
foreach (var invoice in invoices)
{
    invoiceDisplay += invoice.VendorID + "\t\t" +
    invoice.InvoiceNumber + "\t";
    if (invoice.InvoiceNumber.Length < 8)
        invoiceDisplay += "\t";
    invoiceDisplay += (invoice.InvoiceTotal - invoice.PaymentTotal
    - invoice.CreditTotal).ToString("c") + "\n";
}
MessageBox.Show(invoiceDisplay, "Vendor Invoices Due");

The resulting dialog box

Description

• The orderby clause lets you specify how the results of the query are sorted. You can specify one or more expressions on this clause, and each expression can be sorted in ascending or descending sequence.

Figure 11-6 How to sort the results of a query

How to select fields from a query

So far, the queries you've seen in this chapter have returned entire elements of a data source. To do that, the select clause simply named the range variable that represents those elements. But you can also return selected fields of the elements. To do that, you use the select clause shown in figure 11-7. This clause lets you identify one or more fields to be included in the query results. A query that returns something other than entire source elements is called a projection.

To illustrate how this works, the first select clause in this figure returns a single field of each element. In this case, it returns the InvoiceTotal property of an Invoice object. Note that because the InvoiceTotal property is defined as a decimal type, the query variable is declared implicitly as an
IEnumerable<decimal> type.

The second example shows a query expression that returns selected properties from the Invoice objects. Specifically, it returns the VendorID, InvoiceNumber, InvoiceTotal, PaymentTotal, and CreditTotal properties. If you look back at the example in the previous figure, you'll see that these are the only properties that are used when the query is executed. Because of that, these are the only properties that need to be retrieved.

Notice that the select clause in this example uses an object initializer to create the objects that are returned by the query. Also notice that the object initializer doesn't specify a type. That's because a type that includes just the five properties named in the initializer doesn't exist. In that case, an anonymous type is created. Because the name of an anonymous type is generated by the compiler, you can't refer to it directly. In most cases, that's not a problem. If it is, you can define the type you want to use and then name it on the object initializer.

The last example in this figure shows how you can assign an alias to a column in the query results. Here, the alias Number is assigned to the InvoiceNumber column, and the alias BalanceDue is assigned to the expression that calculates the balance due. These aliases are then used as the names of the properties in the anonymous type that's created, and you can refer to them when you execute the query. In this case, the result is the same as in figure 11-6.

Two ways to code of the select clause
select columnExpression
select new [type] { [PropertyName1 =] columnExpression1
[, [PropertyName2 =] columnExpression2]... }

A select clause that returns a single field
select invoice.InvoiceTotal

A select clause that creates an anonymous type
select new { invoice.VendorID, invoice.InvoiceNumber, invoice.InvoiceTotal,
invoice.PaymentTotal, invoice.CreditTotal }

A LINQ query that uses aliases in the select clause

A query expression that assigns aliases to a column and a calculated value
var invoices = from invoice in invoiceList
              where invoice.InvoiceTotal - invoice.PaymentTotal
               - invoice.CreditTotal > 0
               && invoice.DueDate < DateTime.Today.AddDays(15)
               orderby invoice.VendorID,
               invoice.InvoiceTotal - invoice.PaymentTotal
               - invoice.CreditTotal descending
               select new
               {
                   invoice.VendorID,
                   Number = invoice.InvoiceNumber,
                   BalanceDue = invoice.InvoiceTotal
                   - invoice.PaymentTotal
                   - invoice.CreditTotal
               };

Code that executes the query
string invoiceDisplay = "Vendor ID\tInvoice No.\tBalance Due\n";
foreach (var invoice in invoices)
{
    invoiceDisplay += invoice.VendorID + "\t\t" + invoice.Number + "\t";
    if (invoice.Number.Length < 8)
        invoiceDisplay += "\t";
    invoiceDisplay += invoice.BalanceDue.ToString("c") + "\n";
}
MessageBox.Show(invoiceDisplay, "Sorted Vendor Invoices Due");

Description

• The select clause indicates the data you want to return from each element of the query results.
• A query that returns anything other than entire source elements is called a projection. All of the examples above illustrate projections.
• To return two or more fields from each element, you code an object initializer within the select clause. If the object initializer doesn't specify a type, an anonymous type that contains the specified fields as its properties is created. The second and third examples above illustrate object initializers and anonymous types.
• You can assign an alias to a field by coding the alias name, followed by an equals sign, followed by a column expression. You can assign an alias to an existing column in the data source or to a calculated column as shown in the third example above.

Figure 11-7 How to select fields from a query