How to: Count, Sum, or Average Data by Using LINQ (Visual Basic)
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
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.
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
In Visual Studio, open Server Explorer/Database Explorer by clicking Server Explorer/Database Explorer on the View menu.
Right-click Data Connections in Server Explorer/Database Explorer and then click Add Connection.
Specify a valid connection to the Northwind sample database.
To add a project that contains a LINQ to SQL file
In Visual Studio, on the File menu, point to New and then click Project. Select Visual Basic Windows Forms Application as the project type.
On the Project menu, click Add New Item. Select the LINQ to SQL Classes item template.
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
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.
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
Orderobjects 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
Customerobject has an
Ordersproperty for all orders related to that customer.
Save your changes and close the designer.
Save your project.
To add code to query the database and display the results
From the Toolbox, drag a DataGridView control onto the default Windows Form for your project, Form1.
Double-click Form1 to add code to the
Loadevent of the form.
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
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
Loadevent to query the tables that are exposed as properties of your DataContext and count, sum, and average the results. The sample uses the
Aggregateclause to query for a single result, and the
Group Byclause to show an average for grouped results.
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()
Press F5 to run your project and view the results.