//---bind to a datagridview control---
dataGridView1.DataSource = authors;
Aggregate Functions
In an earlier section, you used the following query to obtain a list of authors living in CA:
var authors =
from author in ds.Tables[0].AsEnumerable()
where author.Field<string>('State') == 'CA'
select author;
To get the total number of authors living in CA, you can use the Count() extension method (also known as an aggregate function), like this:
Console.WriteLine(authors.Count());
A much more efficient way would be to use the following query in method syntax:
var query =
ds.Tables[0].AsEnumerable().Count(a => a.Field<string>('State')=='CA');
Console.WriteLine(query);
LINQ supports the following standard aggregate functions:
| Aggregate function | Description |
|---|---|
Aggregate | Performs a custom aggregation operation on the values of a collection. |
Average | Calculates the average value of a collection of values. |
Count | Counts the elements in a collection, optionally only those elements that satisfy a predicate function. |
LongCount | Counts the elements in a large collection, optionally only those elements that satisfy a predicate function. |
Max | Determines the maximum value in a collection. |
Min | Determines the minimum value in a collection. |
Sum | Calculates the sum of the values in a collection. |
For example, the following statements print out the largest odd number contained in the nums array:
int[] nums = {
12, 34, 10, 3, 45, 6, 90, 22, 87, 49, 13, 32
};
var maxOddNums = nums.Where
(n => n % 2 == 1).OrderByDescending(n => n).Max();
Console.WriteLine('Largest odd number: {0}', maxOddNums); //---87---
The following statements print out the sum of all the odd numbers in nums:
int[] nums = {
12, 34, 10, 3, 45, 6, 90, 22, 87, 49, 13, 32
};
var sumOfOddNums = nums.Where
(n => n % 2 == 1).OrderByDescending(n => n).Sum();
Console.WriteLine('Sum of all odd number: {0}', sumOfOddNums); //---197---
Joining Tables
So far you've been dealing with a single table. In real life, you often have multiple, related tables. A good example is the Northwind sample database, which contains a number of related tables, three of which are shown in Figure 14-7.
Figure 14-7
Here, the Customers table is related to the Orders table via the CustomerID field, while the Orders table is related to the Order_Details table via the OrderID field.
You can use LINQ to DataSet to join several tables stored in a DataSet. Here's how. First, load the three tables into the DataSet, using the following code:
conn = new SqlConnection(@'Data Source=.SQLEXPRESS;' +
'Initial Catalog=Northwind;Integrated Security=True');
comm =
new SqlCommand('SELECT * FROM Customers; SELECT * FROM Orders; SELECT * FROM [Order Details]',
conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds);
The three tables loaded onto the DataSet can now be referenced using three DataTable objects:
DataTable customersTable = ds.Tables[0]; //---Customers---
DataTable ordersTable = ds.Tables[1]; //---Orders---
