Sideway
output.to from Sideway
Draft for Information Only

Content

VB.NET LINQ
  Introduction to LINQ in Visual Basic
 Running the examples
 LINQ providers
 Structure of a LINQ query
 Visual Basic LINQ query operators
  From clause
  Select clause
  Where clause
  Order By clause]
  Join clause
  Group By clause
  Group Join clause
  Aggregate clause
  Let clause
  Distinct clause
  Skip clause
  Skip While clause
  Take clause
  Take While clause
 Use additional LINQ query features
 Connect 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
 Featured book chapters
 See also
  How to: Query a Database by Using LINQ
 To create a connection to a database
 To add a project that contains a LINQ to SQL file
 To add tables to query to the O/R Designer
 To add code to query the database and display the results
 See also
  How to: Call a Stored Procedure by Using LINQ
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add stored procedures to the O/R Designer
  To add code to display the results of the stored procedures
 See also
  How to: Modify Data in a Database by Using LINQ
  To create a connection to a database
  To add a Project with a LINQ to SQL file
  To add tables to query and modify to the designer
  To add code to modify the database and display the results
 See also
  How to: Combine Data with LINQ by Using Joins
 Create a Project and Add Sample Data
   To create a project that contains sample data and types
 Perform an Inner Join by Using the Join Clause
   To perform an Inner Join by using the Join clause
 Perform a Left Outer Join by Using the Group Join Clause
   To perform a Left Outer Join by using the Group Join clause
 Perform a Join by Using a Composite Key
   To perform a Join by using a composite key
 Run the Code
   To add code to run the examples
 See also
  How to: Sort Query Results by Using LINQ
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add tables to query to the O/R Designer
  To add code to query the database and display the results
 See also
  How to: Filter Query Results by Using LINQ
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add tables to query to the O/R Designer
  To add code to query the database and display the results
 See also
  How to: Count, Sum, or Average Data by Using LINQ
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add tables to query to the O/R Designer
  To add code to query the database and display the results
 See also
  How to: Find the Minimum or Maximum Value in a Query Result by Using LINQ
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add tables to query to the O/R Designer
  To add code to query the database and display the results
 See also
  How to: Return a LINQ Query Result as a Specific Type
  To create a connection to a database
  To add a project that contains a LINQ to SQL file
  To add tables to query to the O/R Designer
  To add code to query the database and display the results
 See also
  Source/Reference

VB.NET LINQ

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.

VB
' 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

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.

VB
' 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.

Provider Description
LINQ to Objects The LINQ to Objects provider enables you to query in-memory collections and arrays. If an object supports either the IEnumerable or IEnumerable<T> interface, the LINQ to Objects provider enables you to query it.

You can enable the LINQ to Objects provider by importing the System.Linq namespace, which is imported by default for all Visual Basic projects.

For more information about the LINQ to Objects provider, see LINQ to Objects.
LINQ to SQL The LINQ to SQL provider enables you to query and modify data in a SQL Server database. This makes it easy to map the object model for an application to the tables and objects in a database.

Visual Basic makes it easier to work with LINQ to SQL by including the Object Relational Designer (O/R Designer). This designer is used to create an object model in an application that maps to objects in a database. The O/R Designer also provides functionality to map stored procedures and functions to the DataContext object, which manages communication with the database and stores state for optimistic concurrency checks.

For more information about the LINQ to SQL provider, see LINQ to SQL. For more information about the Object Relational Designer, see LINQ to SQL Tools in Visual Studio.
LINQ to XML The LINQ to XML provider enables you to query and modify XML. You can modify in-memory XML, or you can load XML from and save XML to a file.

Additionally, the LINQ to XML provider enables XML literals and XML axis properties that enable you to write XML directly in your Visual Basic code. For more information, see XML.
LINQ to DataSet The LINQ to DataSet provider enables you to query and update data in an ADO.NET dataset. You can add the power of LINQ to applications that use datasets in order to simplify and extend your capabilities for querying, aggregating, and updating the data in your dataset.

For more information, see LINQ to DataSet.

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.

VB
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.

VB
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.

VB
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.

VB
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.)

VB
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 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 following common query clauses. For details about Visual Basic query clauses, see Queries.

From clause

Either a From clause or an Aggregate clause is required to begin a query. A From clause specifies a source collection and an iteration variable for a query. For example:

VB
' 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

Select clause

Optional. A Select clause declares a set of iteration variables for a query. For example:

VB
' 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

If a Select clause is not specified, the iteration variables for the query consist of the iteration variables specified by the From or Aggregate clause.

Where clause

Optional. A Where clause specifies a filtering condition for a query. For example:

VB
' 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

Order By clause]

|Optional. An Order By clause specifies the sort order for columns in a query. For example:

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

Join clause

Optional. A Join clause combines two collections into a single collection. For example:

VB
' 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

Group By clause

Optional. A Group By clause groups the elements of a query result. It can be used to apply aggregate functions to each group. For example:

VB
' 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

Group Join clause

Optional. A Group Join clause combines two collections into a single hierarchical collection. For example:

VB
' 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

Aggregate clause

Either an Aggregate clause or a From clause is required to begin a query. An Aggregate clause applies one or more aggregate functions to a collection. For example, you can use the Aggregate clause to calculate a sum for all the elements returned by a query, as the following example does.

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

You can also use the Aggregate clause to modify a query. For example, you can use the Aggregate clause to perform a calculation on a related query collection. For example:

VB
' 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

Let clause

Optional. A Let clause computes a value and assigns it to a new variable in the query. For example:

VB
' 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

Distinct clause

Optional. A Distinct clause restricts the values of the current iteration variable to eliminate duplicate values in query results. For example:

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

Skip clause

Optional. A Skip clause bypasses a specified number of elements in a collection and then returns the remaining elements. For example:

VB
' 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

Skip While clause

Optional. A Skip While clause bypasses elements in a collection as long as a specified condition is true and then returns the remaining elements. For example:

VB
' 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)

Take clause

Optional. A Take clause returns a specified number of contiguous elements from the start of a collection. For example:

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

Take While clause

Optional. A Take While clause includes elements in a collection as long as a specified condition is true and bypasses the remaining elements. For example:

VB
' 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)

Use additional LINQ query features

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 example uses the Enumerable.Union method to combine the results of two queries into one query result. It uses the Enumerable.ToList method to return the query result as a generic list.

VB
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.

Connect 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 and How to: Call a Stored Procedure.

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.

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.

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.

VB
' 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.

Related resources

Topic Description
XML Describes the XML features in Visual Basic that can be queried and that enable you to include XML as first-class data objects in your Visual Basic code.
Queries Provides reference information about the query clauses that are available in Visual Basic.
LINQ (Language-Integrated Query) Includes general information, programming guidance, and samples for LINQ.
LINQ to SQL Includes general information, programming guidance, and samples for LINQ to SQL.
LINQ to Objects Includes general information, programming guidance, and samples for LINQ to Objects.
LINQ to ADO.NET (Portal Page) Includes links to general information, programming guidance, and samples for LINQ to ADO.NET.
LINQ to XML Includes general information, programming guidance, and samples for LINQ to XML.

How to and walkthrough topics

How to: Query a Database

How to: Call a Stored Procedure

How to: Modify Data in a Database

How to: Combine Data with Joins

How to: Sort Query Results

How to: Filter Query Results

How to: Count, Sum, or Average Data

How to: Find the Minimum or Maximum Value in a Query Result

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

How to: Query a Database by Using LINQ

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

The following example shows how to create a new application that performs queries against a SQL Server database.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

    The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to the Load event of the form.

  3. When you added tables to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those tables, in addition to individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    Add the following code to the Load event to query the tables that are exposed as properties of your data context.

    VB
    Dim db As New northwindDataContext
    
    Dim londonCusts = From cust In db.Customers
                      Where cust.City = "London"
                      Select cust
    
    DataGridView1.DataSource = londonCusts
    
  4. Press F5 to run your project and view the results.

  5. Following are some additional queries that you can try:

    VB
    Dim londonCustOrders = From cust In db.Customers,
                                ord In cust.Orders
                           Where cust.City = "London"
                           Order By ord.OrderID
                           Select cust.City, ord.OrderID, ord.OrderDate
    
    DataGridView1.DataSource = londonCustOrders
    
    VB
    Dim custs = From cust In db.Customers 
                Where cust.Country = "France" And
                    (cust.CompanyName.StartsWith("F") Or
                     cust.CompanyName.StartsWith("V"))
                Order By cust.CompanyName
                Select cust.CompanyName, cust.City
    
    DataGridView1.DataSource = custs
    

See also

How to: Call a Stored Procedure by Using LINQ

Language-Integrated Query (LINQ) makes it easy to access database information, including database objects such as stored procedures.

The following example shows how to create an application that calls a stored procedure in a SQL Server database. The sample shows how to call two different stored procedures in the database. Each procedure returns the results of a query. One procedure takes input parameters, and the other procedure does not take parameters.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add stored procedures to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Stored Procedures folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Sales by Year stored procedure and drag it to the right pane of the designer. Click the Ten Most Expensive Products stored procedure drag it to the right pane of the designer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to display the results of the stored procedures

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to its Load event.

  3. When you added stored procedures to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those procedures. The DataContext object for the project is named based on the name of the .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and call the stored procedure methods specified by the O/R Designer. To bind to the DataGridView object, you may have to force the query to execute immediately by calling the ToList method on the results of the stored procedure.

    Add the following code to the Load event to call either of the stored procedures exposed as methods for your data context.

    VB
    Dim db As New northwindDataContext
    
    ' Display the results of the Sales_by_Year stored procedure.
    DataGridView1.DataSource = 
        db.Sales_by_Year(#1/1/1996#, #1/1/2007#).ToList()
    
    VB
    ' Display the results of the Ten_Most_Expensive_Products
    ' stored procedure.
    
    DataGridView1.DataSource = 
        db.Ten_Most_Expensive_Products.ToList()
    
  4. Press F5 to run your project and view the results.

See also

How to: Modify Data in a Database by Using LINQ

Language-Integrated Query (LINQ) queries make it easy to access database information and modify values in the database.

The following example shows how to create a new application that retrieves and updates information in a SQL Server database.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking the View menu, and then select Server Explorer/Database Explorer.

  2. Right-click Data Connections in Server Explorer/Database Explorer, and click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a Project with a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query and modify to the designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer.

    The designer creates a new Customer object for your project.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to modify the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. When you added tables to the O/R Designer, the designer added a DataContext object to your project. This object contains code that you can use to access the Customers table. It also contains code that defines a local Customer object and a Customers collection for the table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext object in your code and query and modify the Customers collection specified by the O/R Designer. Changes that you make to the Customers collection are not reflected in the database until you submit them by calling the SubmitChanges method of the DataContext object.

    Double-click the Windows Form, Form1, to add code to the Load event to query the Customers table that is exposed as a property of your DataContext. Add the following code:

    VB
    Private db As northwindDataContext  
    
    Private Sub Form1_Load(ByVal sender As System.Object,   
                           ByVal e As System.EventArgs  
                          ) Handles MyBase.Load  
      db = New northwindDataContext()  
    
      RefreshData()  
    End Sub  
    
    Private Sub RefreshData()  
      Dim customers = From cust In db.Customers   
                      Where cust.City(0) = "W"   
                      Select cust  
    
      DataGridView1.DataSource = customers  
    End Sub  
    
  3. From the Toolbox, drag three Button controls onto the form. Select the first Button control. In the Properties window, set the Name of the Button control to AddButton and the Text to Add. Select the second button and set the Name property to UpdateButton and the Text property to Update. Select the third button and set the Name property to DeleteButton and the Text property to Delete.

  4. Double-click the Add button to add code to its Click event. Add the following code:

    VB
    Private Sub AddButton_Click(ByVal sender As System.Object,   
                                ByVal e As System.EventArgs  
                               ) Handles AddButton.Click  
      Dim cust As New Customer With {   
        .City = "Wellington",   
        .CompanyName = "Blue Yonder Airlines",   
        .ContactName = "Jill Frank",   
        .Country = "New Zealand",   
        .CustomerID = "JILLF"}  
    
      db.Customers.InsertOnSubmit(cust)  
    
      Try  
        db.SubmitChanges()  
      Catch  
        ' Handle exception.  
      End Try  
    
      RefreshData()  
    End Sub  
    
  5. Double-click the Update button to add code to its Click event. Add the following code:

    VB
    Private Sub UpdateButton_Click(ByVal sender As System.Object, _  
                                   ByVal e As System.EventArgs  
                                  ) Handles UpdateButton.Click  
      Dim updateCust = (From cust In db.Customers   
                        Where cust.CustomerID = "JILLF").ToList()(0)  
    
      updateCust.ContactName = "Jill Shrader"
      updateCust.Country = "Wales"
      updateCust.CompanyName = "Red Yonder Airlines"
      updateCust.City = "Cardiff"
    
      Try  
        db.SubmitChanges()  
      Catch  
        ' Handle exception.  
      End Try  
    
      RefreshData()  
    End Sub  
    
  6. Double-click the Delete button to add code to its Click event. Add the following code:

    VB
    Private Sub DeleteButton_Click(ByVal sender As System.Object, _  
                                   ByVal e As System.EventArgs  
                                  ) Handles DeleteButton.Click  
      Dim deleteCust = (From cust In db.Customers   
                        Where cust.CustomerID = "JILLF").ToList()(0)  
    
      db.Customers.DeleteOnSubmit(deleteCust)  
    
      Try  
        db.SubmitChanges()  
      Catch  
        ' Handle exception.  
      End Try  
    
      RefreshData()  
    End Sub  
    
  7. Press F5 to run your project. Click Add to add a new record. Click Update to modify the new record. Click Delete to delete the new record.

See also

How to: Combine Data with LINQ by Using Joins

Visual Basic provides the Join and Group Join query clauses to enable you to combine the contents of multiple collections based on common values between the collections. These values are known as key values. Developers familiar with relational database concepts will recognize the Join clause as an INNER JOIN and the Group Join clause as, effectively, a LEFT OUTER JOIN.

The examples in this topic demonstrate a few ways to combine data by using the Join and Group Join query clauses.

Create a Project and Add Sample Data

To create a project that contains sample data and types

  1. To run the samples in this topic, open Visual Studio and add a new Visual Basic Console Application project. Double-click the Module1.vb file created by Visual Basic.

  2. The samples in this topic use the Person and Pet types and data from the following code example. Copy this code into the default Module1 module created by Visual Basic.

    VB
    Private _people As List(Of Person)
    Private _pets As List(Of Pet)
    
    Function GetPeople() As List(Of Person)
        If _people Is Nothing Then CreateLists()
        Return _people
    End Function
    
    Function GetPets(ByVal people As List(Of Person)) As List(Of Pet)
        If _pets Is Nothing Then CreateLists()
        Return _pets
    End Function
    
    Private Sub CreateLists()
        Dim pers As Person
    
        _people = New List(Of Person)
        _pets = New List(Of Pet)
    
        pers = New Person With {.FirstName = "Magnus", .LastName = "Hedlund"}
        _people.Add(pers)
        _pets.Add(New Pet With {.Name = "Daisy", .Owner = pers})
    
        pers = New Person With {.FirstName = "Terry", .LastName = "Adams"}
        _people.Add(pers)
        _pets.Add(New Pet With {.Name = "Barley", .Owner = pers})
        _pets.Add(New Pet With {.Name = "Boots", .Owner = pers})
        _pets.Add(New Pet With {.Name = "Blue Moon", .Owner = pers})
    
        pers = New Person With {.FirstName = "Charlotte", .LastName = "Weiss"}
        _people.Add(pers)
        _pets.Add(New Pet With {.Name = "Whiskers", .Owner = pers})
    
        ' Add a person with no pets for the sake of Join examples.
        _people.Add(New Person With {.FirstName = "Arlene", .LastName = "Huff"})
    
        pers = New Person With {.FirstName = "Don", .LastName = "Hall"}
        ' Do not add person to people list for the sake of Join examples.
        _pets.Add(New Pet With {.Name = "Spot", .Owner = pers})
    
        ' Add a pet with no owner for the sake of Join examples.
        _pets.Add(New Pet With {.Name = "Unknown", 
                                .Owner = New Person With {.FirstName = String.Empty, 
                                                          .LastName = String.Empty}})
    End Sub
    
    VB
    Class Person
        Public Property FirstName As String
        Public Property LastName As String
    End Class
    
    Class Pet
        Public Property Name As String
        Public Property Owner As Person
    End Class
    

Perform an Inner Join by Using the Join Clause

An INNER JOIN combines data from two collections. Items for which the specified key values match are included. Any items from either collection that do not have a matching item in the other collection are excluded.

In Visual Basic, LINQ provides two options for performing an INNER JOIN: an implicit join and an explicit join.

An implicit join specifies the collections to be joined in a From clause and identifies the matching key fields in a Where clause. Visual Basic implicitly joins the two collections based on the specified key fields.

You can specify an explicit join by using the Join clause when you want to be specific about which key fields to use in the join. In this case, a Where clause can still be used to filter the query results.

To perform an Inner Join by using the Join clause

  1. Add the following code to the Module1 module in your project to see examples of both an implicit and explicit inner join.

    VB
    Sub InnerJoinExample()
        ' Create two lists.
        Dim people = GetPeople()
        Dim pets = GetPets(people)
    
        ' Implicit Join.
        Dim petOwners = From pers In people, pet In pets
                        Where pet.Owner Is pers
                        Select pers.FirstName, PetName = pet.Name
    
        ' Display grouped results.
        Dim output As New System.Text.StringBuilder
        For Each pers In petOwners
            output.AppendFormat(
              pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
        Next
    
        Console.WriteLine(output)
    
        ' Explicit Join.
        Dim petOwnersJoin = From pers In people
                            Join pet In pets
                            On pet.Owner Equals pers
                            Select pers.FirstName, PetName = pet.Name
    
        ' Display grouped results.
        output = New System.Text.StringBuilder()
        For Each pers In petOwnersJoin
            output.AppendFormat(
              pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
        Next
    
        Console.WriteLine(output)
    
        ' Both queries produce the following output:
        '
        ' Magnus:    Daisy
        ' Terry:     Barley
        ' Terry:     Boots
        ' Terry:     Blue Moon
        ' Charlotte: Whiskers
    End Sub
    

Perform a Left Outer Join by Using the Group Join Clause

A LEFT OUTER JOIN includes all the items from the left-side collection of the join and only matching values from the right-side collection of the join. Any items from the right-side collection of the join that do not have a matching item in the left-side collection are excluded from the query result.

The Group Join clause performs, in effect, a LEFT OUTER JOIN. The difference between what is typically known as a LEFT OUTER JOIN and what the Group Join clause returns is that the Group Join clause groups results from the right-side collection of the join for each item in the left-side collection. In a relational database, a LEFT OUTER JOIN returns an ungrouped result in which each item in the query result contains matching items from both collections in the join. In this case, the items from the left-side collection of the join are repeated for each matching item from the right-side collection. You will see what this looks like when you complete the next procedure.

You can retrieve the results of a Group Join query as an ungrouped result by extending your query to return an item for each grouped query result. To accomplish this, you have to ensure that you query on the DefaultIfEmpty method of the grouped collection. This ensures that items from the left-side collection of the join are still included in the query result even if they have no matching results from the right-side collection. You can add code to your query to provide a default result value when there is no matching value from the right-side collection of the join.

To perform a Left Outer Join by using the Group Join clause

  1. Add the following code to the Module1 module in your project to see examples of both a grouped left outer join and an ungrouped left outer join.

    VB
    Sub LeftOuterJoinExample()
        ' Create two lists.
        Dim people = GetPeople()
        Dim pets = GetPets(people)
    
        ' Grouped results.
        Dim petOwnersGrouped = From pers In people
                               Group Join pet In pets
                                 On pers Equals pet.Owner
                               Into PetList = Group
                               Select pers.FirstName, pers.LastName,
                                      PetList
    
        ' Display grouped results.
        Dim output As New System.Text.StringBuilder
        For Each pers In petOwnersGrouped
            output.AppendFormat(pers.FirstName & ":" & vbCrLf)
            For Each pt In pers.PetList
                output.AppendFormat(vbTab & pt.Name & vbCrLf)
            Next
        Next
    
        Console.WriteLine(output)
        ' This code produces the following output:
        '
        ' Magnus:
        '     Daisy
        ' Terry:
        '     Barley
        '     Boots
        '     Blue Moon
        ' Charlotte:
        '     Whiskers
        ' Arlene:
    
        ' "Flat" results.
        Dim petOwners = From pers In people
                        Group Join pet In pets On pers Equals pet.Owner
                        Into PetList = Group
                        From pet In PetList.DefaultIfEmpty()
                        Select pers.FirstName, pers.LastName,
                               PetName =
                                 If(pet Is Nothing, String.Empty, pet.Name)
    
    
        ' Display "flat" results.
        output = New System.Text.StringBuilder()
        For Each pers In petOwners
            output.AppendFormat( 
              pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
        Next
    
        Console.WriteLine(output.ToString())
        ' This code produces the following output:
        '
        ' Magnus:	    Daisy
        ' Terry:	    Barley
        ' Terry:	    Boots
        ' Terry:	    Blue Moon
        ' Charlotte:	Whiskers
        ' Arlene:	  
    End Sub
    

Perform a Join by Using a Composite Key

You can use the And keyword in a Join or Group Join clause to identify multiple key fields to use when matching values from the collections being joined. The And keyword specifies that all specified key fields must match for items to be joined.

To perform a Join by using a composite key

  1. Add the following code to the Module1 module in your project to see examples of a join that uses a composite key.

    VB
    Sub CompositeKeyJoinExample()
        ' Create two lists.
        Dim people = GetPeople()
        Dim pets = GetPets(people)
    
        ' Implicit Join.
        Dim petOwners = From pers In people
                        Join pet In pets On
                          pet.Owner.FirstName Equals pers.FirstName And
                          pet.Owner.LastName Equals pers.LastName
                    Select pers.FirstName, PetName = pet.Name
    
        ' Display grouped results.
        Dim output As New System.Text.StringBuilder
        For Each pers In petOwners
            output.AppendFormat(
              pers.FirstName & ":" & vbTab & pers.PetName & vbCrLf)
        Next
    
        Console.WriteLine(output)
        ' This code produces the following output:
        '
        ' Magnus:    Daisy
        ' Terry:     Barley
        ' Terry:     Boots
        ' Terry:     Blue Moon
        ' Charlotte: Whiskers
    End Sub
    

Run the Code

To add code to run the examples

  1. Replace the Sub Main in the Module1 module in your project with the following code to run the examples in this topic.

    VB
    Sub Main()
        InnerJoinExample()
        LeftOuterJoinExample()
        CompositeKeyJoinExample()
    
        Console.ReadLine()
    End Sub
    
  2. Press F5 to run the examples.

See also

How to: Sort Query Results by Using LINQ

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

The following example shows how to create a new application that performs queries against a SQL Server database and sorts the results by multiple fields by using the Order By clause. The sort order for each field can be ascending order or descending order. For more information, see Order By Clause.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

    The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to the Load event of the form.

  3. When you added tables to the O/R Designer, the designer added a DataContext object to your project. This object contains the code that you must have to access those tables, and to access individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    Add the following code to the Load event to query the tables that are exposed as properties of your data context and sort the results. The query sorts the results by the number of customer orders, in descending order. Customers that have the same number of orders are ordered by company name in ascending order (the default).

    VB
    Dim db As New northwindDataContext
    
    Dim q = From cust In db.Customers
            Where cust.Orders.Count > 0
            Select cust.CustomerID, cust.CompanyName,
                   OrderCount = cust.Orders.Count, cust.Country
            Order By OrderCount Descending, CompanyName
    
    DataGridView1.DataSource = q
    
  4. Press F5 to run your project and view the results.

See also

How to: Filter Query Results by Using LINQ

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

The following example shows how to create a new application that performs queries against a SQL Server database and filters the results by a particular value by using the Where clause. For more information, see Where Clause.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) opens for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

    The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to the Load event of the form.

  3. When you added tables to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those tables, in addition to individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    Add the following code to the Load event to query the tables that are exposed as properties of your data context. The query filters the results and returns only customers that are located in London.

    VB
    Dim db As New northwindDataContext
    
    Dim customers_London = From cust In db.Customers 
                           Where cust.City = "London" 
                           Select cust.CustomerID, cust.CompanyName, 
                                  OrderCount = cust.Orders.Count, 
                                  cust.City, cust.Country
    
    DataGridView1.DataSource = customers_London
    
  4. Press F5 to run your project and view the results.

  5. Following are some other filters that you can try.

    VB
    Dim companies_H = From cust In db.Customers 
                      Where cust.Orders.Count > 0 And
                            cust.CompanyName.StartsWith("H") 
                      Select cust.CustomerID, cust.CompanyName, 
                             OrderCount = cust.Orders.Count, 
                             cust.Country
    
    Dim customers_USA = From cust In db.Customers 
                        Where cust.Orders.Count > 15 And
                              cust.Country = "USA" 
                        Select cust.CustomerID, cust.CompanyName, 
                               OrderCount = cust.Orders.Count, 
                               cust.Country
    

See also

How to: Count, Sum, or Average Data by Using LINQ

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

The following example shows how to create a new application that performs queries against a SQL Server database. The sample counts, sums, and averages the results by using the Aggregate and Group By clauses. For more information, see Aggregate Clause and Group By Clause.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

    The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to the Load event of the form.

  3. When you added tables to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those tables, and to access individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    Add the following code to the Load event to query the tables that are exposed as properties of your DataContext and count, sum, and average the results. The sample uses the Aggregate clause to query for a single result, and the Group By clause to show an average for grouped results.

    VB
    Dim db As New northwindDataContext
    Dim msg = ""
    
    Dim londonCustomerCount = Aggregate cust In db.Customers
                              Where cust.City = "London"
                              Into Count()
    msg &= "Count of London Customers: " & londonCustomerCount & vbCrLf
    
    Dim averageOrderCount = Aggregate cust In db.Customers
                            Where cust.City = "London"
                            Into Average(cust.Orders.Count)
    msg &= "Average number of Orders per customer: " &
           averageOrderCount & vbCrLf
    
    Dim venezuelaTotalOrders = Aggregate cust In db.Customers
                               Where cust.Country = "Venezuela"
                               Into Sum(cust.Orders.Count)
    msg &= "Total number of orders from Customers in Venezuela: " &
           venezuelaTotalOrders & vbCrLf
    
    MsgBox(msg)
    
    Dim averageCustomersByCity = From cust In db.Customers
                                 Group By cust.City
                                 Into Average(cust.Orders.Count)
                                 Order By Average
    
    DataGridView1.DataSource = averageCustomersByCity
    
    'Another way to grab the count and sum
    
    londonCustomerCount = (From cust in db.Customers
                            Where cust.City = "London").Count()
    
    venezuelaTotalOrders = (From cust in db.Customers
                                Where cust.Country = "Venezuela"
                                Select cust.Orders).Sum()
    
  4. Press F5 to run your project and view the results.

See also

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

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries.

The following example shows how to create a new application that performs queries against a SQL Server database. The sample determines the minimum and maximum values for the results by using the Aggregate and Group By clauses. For more information, see Aggregate Clause and Group By Clause.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer. Click the Orders table and drag it to the left pane of the designer.

    The designer creates new Customer and Order objects for your project. Notice that the designer automatically detects relationships between the tables and creates child properties for related objects. For example, IntelliSense will show that the Customer object has an Orders property for all orders related to that customer.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to add code to the Load event of the form.

  3. When you added tables to the O/R Designer, the designer added a DataContext object for your project. This object contains the code that you must have to access those tables, in addition to individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    Add the following code to the Load event. This code queries the tables that are exposed as properties of your data context and determines the minimum and maximum values for the results. The sample uses he Aggregate clause to query for a single result, and the Group By clause to show an average for grouped results.

    VB
    Dim db As New northwindDataContext
    
    Dim minimumOrders = Aggregate cust In db.Customers
                        Where cust.City = "London"
                        Into Min(cust.Orders.Count)
    
    MsgBox("Minimum Orders from a London Customer: " & minimumOrders)
    
    Dim maximumOrdersByCountry = From cust In db.Customers
                                 Group By cust.Country
                                   Into MaxOrders = Max(cust.Orders.Count)
    
    DataGridView1.DataSource = maximumOrdersByCountry
    
  4. Press F5 to run your project and view the results.

See also

How to: Return a LINQ Query Result as a Specific Type

Language-Integrated Query (LINQ) makes it easy to access database information and execute queries. By default, LINQ queries return a list of objects as an anonymous type. You can also specify that a query return a list of a specific type by using the Select clause.

The following example shows how to create a new application that performs queries against a SQL Server database and projects the results as a specific named type. For more information, see Anonymous Types and Select Clause.

The examples in this topic use the Northwind sample database. If you do not have this database on your development computer, you can download it from the Microsoft Download Center. For instructions, see Downloading Sample Databases.

Note

Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalizing the IDE.

To create a connection to a database

  1. In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.

  2. Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.

  3. Specify a valid connection to the Northwind sample database.

To add a project that contains a LINQ to SQL file

  1. In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.

  2. On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.

  3. Name the file northwind.dbml. Click Add. The Object Relational Designer (O/R Designer) is opened for the northwind.dbml file.

To add tables to query to the O/R Designer

  1. In Server Explorer/Database Explorer, expand the connection to the Northwind database. Expand the Tables folder.

    If you have closed the O/R Designer, you can reopen it by double-clicking the northwind.dbml file that you added earlier.

  2. Click the Customers table and drag it to the left pane of the designer.

    The designer creates a new Customer object for your project. You can project a query result as the Customer type or as a type that you create. This sample will create a new type in a later procedure and project a query result as that type.

  3. Save your changes and close the designer.

  4. Save your project.

To add code to query the database and display the results

  1. From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.

  2. Double-click Form1 to modify the Form1 class.

  3. After the End Class statement of the Form1 class, add the following code to create a CustomerInfo type to hold the query results for this sample.

    VB
    Public Class CustomerInfo
      Public Property CompanyName As String
      Public Property ContactName As String
    End Class
    
  4. When you added tables to the O/R Designer, the designer added a DataContext object to your project. This object contains the code that you must have to access those tables, and to access individual objects and collections for each table. The DataContext object for your project is named based on the name of your .dbml file. For this project, the DataContext object is named northwindDataContext.

    You can create an instance of the DataContext in your code and query the tables specified by the O/R Designer.

    In the Load event of the Form1 class, add the following code to query the tables that are exposed as properties of your data context. The Select clause of the query will create a new CustomerInfo type instead of an anonymous type for each item of the query result.

    VB
    Dim db As New northwindDataContext
    
    Dim customerList =
      From cust In db.Customers
      Where cust.CompanyName.StartsWith("L")
      Select New CustomerInfo With {.CompanyName = cust.CompanyName,
                                    .ContactName = cust.ContactName}
    
    DataGridView1.DataSource = customerList
    
  5. Press F5 to run your project and view the results.

See also

 

Source/Reference


©sideway

ID: 201000004 Last Updated: 4/10/2020 Revision: 0 Ref:

close

References

  1. Active Server Pages,  , http://msdn.microsoft.com/en-us/library/aa286483.aspx
  2. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929%28v=vs.90%29.aspx
  3. ASP Best Practices,  , http://technet.microsoft.com/en-us/library/cc939157.aspx
  4. ASP Built-in Objects,  , http://msdn.microsoft.com/en-us/library/ie/ms524716(v=vs.90).aspx
  5. Response Object,  , http://msdn.microsoft.com/en-us/library/ms525405(v=vs.90).aspx
  6. Request Object,  , http://msdn.microsoft.com/en-us/library/ms524948(v=vs.90).aspx
  7. Server Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525541(v=vs.90).aspx
  8. Application Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525360(v=vs.90).aspx
  9. Session Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms524319(8v=vs.90).aspx
  10. ASPError Object,  , http://msdn.microsoft.com/en-us/library/ms524942(v=vs.90).aspx
  11. ObjectContext Object (IIS),  , http://msdn.microsoft.com/en-us/library/ms525667(v=vs.90).aspx
  12. Debugging Global.asa Files,  , http://msdn.microsoft.com/en-us/library/aa291249(v=vs.71).aspx
  13. How to: Debug Global.asa files,  , http://msdn.microsoft.com/en-us/library/ms241868(v=vs.80).aspx
  14. Calling COM Components from ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524620(v=VS.90).aspx
  15. IIS ASP Scripting Reference,  , http://msdn.microsoft.com/en-us/library/ms524664(v=vs.90).aspx
  16. ASP Keywords,  , http://msdn.microsoft.com/en-us/library/ms524672(v=vs.90).aspx
  17. Creating Simple ASP Pages,  , http://msdn.microsoft.com/en-us/library/ms524741(v=vs.90).aspx
  18. Including Files in ASP Applications,  , http://msdn.microsoft.com/en-us/library/ms524876(v=vs.90).aspx
  19. ASP Overview,  , http://msdn.microsoft.com/en-us/library/ms524929(v=vs.90).aspx
  20. FileSystemObject Object,  , http://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.84).aspx
  21. http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944(v=vs.85).aspx,  , ADO Object Model
  22. ADO Fundamentals,  , http://msdn.microsoft.com/en-us/library/windows/desktop/ms680928(v=vs.85).aspx
close
IMAGE

Home 5

Business

Management

HBR 3

Information

Recreation

Hobbies 8

Culture

Chinese 1097

English 337

Reference 67

Computer

Hardware 151

Software

Application 198

Digitization 25

Latex 9

Manim 121

Numeric 19

Programming

Web 283

Unicode 494

HTML 65

CSS 58

ASP.NET 174

OS 389

DeskTop 7

Python 19

Knowledge

Mathematics

Formulas 8

Algebra 25

Number Theory 206

Trigonometry 18

Geometry 21

Calculus 67

Complex Analysis 21

Engineering

Tables 8

Mechanical

Mechanics 1

Rigid Bodies

Statics 92

Dynamics 37

Fluid 5

Fluid Kinematics 5

Control

Process Control 1

Acoustics 19

FiniteElement 2

Physics

Electric 27

Biology 1

Geography 1


Copyright © 2000-2020 Sideway . All rights reserved Disclaimers last modified on 06 September 2019