Walkthrough: Writing Queries in Visual Basic

Article
11/24/2013

This walkthrough demonstrates how you can use Visual Basic language features to write Language-Integrated Query (LINQ) query expressions. The walkthrough demonstrates how to create queries on a list of Student objects, how to run the queries, and how to modify them. The queries incorporate several features that were new in Visual Basic 2008, including object initializers, local type inference, and anonymous types.

After completing this walkthrough, you will be ready to move on to the samples and documentation for the specific LINQ provider you are interested in. LINQ providers include LINQ to SQL, LINQ to DataSet, and LINQ to XML.

Create a Project

To create a console application project

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the Installed Templates list, click Visual Basic.
In the list of project types, click Console Application. In the Name box, type a name for the project, and then click OK.

A project is created. By default, it contains a reference to System.Core.dll. Also, the Imported namespaces list on the References Page, Project Designer (Visual Basic) includes the System.Linq namespace.
On the Compile Page, Project Designer (Visual Basic), ensure that Option infer is set to On.

Add an In-Memory Data Source

The data source for the queries in this walkthrough is a list of Student objects. Each Student object contains a first name, a last name, a class year, and an academic rank in the student body.

To add the data source

Define a Student class, and create a list of instances of the class.

Important

The code needed to define the Student class and create the list used in the walkthrough examples is provided in How to: Create a List of Items. You can copy it from there and paste it into your project. The new code replaces the code that appeared when you created the project.

To add a new student to the students list

Follow the pattern in the getStudents method to add another instance of the Student class to the list. Adding the student will introduce you to object initializers. For more information, see Object Initializers: Named and Anonymous Types (Visual Basic).

Create a Query

When executed, the query added in this section produces a list of the students whose academic rank puts them in the top ten. Because the query selects the complete Student object each time, the type of the query result is IEnumerable(Of Student). However, the type of the query typically is not specified in query definitions. Instead, the compiler uses local type inference to determine the type. For more information, see Local Type Inference (Visual Basic). The query's range variable, currentStudent, serves as a reference to each Student instance in the source, students, providing access to the properties of each object in students.

To create a simple query

Find the place in the Main method of the project that is marked as follows:

' ****Paste query and query execution code from the walkthrough, 
' ****or any code of your own, here in Main.

Copy the following code and paste it in.

Dim studentQuery = From currentStudent In students
                   Where currentStudent.Rank <= 10
                   Select currentStudent

Rest the mouse pointer over studentQuery in your code to verify that the compiler-assigned type is IEnumerable(Of Student).

Run the Query

The variable studentQuery contains the definition of the query, not the results of running the query. A typical mechanism for running a query is a For Each loop. Each element in the returned sequence is accessed through the loop iteration variable. For more information about query execution, see Writing Your First LINQ Query (Visual Basic).

To run the query

Add the following For Each loop below the query in your project.

For Each studentRecord In studentQuery
    Console.WriteLine(studentRecord.Last & ", " & studentRecord.First)
Next

Rest the mouse pointer over the loop control variable studentRecord to see its data type. The type of studentRecord is inferred to be Student, because studentQuery returns a collection of Student instances.
Build and run the application by pressing CTRL+F5. Note the results in the console window.

Modify the Query

It is easier to scan query results if they are in a specified order. You can sort the returned sequence based on any available field.

To order the results

Add the following Order By clause between the Where statement and the Select statement of the query. The Order By clause will order the results alphabetically from A to Z, according to the last name of each student.
```
Order By currentStudent.Last Ascending 
```
To order by last name and then first name, add both fields to the query:
```
Order By currentStudent.Last Ascending, currentStudent.First Ascending 
```
You can also specify Descending to order from Z to A.
Build and run the application by pressing CTRL+F5. Note the results in the console window.

To introduce a local identifier

Add the code in this section to introduce a local identifier in the query expression. The local identifier will hold an intermediate result. In the following example, name is an identifier that holds a concatenation of the student's first and last names. A local identifier can be used for convenience, or it can enhance performance by storing the results of an expression that would otherwise be calculated multiple times.

Dim studentQuery2 =
        From currentStudent In students
        Let name = currentStudent.Last & ", " & currentStudent.First
        Where currentStudent.Year = "Senior" And currentStudent.Rank <= 10
        Order By name Ascending
        Select currentStudent

' If you see too many results, comment out the previous 
' For Each loop. 
For Each studentRecord In studentQuery2
    Console.WriteLine(studentRecord.Last & ", " & studentRecord.First)
Next

Build and run the application by pressing CTRL+F5. Note the results in the console window.

To project one field in the Select clause

Add the query and For Each loop from this section to create a query that produces a sequence whose elements differ from the elements in the source. In the following example, the source is a collection of Student objects, but only one member of each object is returned: the first name of students whose last name is Garcia. Because currentStudent.First is a string, the data type of the sequence returned by studentQuery3 is IEnumerable(Of String), a sequence of strings. As in earlier examples, the assignment of a data type for studentQuery3 is left for the compiler to determine by using local type inference.
```
Dim studentQuery3 = From currentStudent In students
                    Where currentStudent.Last = "Garcia" 
                    Select currentStudent.First

' If you see too many results, comment out the previous 
' For Each loops. 
For Each studentRecord In studentQuery3
    Console.WriteLine(studentRecord)
Next
```
Rest the mouse pointer over studentQuery3 in your code to verify that the assigned type is IEnumerable(Of String).
Build and run the application by pressing CTRL+F5. Note the results in the console window.

To create an anonymous type in the Select clause

Add the code from this section to see how anonymous types are used in queries. You use them in queries when you want to return several fields from the data source rather than complete records (currentStudent records in previous examples) or single fields (First in the preceding section). Instead of defining a new named type that contains the fields you want to include in the result, you specify the fields in the Select clause and the compiler creates an anonymous type with those fields as its properties. For more information, see Anonymous Types (Visual Basic).

The following example creates a query that returns the name and rank of seniors whose academic rank is between 1 and 10, in order of academic rank. In this example, the type of studentQuery4 must be inferred because the Select clause returns an instance of an anonymous type, and an anonymous type has no usable name.
```
Dim studentQuery4 =
        From currentStudent In students
        Where currentStudent.Year = "Senior" And currentStudent.Rank <= 10
        Order By currentStudent.Rank Ascending
        Select currentStudent.First, currentStudent.Last, currentStudent.Rank

' If you see too many results, comment out the previous 
' For Each loops. 
For Each studentRecord In studentQuery4
    Console.WriteLine(studentRecord.Last & ", " & studentRecord.First &
                      ":  " & studentRecord.Rank)
Next
```
Build and run the application by pressing CTRL+F5. Note the results in the console window.

Additional Examples

Now that you understand the basics, the following is a list of additional examples to illustrate the flexibility and power of LINQ queries. Each example is preceded by a brief description of what it does. Rest the mouse pointer over the query result variable for each query to see the inferred type. Use a For Each loop to produce the results.

' Find all students who are seniors. 
Dim q1 = From currentStudent In students
         Where currentStudent.Year = "Senior" 
         Select currentStudent

' Write a For Each loop to execute the query. 
For Each q In q1
    Console.WriteLine(q.First & " " & q.Last)
Next 

' Find all students with a first name beginning with "C". 
Dim q2 = From currentStudent In students
         Where currentStudent.First.StartsWith("C")
         Select currentStudent

' Find all top ranked seniors (rank < 40). 
Dim q3 = From currentStudent In students
         Where currentStudent.Rank < 40 And currentStudent.Year = "Senior" 
         Select currentStudent

' Find all seniors with a lower rank than a student who  
' is not a senior. 
Dim q4 = From student1 In students, student2 In students
         Where student1.Year = "Senior" And student2.Year <> "Senior" And
               student1.Rank > student2.Rank
         Select student1
         Distinct

' Retrieve the full names of all students, sorted by last name. 
Dim q5 = From currentStudent In students
         Order By currentStudent.Last
         Select Name = currentStudent.First & " " & currentStudent.Last

' Determine how many students are ranked in the top 20. 
Dim q6 = Aggregate currentStudent In students
         Where currentStudent.Rank <= 20
         Into Count()

' Count the number of different last names in the group of students. 
Dim q7 = Aggregate currentStudent In students
         Select currentStudent.Last
         Distinct
         Into Count()

' Create a list box to show the last names of students. 
Dim lb As New System.Windows.Forms.ListBox
Dim q8 = From currentStudent In students
         Order By currentStudent.Last
         Select currentStudent.Last Distinct

For Each nextName As String In q8
    lb.Items.Add(nextName)
Next 

' Find every process that has a lowercase "h", "l", or "d" in its name. 
Dim letters() As String = {"h", "l", "d"}
Dim q9 = From proc In System.Diagnostics.Process.GetProcesses,
         letter In letters
         Where proc.ProcessName.Contains(letter)
         Select proc

For Each proc In q9
    Console.WriteLine(proc.ProcessName & ", " & proc.WorkingSet64)
Next

Additional Information

After you are familiar with the basic concepts of working with queries, you are ready to read the documentation and samples for the specific type of LINQ provider that you are interested in:

LINQ to Objects

LINQ to SQL [LINQ to SQL]

LINQ to XML [from BPUEDev11]

LINQ to DataSet

Partager via