23.3. Query Pieces

This section introduces you to a number of the query operations that make up the basis of LINQ. If you have written SQL statements, these will feel familiar, although the ordering and syntax might take a little time to get used to. There are a number of query operations you can use, and there are numerous reference web sites that provide more information on how to use these. For the moment we will focus on those operations necessary to improve the search query introduced at the beginning of this chapter.

23.3.1. From

Unlike SQL, where the first statement is Select, in LINQ the first statement is typically From. One of the key considerations in the creation of LINQ was providing IntelliSense support within Visual Studio 2008. If you've ever wondered why there is no IntelliSense support in SQL Management Studio for SQL Server 2005 for writing queries, this is because, in order to determine what to select, you need to know where the data is coming from. By reversing the order of the statements, LINQ is able to generate IntelliSense as soon as you start typing.

As you can see from the tooltip in Figure 23-4, the From statement is made up of two parts, <element> and <collection>. The latter is the source collection from which you will be extracting data, and the former is essentially an iteration variable that can be used to refer to the items being queried. This pair can then be repeated for each source collection.

Figure 23.4. Figure 23-4

In this case you can see we are querying the customers collection, with an iteration variable c, and the orders collection c.Orders using the iteration variable o. There is an implicit join between the two source collections because of the relationship between a customer and that customer's orders. As you can imagine, this query will result in the cross-product of items in each source collection. This will lead to the pairing of a customer with each order that this customer has.

Note that we don't have a Select statement, because we are simply going to return all elements, but what does each result record look like? If you were to look at the tooltip for results, you would see that it is a generic IEnumerable of an anonymous type. The anonymous type feature is heavily used in LINQ so that you don't have to create classes for every result. If you recall from the initial code, we had to have a SearchResult class in order to capture each of the results. Anonymous types mean that we no longer have to create a class to store the results. During compilation, types containing the relevant properties are dynamically created, thereby giving us a strongly typed result set along with IntelliSense support. Though the tooltip for results may report only that it is an IEnumerable of an anonymous type, when you start to use the results collection you will see that the type has two properties, c and o, of type Customer and Order, respectively. Figure 23-5 displays the output of this code, showing the customer-order pairs.

Figure 23.5. Figure 23-5

23.3.2. Select

In the previous code snippet the result set was a collection of customer-order pairs, when in fact what we want to return is the customer name and the order information. We can do this by using a Select statement in a way similar to the way you would when writing a SQL statement:

Private Sub LinqQueryWithSelect()
    Dim customers = BuildCustomers()

    Dim results = From c In customers, o In c.Orders _
                  Select c.FirstName, c.LastName, o.Product, o.Quantity

    ObjectDumper.Write(results)
End Sub

Now when we execute this code the result set is a collection of objects that have FirstName, LastName, Product, and Quantity properties. This is illustrated in the output shown in Figure 23-6.

Figure 23.6. Figure 23-6

23.3.3. Where

So far all you have seen is how we can effectively flatten the customer-order hierarchy into a result set containing the appropriate properties. What we haven't done is filter these results so that they only return customers with a first name greater than or equal to five characters, and who are ordering Milk. In the following snippet we introduce a Where statement, which restricts the source collections on both these axes:

Private Sub LinqQueryWithWhere()
    Dim customers = BuildCustomers()

    Dim results = From c In customers, o In c.Orders _
                  Where c.FirstName.Length >= 5 And _
                        o.Product = "Milk" _
                  Select c.FirstName, c.LastName, o.Product, o.Quantity

    ObjectDumper.Write(results)
End Sub

One thing to be aware of here is the spot in which the Where statement appears relative to the From and Select statements. In Figure 23-7 you can see that you can place a Where statement after the Select statement.

Figure 23.7. Figure 23-7

The difference lies in the order in which the operations are carried out. As you can imagine, placing the Where statement after the Select statement causes the filter to be carried out after the projection. In the following code snippet you can see how the previous snippet can be rewritten with the Where statement after the Select statement. You will notice that the only difference is that there are no c or o prefixes in the Where clause. This is because these iteration variables are no longer in scope once the Select statement has projected the data from the source collection into the result set. Instead, the Where statement uses the properties on the generated anonymous type.

Dim results = From c In customers, o In c.Orders _
              Select c.FirstName, c.LastName, o.Product, o.Quantity _
              Where FirstName.Length >= 5 And _
                    Product = "Milk"

The output of this query is similar to the previous one in that it is a result set of an anonymous type with the four properties FirstName, LastName, Product, and Quantity.

23.3.4. Group By

We are getting close to our initial query, except that our current query returns a list of all the Milk orders for all the customers. For a customer who might have placed two orders for Milk, this will result in two records in the result set. What we actually want to do is to group these orders by customer and take an average of the quantities ordered. Not surprisingly, this is done with a Group By statement, as shown in the following snippet:

Private Sub LinqQueryWithGroupingAndWhere()
    Dim customers = BuildCustomers()

    Dim results = From c In customers, o In c.Orders _
                  Where c.FirstName.Length >= 5 And _
                        o.Product = "Milk" _
                  Group By c Into avg = Average(o.Quantity) _
                  Select c.FirstName, c.LastName, avg

    ObjectDumper.Write(results)
End Sub

What is a little confusing about the Group By statement is the syntax that it uses. Essentially what it is saying is "group by dimension X" and place the results "Into" an alias that can be used elsewhere. In this case the alias is avg, which will contain the average we are interested in. Because we are grouping by the iteration variable c, we can still use this in the Select statement, along with the Group By alias. Now when we run this we get the output shown in Figure 23-8, which is much closer to our initial query.

Figure 23.8. Figure 23-8

23.3.5. Custom Projections

We still need to tidy up the output so that we are returning a well-formatted customer name and an appropriately named average property, instead of the query results, FirstName, LastName, and avg. We can do this by customizing the properties that are contained in the anonymous type that is created as part of the Select statement projection. Figure 23-9 shows how you can create anonymous types with named properties.

Figure 23.9. Figure 23-9

This figure also illustrates that the type of the AverageMilkOrder property is indeed a Double, which is what we would expect based on the use of the Average function. It is this strongly typed behavior that can really assist us in the creation and use of rich LINQ statements.

23.3.6. Order By

The last thing we have to do with the LINQ statement is to order the results. We can do this by ordering the customers based on their FirstName property, as shown in the following snippet:

Private Sub FinalLinqQuery()
    Dim customers = BuildCustomers()

    Dim results = From c In customers, o In c.Orders _
                  Order By c.FirstName _
                  Where c.FirstName.Length >= 5 And _
                        o.Product = "Milk" _
                  Group By c Into avg = Average(o.Quantity) _
                  Select New With {.Name = c.FirstName & " " & c.LastName, _
                                   .AverageMilkOrder = avg} _

    ObjectDumper.Write(results)
End Sub

One thing to be aware of is how you can easily reverse the order of the query results. Here this can be done either by supplying the keyword Descending (Ascending is the default) at the end of the Order By statement, or by applying the Reverse transformation on the entire results set:

Order By c.FirstName Descending

or

ObjectDumper.Write(results.Reverse)

As you can see from the final query we have built up, it is much more descriptive than the initial query. We can easily see that we are selecting the customer name and an average of the order quantities. It is clear that we are filtering based on the length of the customer name and on orders for Milk, and that the results are sorted by the customer's first name. We also haven't needed to create any additional classes to help perform this query.