Introduction to LINQ in Visual Basic

Language-Integrated Query (LINQ) adds query capabilities to Visual Basic and provides simple and powerful capabilities when you work with all kinds of data. Rather than sending a query to a database to be processed, or working with different query syntax for each type of data that you are searching, LINQ introduces queries as part of the Visual Basic language. It uses a unified syntax regardless of the type of data.

LINQ enables you to query data from a SQL Server database, XML, in-memory arrays and collections, ADO.NET datasets, or any other remote or local data source that supports LINQ. You can do all this with common Visual Basic language elements. Because your queries are written in the Visual Basic language, your query results are returned as strongly-typed objects. These objects support IntelliSense, which enables you to write code faster and catch errors in your queries at compile time instead of at run time. LINQ queries can be used as the source of additional queries to refine results. They can also be bound to controls so that users can easily view and modify your query results.

For example, the following code example shows a LINQ query that returns a list of customers from a collection and groups them based on their location.

' Obtain a list of customers. 
Dim customers As List(Of Customer) = GetCustomers()

' Return customers that are grouped based on country. 
Dim countries = From cust In customers
                Order By cust.Country, cust.City
                Group By CountryName = cust.Country
                Into CustomersInCountry = Group, Count()
                Order By CountryName

' Output the results. 
For Each country In countries
    Debug.WriteLine(country.CountryName & " count=" & country.Count)

    For Each customer In country.CustomersInCountry
        Debug.WriteLine("   " & customer.CompanyName & "  " & customer.City)
    Next 
Next 

' Output: 
'   Canada count=2 
'      Contoso, Ltd  Halifax 
'      Fabrikam, Inc.  Vancouver 
'   United States count=1 
'      Margie's Travel  Redmond

In this topic, you will find information about the following areas:

• Running the Examples

• LINQ Providers

• Structure of a LINQ Query

• Visual Basic LINQ Query Operators

• Connecting to a Database by Using LINQ to SQL

• Visual Basic Features That Support LINQ

• Deferred and Immediate Query Execution

• XML in Visual Basic

• Related Resources

• How To and Walkthrough Topics

Running the Examples

To run the examples in the introduction and in the "Structure of a LINQ Query" section, include the following code, which returns lists of customers and orders.

' Return a list of customers. 
Private Function GetCustomers() As List(Of Customer)
    Return New List(Of Customer) From
        {
            New Customer With {.CustomerID = 1, .CompanyName = "Contoso, Ltd", .City = "Halifax", .Country = "Canada"},
            New Customer With {.CustomerID = 2, .CompanyName = "Margie's Travel", .City = "Redmond", .Country = "United States"},
            New Customer With {.CustomerID = 3, .CompanyName = "Fabrikam, Inc.", .City = "Vancouver", .Country = "Canada"}
        }
End Function 

' Return a list of orders. 
Private Function GetOrders() As List(Of Order)
    Return New List(Of Order) From
        {
            New Order With {.CustomerID = 1, .Amount = "200.00"},
            New Order With {.CustomerID = 3, .Amount = "600.00"},
            New Order With {.CustomerID = 1, .Amount = "300.00"},
            New Order With {.CustomerID = 2, .Amount = "100.00"},
            New Order With {.CustomerID = 3, .Amount = "800.00"}
        }
End Function 

' Customer Class. 
Private Class Customer
    Public Property CustomerID As Integer 
    Public Property CompanyName As String 
    Public Property City As String 
    Public Property Country As String 
End Class 

' Order Class. 
Private Class Order
    Public Property CustomerID As Integer 
    Public Property Amount As Decimal 
End Class

LINQ Providers

A LINQ provider maps your Visual Basic LINQ queries to the data source being queried. When you write a LINQ query, the provider takes that query and translates it into commands that the data source will be able to execute. The provider also converts data from the source to the objects that make up your query result. Finally, it converts objects to data when you send updates to the data source.

Visual Basic includes the following LINQ providers.

Structure of a LINQ Query

A LINQ query, often referred to as a query expression, consists of a combination of query clauses that identify the data sources and iteration variables for the query. A query expression can also include instructions for sorting, filtering, grouping, and joining, or calculations to apply to the source data. Query expression syntax resembles the syntax of SQL; therefore, you may find much of the syntax familiar.

A query expression starts with a From clause. This clause identifies the source data for a query and the variables that are used to refer to each element of the source data individually. These variables are named range variables or iteration variables. The From clause is required for a query, except for Aggregate queries, where the From clause is optional. After the scope and source of the query are identified in the From or Aggregate clauses, you can include any combination of query clauses to refine the query. For details about query clauses, see Visual Basic LINQ Query Operators later in this topic. For example, the following query identifies a source collection of customer data as the customers variable, and an iteration variable named cust.

Dim customers = GetCustomers()

Dim queryResults = From cust In customers

For Each result In queryResults
    Debug.WriteLine(result.CompanyName & "  " & result.Country)
Next 

' Output: 
'   Contoso, Ltd  Canada 
'   Margie's Travel  United States 
'   Fabrikam, Inc.  Canada

This example is a valid query by itself; however, the query becomes far more powerful when you add more query clauses to refine the result. For example, you can add a Where clause to filter the result by one or more values. Query expressions are a single line of code; you can just append additional query clauses to the end of the query. You can break up a query across multiple lines of text to improve readability by using the underscore (_) line-continuation character. The following code example shows an example of a query that includes a Where clause.

Dim queryResults = From cust In customers
                   Where cust.Country = "Canada"

Another powerful query clause is the Select clause, which enables you to return only selected fields from the data source. LINQ queries return enumerable collections of strongly typed objects. A query can return a collection of anonymous types or named types. You can use the Select clause to return only a single field from the data source. When you do this, the type of the collection returned is the type of that single field. You can also use the Select clause to return multiple fields from the data source. When you do this, the type of the collection returned is a new anonymous type. You can also match the fields returned by the query to the fields of a specified named type. The following code example shows a query expression that returns a collection of anonymous types that have members populated with data from the selected fields from the data source.

Dim queryResults = From cust In customers
               Where cust.Country = "Canada" 
               Select cust.CompanyName, cust.Country

LINQ queries can also be used to combine multiple sources of data and return a single result. This can be done with one or more From clauses, or by using the Join or Group Join query clauses. The following code example shows a query expression that combines customer and order data and returns a collection of anonymous types containing customer and order data.

Dim customers = GetCustomers()
Dim orders = GetOrders()

Dim queryResults = From cust In customers, ord In orders
           Where cust.CustomerID = ord.CustomerID
           Select cust, ord

For Each result In queryResults
    Debug.WriteLine(result.ord.Amount & "  " & result.ord.CustomerID & "  " & result.cust.CompanyName)
Next 

' Output: 
'   200.00  1  Contoso, Ltd 
'   300.00  1  Contoso, Ltd 
'   100.00  2  Margie's Travel 
'   600.00  3  Fabrikam, Inc. 
'   800.00  3  Fabrikam, Inc.

You can use the Group Join clause to create a hierarchical query result that contains a collection of customer objects. Each customer object has a property that contains a collection of all orders for that customer. The following code example shows a query expression that combines customer and order data as a hierarchical result and returns a collection of anonymous types. The query returns a type that includes a CustomerOrders property that contains a collection of order data for the customer. It also includes an OrderTotal property that contains the sum of the totals for all the orders for that customer. (This query is equivalent to a LEFT OUTER JOIN.)

Dim customers = GetCustomers()
Dim orders = GetOrders()

Dim queryResults = From cust In customers
                   Group Join ord In orders On
                     cust.CustomerID Equals ord.CustomerID
                     Into CustomerOrders = Group,
                          OrderTotal = Sum(ord.Amount)
                   Select cust.CompanyName, cust.CustomerID,
                          CustomerOrders, OrderTotal

For Each result In queryResults
    Debug.WriteLine(result.OrderTotal & "  " & result.CustomerID & "  " & result.CompanyName)
    For Each ordResult In result.CustomerOrders
        Debug.WriteLine("   " & ordResult.Amount)
    Next 
Next 

' Output: 
'   500.00  1  Contoso, Ltd 
'      200.00 
'      300.00 
'   100.00  2  Margie's Travel 
'      100.00 
'   1400.00  3  Fabrikam, Inc. 
'      600.00 
'      800.00

There are several additional LINQ query operators that you can use to create powerful query expressions. The next section of this topic discusses the various query clauses that you can include in a query expression. For details about Visual Basic query clauses, see Queries (Visual Basic).

Visual Basic LINQ Query Operators

The classes in the System.Linq namespace and the other namespaces that support LINQ queries include methods that you can call to create and refine queries based on the needs of your application. Visual Basic includes keywords for the most common query clauses, as described by the following table.

' Returns the company name for all customers for which 
' the Country is equal to "Canada". 
Dim names = From cust In customers
            Where cust.Country = "Canada" 
            Select cust.CompanyName

' Returns the company name and ID value for each 
' customer as a collection of a new anonymous type. 
Dim customerList = From cust In customers
                   Select cust.CompanyName, cust.CustomerID

' Returns all product names for which the Category of 
' the product is "Beverages". 
Dim names = From product In products
            Where product.Category = "Beverages" 
            Select product.Name

' Returns a list of books sorted by price in  
' ascending order. 
Dim titlesAscendingPrice = From b In books
                           Order By b.price

' Returns a combined collection of all of the  
' processes currently running and a descriptive 
' name for the process taken from a list of  
' descriptive names. 
Dim processes = From proc In Process.GetProcesses
                Join desc In processDescriptions
                  On proc.ProcessName Equals desc.ProcessName
                Select proc.ProcessName, proc.Id, desc.Description

' Returns a list of orders grouped by the order date 
' and sorted in ascending order by the order date. 
Dim orderList = From order In orders
                Order By order.OrderDate
                Group By OrderDate = order.OrderDate
                Into OrdersByDate = Group

' Returns a combined collection of customers and 
' customer orders. 
Dim customerList = From cust In customers
                   Group Join ord In orders On
                     cust.CustomerID Equals ord.CustomerID
                   Into CustomerOrders = Group,
                        TotalOfOrders = Sum(ord.Amount)
                   Select cust.CompanyName, cust.CustomerID,
                          CustomerOrders, TotalOfOrders

' Returns the sum of all order amounts. 
Dim orderTotal = Aggregate order In orders
                 Into Sum(order.Amount)

' Returns the customer company name and largest  
' order amount for each customer. 
Dim customerMax = From cust In customers
                  Aggregate order In cust.Orders
                  Into MaxOrder = Max(order.Amount)
                  Select cust.CompanyName, MaxOrder

' Returns a list of products with a calculation of 
' a ten percent discount. 
Dim discountedProducts = From prod In products
                         Let Discount = prod.UnitPrice * 0.1
                         Where Discount >= 50
                         Select prod.Name, prod.UnitPrice, Discount

' Returns a list of cities with no duplicate entries. 
Dim cities = From item In customers
             Select item.City
             Distinct

' Returns a list of customers. The first 10 customers 
' are ignored and the remaining customers are 
' returned. 
Dim customerList = From cust In customers
                   Skip 10

' Returns a list of customers. The query ignores all 
' customers until the first customer for whom 
' IsSubscriber returns false. That customer and all 
' remaining customers are returned. 
Dim customerList = From cust In customers
                   Skip While IsSubscriber(cust)

' Returns the first 10 customers. 
Dim customerList = From cust In customers
                   Take 10

' Returns a list of customers. The query returns 
' customers until the first customer for whom  
' HasOrders returns false. That customer and all  
' remaining customers are ignored. 
Dim customersWithOrders = From cust In customers
                          Order By cust.Orders.Count Descending
                          Take While HasOrders(cust)

For details about Visual Basic query clauses, see Queries (Visual Basic).

You can use additional LINQ query features by calling members of the enumerable and queryable types provided by LINQ. You can use these additional capabilities by calling a particular query operator on the result of a query expression. For example, the following code example uses the Union method to combine the results of two queries into one query result. It uses the ToList<TSource> method to return the query result as a generic list.

Public Function GetAllCustomers() As List(Of Customer)
    Dim customers1 = From cust In domesticCustomers
    Dim customers2 = From cust In internationalCustomers

    Dim customerList = customers1.Union(customers2)

    Return customerList.ToList()
End Function

For details about additional LINQ capabilities, see Standard Query Operators Overview.

Connecting to a Database by Using LINQ to SQL

In Visual Basic, you identify the SQL Server database objects, such as tables, views, and stored procedures, that you want to access by using a LINQ to SQL file. A LINQ to SQL file has an extension of .dbml.

When you have a valid connection to a SQL Server database, you can add a LINQ to SQL Classes item template to your project. This will display the Object Relational Designer (O/R designer). The O/R Designer enables you to drag the items that you want to access in your code from the Server Explorer/Database Explorer onto the designer surface. The LINQ to SQL file adds a DataContext object to your project. This object includes properties and collections for the tables and views that you want access to, and methods for the stored procedures that you want to call. After you have saved your changes to the LINQ to SQL (.dbml) file, you can access these objects in your code by referencing the DataContext object that is defined by the O/R Designer. The DataContext object for your project is named based on the name of your LINQ to SQL file. For example, a LINQ to SQL file that is named Northwind.dbml will create a DataContext object named NorthwindDataContext.

For examples with step-by-step instructions, see How to: Query a Database by Using LINQ (Visual Basic) and How to: Call a Stored Procedure by Using LINQ (Visual Basic).

Visual Basic Features That Support LINQ

Visual Basic includes other notable features that make the use of LINQ simple and reduce the amount of code that you must write to perform LINQ queries. These include the following:

• Anonymous types, which enable you to create a new type based on a query result.

• Implicitly typed variables, which enable you to defer specifying a type and let the compiler infer the type based on the query result.

• Extension methods, which enable you to extend an existing type with your own methods without modifying the type itself.

For details, see Visual Basic Features That Support LINQ.

Deferred and Immediate Query Execution

Query execution is separate from creating a query. After a query is created, its execution is triggered by a separate 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).

By default, when you create a query, the query itself does not execute immediately. Instead, the query definition is stored in the variable that is used to reference the query result. When the query result variable is accessed later in code, such as in a For…Next loop, the query is executed. This process is referred to as deferred execution.

Queries can also be executed when they are defined, which is referred to as immediate execution. You can trigger immediate execution by applying a method that requires access to individual elements of the query result. This can be the result of including an aggregate function, such as Count, Sum, Average, Min, or Max. For more information about aggregate functions, see Aggregate Clause (Visual Basic).

Using the ToList or ToArray methods will also force immediate execution. This can be useful when you want to execute the query immediately and cache the results. For more information about these methods, see Converting Data Types.

For more information about query execution, see Writing Your First LINQ Query (Visual Basic).

XML in Visual Basic

The XML features in Visual Basic include XML literals and XML axis properties, which enable you easily to create, access, query, and modify XML in your code. XML literals enable you to write XML directly in your code. The Visual Basic compiler treats the XML as a first-class data object.

The following code example shows how to create an XML element, access its sub-elements and attributes, and query the contents of the element by using LINQ.

' Place Imports statements at the top of your program.   
Imports <xmlns:ns="http://SomeNamespace">

Module Sample1

    Sub SampleTransform()

        ' Create test by using a global XML namespace prefix.  

        Dim contact = 
            <ns:contact>
                <ns:name>Patrick Hines</ns:name>
                <ns:phone ns:type="home">206-555-0144</ns:phone>
                <ns:phone ns:type="work">425-555-0145</ns:phone>
            </ns:contact>

        Dim phoneTypes = 
          <phoneTypes>
              <%= From phone In contact.<ns:phone> 
                  Select <type><%= phone.@ns:type %></type> 
              %>
          </phoneTypes>

        Console.WriteLine(phoneTypes)
    End Sub 

End Module

For more information, see XML in Visual Basic.

Related Resources

How To and Walkthrough Topics

How to: Query a Database by Using LINQ (Visual Basic)

How to: Call a Stored Procedure by Using LINQ (Visual Basic)

How to: Modify Data in a Database by Using LINQ (Visual Basic)

How to: Combine Data with LINQ by Using Joins (Visual Basic)

How to: Sort Query Results by Using LINQ (Visual Basic)

How to: Filter Query Results by Using LINQ (Visual Basic)

How to: Count, Sum, or Average Data by Using LINQ (Visual Basic)

How to: Find the Minimum or Maximum Value in a Query Result by Using LINQ (Visual Basic)

Walkthrough: Creating LINQ to SQL Classes (O/R Designer)

How to: Assign Stored Procedures to Perform Updates, Inserts, and Deletes (O/R Designer)

Featured Book Chapters

Chapter 17: LINQ in Programming Visual Basic 2008

See Also

Tasks

LINQ Samples

Concepts

Overview of LINQ to XML in Visual Basic

LINQ to DataSet Overview

DataContext Methods (O/R Designer)

Other Resources

LINQ (Language-Integrated Query)

LINQ to SQL

Object Relational Designer (O/R Designer)