Introduction to LINQ Visual Basic 2010 How to

© 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 1 Introduction � Large amounts of data are often stored in a database—an organized collection of data. � A database management system (DBMS) provides mechanisms for storing, organizing, retrieving and modifying data contained in the database. � Today’s most popular database systems are relational databases. � A language called Structured Query Language (SQL)— pronounced “sequel”—is an international standard used with relational databases to perform queries (that is, to request information that satisfies given criteria) and to manipulate data. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 1 Introduction � For years, programs that accessed a relational database passed SQL queries as Strings to the database management system then processed the results. � A logical extension of querying and manipulating data in databases is to perform similar operations on any sources of data, such as arrays, collections (like the Items collection of a List. Box) and files. � Microsoft developed LINQ (Language Integrated Query) to enable you to write query expressions similar to SQL queries that retrieve information from a wide variety of data sources—not just relational databases—using a common syntax that is built into Visual Basic. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 1 Introduction � This enables you to take advantage of the IDE’s Intelli. Sense feature when writing LINQ queries— Intelli. Sense is not provided for SQL queries. � We use LINQ to Objects in this chapter to query the contents of arrays, selecting elements that satisfy a set of conditions—this is known as filtering. � We also use LINQ to Objects to perform common array manipulations such as sorting an array. � Figure 11. 1 shows the types of LINQ queries we cover in this book and how we use them. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � LINQ allows you to look at collections of data, extract information and manipulate data. � In a LINQ query, you might want to locate all Employees whose salaries are in a specific range (Section 11. 3). � To respond to that query, LINQ has to iterate over the data, looking at each item to see if the Employee’s salary is in range and, if so, selecting that item. � You might also manipulate the data—for example, for each Employee in the result of the preceding query, you could increase the Employee’s base salary by 4%. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � LINQ to Objects works with objects that implement the IEnumerable interface, which enables a program to iterate over a collection of data. � In fact, the For Each…Next statement is specifically used to iterate over IEnumerable objects, such as arrays —arrays implement the IEnumerable interface. � Any object of a class that implements the IEnumerable interface has an is-a relationship with IEnumerable— this is true of collections like the Items collection in a List. Box and other collections that we introduce in the online Chapter 25. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � Figure 11. 2 demonstrates querying the array of Integers named values (declared in line 9) using LINQ. � Repetition statements that filter arrays focus on the process of getting the results—iterating through the elements and checking whether they satisfy the desired criteria. � In contrast, LINQ specifies the conditions that elements must satisfy, not the steps necessary to get the results. � The query in lines 20– 22 specifies that the results should consist of all the Integers in the values array that are greater than 4. � It does not specify how those results are obtained— nevertheless, the compiler generates all the necessary code. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � Our first LINQ query begins with a From clause (line 20), which specifies a range variable (value) and the data source to query (the array values). � The range variable represents each item in the data source, much like the control variable in a For Each…Next statement. � Introducing the range variable in the From clause at the beginning of the query allows the IDE to provide Intelli. Sense while you write the rest of the query—the IDE knows the type of the range variable (via local type inference), so it can display the methods and properties of the object. � If the condition in the Where clause (line 21) evaluates to True, the element is selected—that is, it’s included in the collection of Integers that represents the query results. � Here, the Integers in the array are included only if they’re greater than 4. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � For each item in the data source, the Select clause (line 22) determines what value appears in the results. � In this case, it’s the Integer that the range variable currently represents. � The Select clause is usually placed at the end of the query for clarity, though it may be placed after the From clause and before other clauses, or omitted. � If omitted, the range variable is implicitly selected. � Later, you’ll see that the Select clause can transform the selected items—for example, Select value * 2 in this example would have multiplied each selected value in the result by 2. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � Displaying the Results of a LINQ Query ◦ Lines 27– 29 display the query results using a For Each…Next statement. ◦ A LINQ query returns an IEnumerable object. ◦ Therefore, you can use a For Each…Next statement to iterate over the results of any LINQ query. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � Sorting LINQ Query Results ◦ The LINQ query in lines 33– 35 selects the elements of the array values and returns an IEnumerable object containing a sorted copy of the elements. ◦ The Order By clause (line 34) sorts the query results in ascending order. ◦ The LINQ queries in lines 46– 48 and 60– 63 use the Descending modifier in the Order By clause to sort query results in descending order. ◦ An Ascending modifier also exists but is rarely used, because it’s the default. ◦ You can use the Order By clause only for values that can be compared to one another. ◦ The Order By clause supports values of any type that implements the interface IComparable, such as the primitive numeric types and String. ◦ Such types provide a Compare. To method. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 2 Querying an Array of Primitive-Type Elements Using LINQ � Chaining LINQ Queries ◦ The query in lines 46– 48 shows that you can chain LINQ queries by having one query operate on the results of another. ◦ In this case, the query sorts filtered (the result of the query in lines 20– 22) in descending order. � Combining the Where and Order By Clauses in a LINQ Query ◦ The query in lines 60– 63 uses the Where and Order By clauses to filter the elements of array values, looking for all values greater than 4, and sorts the results in descending order. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � LINQ is not limited to querying arrays of primitive types. � It can be used with most data types. � It cannot be used when a query does not have a defined meaning—for example, you cannot use Order By on objects that cannot be compared to one another to determine sorting order. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Primitive-Type Elements Using LINQ � Figure 11. 3 presents class Employee and Fig. 11. 4 uses LINQ to perform the following operations on an array of Employee objects: ◦ Select all Employees with salaries in the range $4000– 6000. ◦ Sort Employees by Last. Name then by First. Name for Employees that have the same Last. Name. ◦ Obtain the first Employee in the sorted collection of Employees. ◦ Select the unique Last. Names. ◦ Select just the First. Name and Last. Name from each Employee object. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Filtering Employees Based on Monthly. Salary ◦ Lines 28– 29 of Fig. 11. 4 shows a Where clause that accesses the properties of the range variable employee. ◦ In this example, the compiler infers that the range variable is of type Employee because employees was declared as an array of Employees (lines 8– 15). © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Any Boolean expression can be used in a Where clause. � Lines 28– 29 use And. Also to combine conditions. � Here, only employees that have a salary between $4, 000 and $6, 000 per month, inclusive, are included in the query result. � Lines 36– 39 display the query results. � Each element in the result is an Employee object, so when each element is implicitly converted to a String (line 38), Employee’s To. String method is called implicitly. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Sorting Employees by Last. Name and First. Name ◦ Line 44 uses an Order By clause to sort the Employees in the result according to multiple properties specified in a comma-separated list. ◦ In this query, the employees are sorted alphabetically by last name. ◦ Each group of Employees that have the same last name is then sorted within the group by first name. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Using the Count and First Extension Methods � As we’ve mentioned, LINQ operates on IEnumerable objects. � The IEnumerable interface contains the overloaded method Get. Enumerator, which returns an object that can be used to iterate over a collection of data. � Though IEnumerable declares only the overloaded Get. Enumerator method, you can actually call many other methods on an IEnumerable object. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � In the IDE, when you type the name of an IEnumerable object (such as an array or the result of a LINQ query) then type the dot (. ) separator, Intelli. Sense shows you a list of the methods and properties that can be used with that object. � Some of the methods are so-called extension methods. � Extension methods allow you to enhance the functionality of an existing class or interface without modifying the class’s or interface’s source code. � In fact, Microsoft has defined most LINQ capabilities as extension methods for the IEnumerable interface and you can call these directly on any IEnumerable object. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � For example, if you have an array of Doubles called numbers and you want to calculate the average of its values, you can simply call the Average extension method, as in numbers. Average(). � You can also create your own extension methods, which is beyond the scope of this book; so, we show to do this at www. deitel. com/books/vb 2010 htp/. � Some of IEnumerable’s 45 extension methods are shown in Fig. 11. 5. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Line 51 introduces the Count extension method, which returns the number of elements in the result of the query at lines 43– 45. � The First extension method (line 52) returns the first element in the result—an Employee object in this example. � If the result does not contain any elements, method First throws an Invalid. Operation. Exception. � For this reason, we use extension method Count to ensure that the query result contains at least one item (line 51) before calling First. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � You might wonder why Microsoft chose to use extension methods rather than simply including these methods directly in the IEnumerable interface. � There are 45 extension methods for interface IEnumerable, so it would be costly to implement the interface for every class that wants to be an IEnumerable when you only need a few of the methods for your class. � Separately, the extension methods for IEnumerable are implemented in such a manner that they are defined once and can be used on any IEnumerable object, so you do not need to customize these methods for your own classes. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Selecting Specific Properties of an Object ◦ Line 60 uses the Select clause in a new way. ◦ Rather than selecting the range variable, we select each Employee’s Last. Name property. ◦ In this case, the query result is a collection of only of the last names—which are String objects, rather than complete Employee objects. ◦ The Distinct clause (line 61) prevents duplicate values from appearing in the query results. ◦ In this case, it eliminates duplicate last names—this occurs because the clauses in a LINQ query are applied in the order in which they appear, each using the results of the previous clause in the query. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Creating Objects of Anonymous Types ◦ The last LINQ query in the example (lines 73– 74) selects only the properties First. Name and Last. Name from each Employee object. ◦ You can select portions of matching objects by specifying the properties to select in a comma-separated list. ◦ Only the selected properties can be accessed when iterating over the query results. ◦ When you select a portion of an object’s properties, the compiler creates a new class containing those properties— First. Name and Last. Name in this example—and the methods that are inherited by all classes from class Object. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � The new class does not have a name and cannot be used by you to create new objects—such classes are called anonymous types. � In the anonymous type, the compiler overrides the inherited To. String method to return a String representation of the object in the form: { property. Name 1 = value 1, property. Name 2 = value 2, … } © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � Local type inference allows you to use the anonymous type—the compiler can infer the anonymous type from the query in lines 73– 74. � In addition, the IDE provides Intelli. Sense support for the compiler-generated anonymous types. � In the loop at lines 79– 82, the compiler infers that the control variable’s type is the anonymous type containing the First. Name and Last. Name properties and the methods inherited from class Object. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � If you type a dot (. ) separator after the control variable’s name (element) in line 81, the IDE displays the Intelli. Sense window (Fig. 11. 6) showing the anonymous type’s properties and methods. � When you execute this program, line 81 implicitly calls the anonymous type’s To. String method to get the String representation of each element. � You can see in the program’s output that the To. String method of an anonymous type returns the property names and their values, enclosed in braces. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 3 Querying an Array of Reference. Type Elements Using LINQ � You can create your own anonymous types, which is beyond the scope of this book; so we show to do this at www. deitel. com/books/vb 2010 htp/. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 4 Deferred Execution and Transforming Query Results � LINQ uses a technique called deferred execution—a query executes only when you iterate over the results, not when the query is defined. � This allows you to create a query once and execute it many times. � This is similar to how methods are used. � If you make any changes to the data in a LINQ query’s data source, the next time you iterate over the query’s results, the query will process the current data in the data source. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 4 Deferred Execution and Transforming Query Results � Figure 11. 7 filters an array of Strings by searching for those that begin with "r". � Initially the array (lines 8– 9) contains two such Strings. � Later in the program we modify the array then reexecute the LINQ query to demonstrate deferred execution. � This example also demonstrates how to transform the items that match the Where clause—in this case, each matching String is converted to uppercase in the query result. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 4 Deferred Execution and Transforming Query Results � Calling a Method of an Object in a LINQ Query’s Where Clause � The query in lines 13– 16 filters the colors array by using String method Starts. With in the Where clause (line 14) to determine whether each item in the array starts with the letter "r". � Method Starts. With uses a case sensitive comparison to determine whether a String starts with the String in the method’s argument. � If the String starts with "r", the Starts. With method returns True and the element is included in the query results. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 4 Deferred Execution and Transforming Query Results � Transforming the Results ◦ In Section 11. 2, we mentioned that the Select clause can transform the selected items. ◦ The Select clause (line 16) transforms the Strings in the result by converting each String to uppercase using String method To. Upper. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 4 Deferred Execution and Transforming Query Results � Modifying the Array’s Contents and Reexecuting the Query ◦ We created the query only once (lines 13– 16), yet iterating over the results (lines 19– 21 and 30– 32) gives two different lists of colors. ◦ This demonstrates LINQ’s deferred execution. ◦ The first line of output (produced by lines 19– 21) shows the two Strings that begin with "r" in the original array. ◦ Lines 26– 27 replaced the Strings "blue" and "orange" in the original array with the Strings "ruby" and "rose". ◦ Notice that the second line of output (produced by lines 30– 32) shows four matching Strings rather than two—the changes to the data source were included when we reexecuted the query. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.

11. 5 LINQ Resource Center � This chapter introduced LINQ’s basic capabilities and syntax. � We use more advanced features of LINQ in later chapters. � We’ve created an extensive LINQ Resource Center that contains many links to additional information, including blogs by Microsoft LINQ team members, sample chapters, tutorials, videos, FAQs, resource sites and webcasts. � Browse the LINQ Resource Center (www. deitel. com/LINQ/) to learn more about this exciting technology. © 1992 -2011 by Pearson Education, Inc. All Rights Reserved.