Wei-Meng Lee - C# 2008 Programmer's Reference

To make use of LINQ to DataSet, ensure that you have a reference to System.Data.DataSetExtensions.dllin your project.

To display the result, you can either bind the result to a DataGridView control using the AsDataView()method:

//---bind to a datagridview control---

dataGridView1.DataSource = authors.AsDataView();

Or, iteratively loop through the result using a foreach loop:

foreach (DataRow row in authors) {

Console.WriteLine("{0} - {1}, {2}",

row["au_id"], row["au_fname"], row["au_lname"]);

}

To query the authors based on their contract status, use the following query:

EnumerableRowCollection authors =

from author in ds.Tables[0].AsEnumerable()

where author.Field("Contract") == true

select author;

Using the new anonymous types feature in C# 3.0, you can define a new type without needing to define a new class. Consider the following statement:

//---query for authors living in CA---

var authors =

from author in ds.Tables[0].AsEnumerable()

where author.Field("State") == "CA"

select new {

ID = author.Field("au_id"),

FirstName = author.Field("au_fname"),

LastName = author.Field("au_lname")

};

Here, you select all the authors living in the CA state and at the same time create a new type consisting of three properties: ID, FirstName, and LastName. If you now type the word authors, IntelliSense will show you that authors is of type EnumerableRowCollection<'a> authors, and 'ais an anonymous type containing the three fields (see Figure 14-6).

Figure 14-6

You can now print out the result using a foreach loop:

foreach (var row in authors) {

Console.WriteLine("{0} - {1}, {2}",

row.ID, row.FirstName, row.LastName);

}

To databind to a DataGridView control, you first must convert the result of the query to a List object:

//---query for authors living in CA---

var authors =

(from author in ds.Tables[0].AsEnumerable()

where author.Field("State") == "CA"

select new {

ID = author.Field("au_id"),

FirstName = author.Field("au_fname"),

LastName = author.Field("au_lname")

}).ToList();

//---bind to a datagridview control---

dataGridView1.DataSource = authors;

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("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("State")=="CA");

Console.WriteLine(query);

LINQ supports the following standard aggregate functions:

For example, the following statements print out the largest odd number contained in the numsarray:

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

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 Customerstable is related to the Orderstable via the CustomerIDfield, while the Orderstable is related to the Order_Detailstable via the OrderIDfield.

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);