Writing Your First LINQ Query (Visual Basic)
A query is an expression that retrieves data from a data source. Queries are expressed in a dedicated query language. Over time, different languages have been developed for different types of data sources, for example, SQL for relational databases and XQuery for XML. This makes it necessary for the application developer to learn a new query language for each type of data source or data format that is supported.
Language-Integrated Query (LINQ) simplifies the situation by offering a consistent model for working with data across various kinds of data sources and formats. In a LINQ query, you are always working with objects. You use the same basic coding patterns to query and transform data in XML documents, SQL databases, ADO.NET datasets and entities, .NET Framework collections, and any other source or format for which a LINQ provider is available. This document describes the three phases of the creation and use of basic LINQ queries.
Three Stages of a Query Operation
LINQ query operations consist of three actions:
Obtain the data source or sources.
Create the query.
Execute the query.
In LINQ, the execution of a query is distinct from the creation of the query. You do not retrieve any data just by creating a query. This point is discussed in more detail later in this topic.
The following example illustrates the three parts of a query operation. The example uses an array of integers as a convenient data source for demonstration purposes. However, the same concepts also apply to other data sources.
Note
On the Compile Page, Project Designer (Visual Basic), ensure that Option infer is set to On.
' Data source.
Dim numbers() As Integer = {0, 1, 2, 3, 4, 5, 6}
' Query creation.
Dim evensQuery = From num In numbers
Where num Mod 2 = 0
Select num
' Query execution.
For Each number In evensQuery
Console.Write(number & " ")
Next
Output:
0 2 4 6
The Data Source
Because the data source in the previous example is an array, it implicitly supports the generic IEnumerable<T> interface. It is this fact that enables you to use an array as a data source for a LINQ query. Types that support IEnumerable<T> or a derived interface such as the generic IQueryable<T> are called queryable types.
As an implicitly queryable type, the array requires no modification or special treatment to serve as a LINQ data source. The same is true for any collection type that supports IEnumerable<T>, including the generic List<T>, Dictionary<TKey,TValue>, and other classes in the .NET Framework class library.
If the source data does not already implement IEnumerable<T>, a LINQ provider is needed to implement the functionality of the standard query operators for that data source. For example, LINQ to XML handles the work of loading an XML document into a queryable XElement type, as shown in the following example. For more information about standard query operators, see Standard Query Operators Overview (Visual Basic).
' Create a data source from an XML document.
Dim contacts = XElement.Load("c:\myContactList.xml")
With LINQ to SQL, you first create an object-relational mapping at design time, either manually or by using the LINQ to SQL Tools in Visual Studio in Visual Studio. You write your queries against the objects, and at run-time LINQ to SQL handles the communication with the database. In the following example, customers represents a specific table in the database, and Table<TEntity> supports generic IQueryable<T>.
' Create a data source from a SQL table.
Dim db As New DataContext("C:\Northwind\Northwnd.mdf")
Dim customers As Table(Of Customer) = db.GetTable(Of Customer)
For more information about how to create specific types of data sources, see the documentation for the various LINQ providers. (For a list of these providers, see LINQ (Language-Integrated Query).) The basic rule is simple: a LINQ data source is any object that supports the generic IEnumerable<T> interface, or an interface that inherits from it.
Note
Types such as ArrayList that support the non-generic IEnumerable interface can also be used as LINQ data sources. For an example that uses an ArrayList, see How to: Query an ArrayList with LINQ (Visual Basic).
The Query
In the query, you specify what information you want to retrieve from the data source or sources. You also have the option of specifying how that information should be sorted, grouped, or structured before it is returned. To enable query creation, Visual Basic has incorporated new query syntax into the language.
When it is executed, the query in the following example returns all the even numbers from an integer array, numbers.
' Data source.
Dim numbers() As Integer = {0, 1, 2, 3, 4, 5, 6}
' Query creation.
Dim evensQuery = From num In numbers
Where num Mod 2 = 0
Select num
' Query execution.
For Each number In evensQuery
Console.Write(number & " ")
Next
The query expression contains three clauses: From, Where, and Select. The specific function and purpose of each query expression clause is discussed in Basic Query Operations (Visual Basic). For more information, see Queries. Note that in LINQ, a query definition often is stored in a variable and executed later. The query variable, such as evensQuery in the previous example, must be a queryable type. The type of evensQuery is IEnumerable(Of Integer), assigned by the compiler using local type inference.
It is important to remember that the query variable itself takes no action and returns no data. It only stores the query definition. In the previous example, it is the For Each loop that executes the query.
Query Execution
Query execution is separate from query creation. Query creation defines the query, but execution is triggered by a different mechanism. A query can be executed as soon as it is defined (immediate execution), or the definition can be stored and the query can be executed later (deferred execution).
Deferred Execution
A typical LINQ query resembles the one in the previous example, in which evensQuery is defined. It creates the query but does not execute it immediately. Instead, the query definition is stored in the query variable evensQuery. You execute the query later, typically by using a For Each loop, which returns a sequence of values, or by applying a standard query operator, such as Count or Max. This process is referred to as deferred execution.
' Query execution that results in a sequence of values.
For Each number In evensQuery
Console.Write(number & " ")
Next
' Query execution that results in a single value.
Dim evens = evensQuery.Count()
For a sequence of values, you access the retrieved data by using the iteration variable in the For Each loop (number in the previous example). Because the query variable, evensQuery, holds the query definition rather than the query results, you can execute a query as often as you want by using the query variable more than one time. For example, you might have a database in your application that is being updated continually by a separate application. After you have created a query that retrieves data from that database, you can use a For Each loop to execute the query repeatedly, retrieving the most recent data every time.
The following example demonstrates how deferred execution works. After evensQuery2 is defined and executed with a For Each loop, as in the previous examples, some elements in the data source numbers are changed. Then a second For Each loop runs evensQuery2 again. The results are different the second time, because the For Each loop executes the query again, using the new values in numbers.
Dim numberArray() = {0, 1, 2, 3, 4, 5, 6}
Dim evensQuery2 = From num In numberArray
Where num Mod 2 = 0
Select num
Console.WriteLine("Evens in original array:")
For Each number In evensQuery2
Console.Write(" " & number)
Next
Console.WriteLine()
' Change a few array elements.
numberArray(1) = 10
numberArray(4) = 22
numberArray(6) = 8
' Run the same query again.
Console.WriteLine(vbCrLf & "Evens in changed array:")
For Each number In evensQuery2
Console.Write(" " & number)
Next
Console.WriteLine()
Output:
Evens in original array:
0 2 4 6
Evens in changed array:
0 10 2 22 8
Immediate Execution
In deferred execution of queries, the query definition is stored in a query variable for later execution. In immediate execution, the query is executed at the time of its definition. Execution is triggered when you apply a method that requires access to individual elements of the query result. Immediate execution often is forced by using one of the standard query operators that return single values. Examples are Count, Max, Average, and First. These standard query operators execute the query as soon as they are applied in order to calculate and return a singleton result. For more information about standard query operators that return single values, see Aggregation Operations, Element Operations, and Quantifier Operations.
The following query returns a count of the even numbers in an array of integers. The query definition is not saved, and numEvens is a simple Integer.
Dim numEvens = (From num In numbers
Where num Mod 2 = 0
Select num).Count()
You can achieve the same result by using the Aggregate method.
Dim numEvensAgg = Aggregate num In numbers
Where num Mod 2 = 0
Select num
Into Count()
You can also force execution of a query by calling the ToList or ToArray method on a query (immediate) or query variable (deferred), as shown in the following code.
' Immediate execution.
Dim evensList = (From num In numbers
Where num Mod 2 = 0
Select num).ToList()
' Deferred execution.
Dim evensQuery3 = From num In numbers
Where num Mod 2 = 0
Select num
' . . .
Dim evensArray = evensQuery3.ToArray()
In the previous examples, evensQuery3 is a query variable, but evensList is a list and evensArray is an array.
Using ToList or ToArray to force immediate execution is especially useful in scenarios in which you want to execute the query immediately and cache the results in a single collection object. For more information about these methods, see Converting Data Types.
You can also cause a query to be executed by using an IEnumerable method such as the IEnumerable.GetEnumerator method.
