第 87 页

If orders.Length > 0 Then

Dim cr As New SearchResult

cr.Customer = c.FirstName & " " & c.LastName

For Each o As Order In orders

cr.Quantity += o.Quantity

cr.Count += 1

Next

results.Add(cr)

End If

End If

Next

results.Sort(AddressOf CompareSearchResults)

ObjectDumper.Write(results, Writer)

End Sub

Code snippet MainForm.vb

Before we jump in and show how LINQ can improve this snippet, let’s examine how this snippet

works. The opening line calls out to a method that simply generates Customer objects. This will

be used throughout the snippets in this chapter. The main loop in this method iterates through

the array of customers searching for those customers with a first name longer than five characters.

Upon finding such a customer, you use the Array.FindAll method to retrieve all orders where the

predicate is true. Prior to the introduction of anonymous methods you couldn’t supply the predicate

function inline with the method. As a result, the usual way to do this was to create a simple class

that could hold the query variable (in this case, the product, Milk) that you were searching for,

and that had a method that accepted the type of object you were searching through, in this case an

Order. With the introduction of Lambda expressions, you can now rewrite this line:

590 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

c#

var orders = Array.FindAll(c.Orders, order=>order.Product =="Milk");

Vb

Dim orders = Array.FindAll(c.Orders,

Function(o As Order) o.Product = "Milk")

Here you have also taken advantage of type inferencing to determine the type of the variable orders,

which is of course still an array of orders.

Returning to the snippet, once you have located the orders you still need to iterate through them and

sum up the quantity ordered and store this, along with the name of the customer and the number of

orders. This is your search result, and as you can see you are using a SearchResult object to store

this information. For convenience, the SearchResult object also has a read-only Average property,

which simply divides the total quantity ordered by the number of orders. Because you want to

sort the customer list, you use the Sort method on the List class, passing in the address of a

comparison method. Again, using Lambda expressions, this can be rewritten as an inline statement:

c#

results.Sort((r1, r2) => string.Compare(r1.Customer, r2.Customer));

Vb

results.Sort( Function(r1 as SearchResult, r2 as SearchResult) _

String.Compare(r1.Customer, r2.Customer))

The last part of this snippet is to print out the search

results. This is using one of the samples that ships with

Visual Studio 2010 called ObjectDumper. This is a simple

class that iterates through a collection of objects printing

out the values of the public properties. In this case the

output would look like Figure 28-2.

fiGure 28-2

As you can see from this relatively simple query, the code

to do this in the past was quite prescriptive and required additional classes in order to carry out the

query logic and return the results. With the power of LINQ you can build a single expression that

clearly describes what the search results should be.

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. You can use a number of query operations, and numerous reference

web sites provide more information on how to use them. For the moment, you will focus on those

operations necessary to improve the search query introduced at the beginning of this chapter.

Query Pieces .

591

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 2010. 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 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 28-3,

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

fiGure 28-3

an iteration variable that can be used to refer

to the items being queried. This pair can then be repeated for each source collection.

In this case you can see you 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 you don’t have a Select statement, because you 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, you had to have a SearchResult class in order to capture each of the results.

Anonymous types mean that you no longer have to create a class to store the results. During

compilation, types containing the relevant properties are dynamically created, thereby giving

you 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 28-4 displays the output

of this code, showing the customer-order

pairs.

fiGure 28-4

C# actually requires a Select clause to be present in all LINQ, even if you are

returning all objects in the From clause.

592 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

select

In the previous code snippet the result set was a collection of customer-order pairs, when in fact

what you want to return is the customer name and the order information. You can do this by using

a Select statement in a way similar to the way you would when writing a SQL statement:

c#

private void LinqQueryWithSelect(){

var customers = BuildCustomers();

var results = from c in customers

from o in c.Orders

select new{c.FirstName,

c.LastName,o.Product,o.Quantity};

ObjectDumper.Write(results, Writer);

}

Code snippet MainForm.cs

Vb

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, Writer)

End Sub

Code snippet MainForm.vb

Now when you 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 28-5.

where

So far all you have seen is how you can effectively

flatten the customer-order hierarchy into a result set

containing the appropriate properties. What you 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. The following snippet introduces a Where statement, which restricts the source collections on

both these axes:

c#

private void LinqQueryWithWhere(){

var customers = BuildCustomers();

var results = from c in customers

fiGure 28-5

Query Pieces .

593

from o in c.Orders

where c.FirstName.Length >= 5 &&

o.Product == "Milk"

select new { c.FirstName, c.LastName, o.Product, o.Quantity };

ObjectDumper.Write(results, Writer);

}

Code snippet MainForm.cs

Vb

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, Writer)

End Sub

Code snippet MainForm.vb

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.

Group by

You are getting close to your initial query, except that your 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 you 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:

c#

private void LinqQueryWithGroupingAndWhere(){

var customers = BuildCustomers();

var results = from c in customers

from o in c.Orders

where c.FirstName.Length >= 5 &&

o.Product == "Milk"

group o by c into avg

select new { avg.Key.FirstName, avg.Key.LastName,

avg = avg.Average(o => o.Quantity) };

ObjectDumper.Write(results, Writer);

}

Code snippet MainForm.cs

594 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

Vb

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

Code snippet MainForm.vb

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 you are interested in. Because you are

grouping by the iteration variable c, you can still use this in

the Select statement, along with the Group By alias. Note

that the C# example is slightly different in that although the

grouping is still done on c, you then have to access it via

the Key property of the alias. Now when you run this you

get the output shown in Figure 28-6, which is much closer

to your initial query.

custom Projections

fiGure 28-6

You still need to tidy up the output so that

you are returning a well-formatted customer

name and an appropriately named average

property, instead of the query results,

FirstName, LastName, and avg. You can do

this by customizing the properties that

fiGure 28-7

are contained in the anonymous type

that is created as part of the Select statement

projection. Figure 28-7 shows how you can create

anonymous types with named properties.

This figure also illustrates that the type of the AverageMilkOrder property is indeed a Double,

which is what you would expect based on the use of the Average function. It is this strongly typed

behavior that can really assist you in the creation and use of rich LINQ statements.

order by

The last thing you have to do with the LINQ statement is to order the results. You can do this by

ordering the customers based on their FirstName property, as shown in the following snippet:

Query Pieces .

595

c#

private void LinqQueryWithGroupingAndWhere(){

var customers = BuildCustomers();

var results = from c in customers

from o in c.Orders

orderby c.FirstName

where c.FirstName.Length >= 5 &&

o.Product == "Milk"

group o by c into avg

select new { Name = avg.Key.FirstName + " " + avg.Key.LastName,

AverageMilkOrder = avg.Average(o => o.Quantity) };

ObjectDumper.Write(results, Writer);

}

Code snippet MainForm.cs

Vb

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

Code snippet MainForm.vb

One thing to be aware of is how you can easily reverse the order of the query results. Here you can

do this 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 result set:

Order By c.FirstName Descending

or

ObjectDumper.Write(results.Reverse)

As you can see from the final query you have built up, it is much more descriptive than the initial

query. You can easily see that you are selecting the customer name and an average of the order

quantities. It is clear that you 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. You also haven’t

needed to create any additional classes to help perform this query.

596 .

chaPter 28 lAnguAge inTegrATed QuerieS (linQ)

debuGGinG and execution

One of the things you should be aware of with LINQ is that the queries are not executed until they

are used. In fact, each time you use a LINQ query you will find that the query is re-executed. This