Basic Query Operations (Visual Basic)

Artikkeli
09/15/2021

This topic provides a brief introduction to Language-Integrated Query (LINQ) expressions in Visual Basic, and to some of the typical kinds of operations that you perform in a query. For more information, see the following topics:

Introduction to LINQ in Visual Basic

Queries

Walkthrough: Writing Queries in Visual Basic

Specifying the Data Source (From)

In a LINQ query, the first step is to specify the data source that you want to query. Therefore, the From clause in a query always comes first. Query operators select and shape the result based on the type of the source.

Dim query = From cust In customers
'           ...

The From clause specifies the data source, customers, and a range variable, cust. The range variable is like a loop iteration variable, except that in a query expression, no actual iteration occurs. When the query is executed, often by using a For Each loop, the range variable serves as a reference to each successive element in customers. Because the compiler can infer the type of cust, you do not have to specify it explicitly. For examples of queries written with and without explicit typing, see Type Relationships in Query Operations (Visual Basic).

For more information about how to use the From clause in Visual Basic, see From Clause.

Filtering Data (Where)

Probably the most common query operation is applying a filter in the form of a Boolean expression. The query then returns only those elements for which the expression is true. A Where clause is used to perform the filtering. The filter specifies which elements in the data source to include in the resulting sequence. In the following example, only those customers who have an address in London are included.

Dim londonCusts = From cust In customers
                  Where cust.City = "London"
'                 ...

You can use logical operators such as And and Or to combine filter expressions in a Where clause. For example, to return only those customers who are from London and whose name is Devon, use the following code:

Where cust.City = "London" And cust.Name = "Devon"

To return customers from London or Paris, use the following code:

Where cust.City = "London" Or cust.City = "Paris"

For more information about how to use the Where clause in Visual Basic, see Where Clause.

Ordering Data (Order By)

It often is convenient to sort returned data into a particular order. The Order By clause will cause the elements in the returned sequence to be sorted on a specified field or fields. For example, the following query sorts the results based on the Name property. Because Name is a string, the returned data will be sorted alphabetically, from A to Z.

Dim londonCusts1 = From cust In customers
                   Where cust.City = "London"
                   Order By cust.Name Ascending
'                   ...

To order the results in reverse order, from Z to A, use the Order By...Descending clause. The default is Ascending when neither Ascending nor Descending is specified.

For more information about how to use the Order By clause in Visual Basic, see Order By Clause.

Selecting Data (Select)

The Select clause specifies the form and content of returned elements. For example, you can specify whether your results will consist of complete Customer objects, just one Customer property, a subset of properties, a combination of properties from various data sources, or some new result type based on a computation. When the Select clause produces something other than a copy of the source element, the operation is called a projection.

To retrieve a collection that consists of complete Customer objects, select the range variable itself:

Dim londonCusts2 = From cust In customers
                   Where cust.City = "London"
                   Order By cust.Name Ascending
                   Select cust

If a Customer instance is a large object that has many fields, and all that you want to retrieve is the name, you can select cust.Name, as shown in the following example. Local type inference recognizes that this changes the result type from a collection of Customer objects to a collection of strings.

Dim londonCusts3 = From cust In customers
                   Where cust.City = "London"
                   Order By cust.Name Ascending
                   Select cust.Name

To select multiple fields from the data source, you have two choices:

In the Select clause, specify the fields you want to include in the result. The compiler will define an anonymous type that has those fields as its properties. For more information, see Anonymous Types.

Because the returned elements in the following example are instances of an anonymous type, you cannot refer to the type by name elsewhere in your code. The compiler-designated name for the type contains characters that are not valid in normal Visual Basic code. In the following example, the elements in the collection that is returned by the query in londonCusts4 are instances of an anonymous type
```
Dim londonCusts4 = From cust In customers
                   Where cust.City = "London"
                   Order By cust.Name Ascending
                   Select Name = cust.Name, Phone = cust.Phone

For Each londonCust In londonCusts4
    Console.WriteLine(londonCust.Name & " " & londonCust.Phone)
Next
```
-or-
Define a named type that contains the particular fields that you want to include in the result, and create and initialize instances of the type in the Select clause. Use this option only if you have to use individual results outside the collection in which they are returned, or if you have to pass them as parameters in method calls. The type of londonCusts5 in the following example is IEnumerable(Of NamePhone).
```
Public Class NamePhone
    Public Name As String
    Public Phone As String
    ' Additional class elements
End Class
```
```
Dim londonCusts5 = From cust In customers
                   Where cust.City = "London"
                   Order By cust.Name Ascending
                   Select New NamePhone With {.Name = cust.Name,
                                              .Phone = cust.Phone}
```

For more information about how to use the Select clause in Visual Basic, see Select Clause.

Joining Data (Join and Group Join)

You can combine more than one data source in the From clause in several ways. For example, the following code uses two data sources and implicitly combines properties from both of them in the result. The query selects students whose last names start with a vowel.

Dim vowels() As String = {"A", "E", "I", "O", "U"}
Dim vowelNames = From student In students, vowel In vowels
                 Where student.Last.IndexOf(vowel) = 0
                 Select Name = student.First & " " &
                 student.Last, Initial = vowel
                 Order By Initial

For Each vName In vowelNames
    Console.WriteLine(vName.Initial & ":  " & vName.Name)
Next

Note

You can run this code with the list of students created in How to: Create a List of Items.

The Join keyword is equivalent to an INNER JOIN in SQL. It combines two collections based on matching key values between elements in the two collections. The query returns all or part of the collection elements that have matching key values. For example, the following code duplicates the action of the previous implicit join.

Dim vowelNames2 = From student In students
                  Join vowel In vowels
                  On student.Last(0) Equals vowel
                  Select Name = student.First & " " &
                  student.Last, Initial = vowel
                  Order By Initial

Group Join combines collections into a single hierarchical collection, just like a LEFT JOIN in SQL. For more information, see Join Clause and Group Join Clause.

Grouping Data (Group By)

You can add a Group By clause to group the elements in a query result according to one or more fields of the elements. For example, the following code groups students by class year.

Dim studentsByYear = From student In students
                     Select student
                     Group By year = student.Year
                     Into Classes = Group

For Each yearGroup In studentsByYear
    Console.WriteLine(vbCrLf & "Year: " & yearGroup.year)
    For Each student In yearGroup.Classes
        Console.WriteLine("   " & student.Last & ", " & student.First)
    Next
Next

If you run this code using the list of students created in How to: Create a List of Items, the output from the For Each statement is:

Year: Junior

Tucker, Michael

Garcia, Hugo

Garcia, Debra

Tucker, Lance

Year: Senior

Omelchenko, Svetlana

Osada, Michiko

Fakhouri, Fadi

Feng, Hanying

Adams, Terry

Year: Freshman

Mortensen, Sven

Garcia, Cesar

The variation shown in the following code orders the class years, and then orders the students within each year by last name.

Dim studentsByYear2 = From student In students
                      Select student
                      Order By student.Year, student.Last
                      Group By year = student.Year
                      Into Classes = Group

For more information about Group By, see Group By Clause.

Jaa