C# 2008 Programmer's Reference

DataTable orderDetailsTable = ds.Tables[2]; //---Order Details---

The following LINQ query joins two DataTable objects — customersTable and ordersTable — using the query syntax:

//---using query syntax to join two tables - Customers and Orders-
var query1 =
 (from customer in customersTable.AsEnumerable()
 join order in ordersTable.AsEnumerable() on
 customer.Field<string>('CustomerID') equals order.Field<string>('CustomerID')
 select new {
  id = customer.Field<string>('CustomerID'),
  CompanyName = customer.Field<string>('CompanyName'),
  ContactName = customer.Field<string>('ContactName'),
  OrderDate = order.Field<DateTime>('OrderDate'),
  ShipCountry = order.Field<string>('ShipCountry')
 }).ToList();

As evident in the query, the Customers and Orders table are joined using the CustomerID field. The result is reshaped using an anonymous type and then converted to a List object using the ToList() extension method. You can now bind the result to a DataGridView control if desired. Figure 14-8 shows the result bound to a DataGridView control.

Figure 14-8

You can also rewrite the query using the method syntax:

//---using method syntax to join two tables - Customers and Orders
var query1 =
 (customersTable.AsEnumerable().Join(ordersTable.AsEnumerable(),
 customer => customer.Field<string>('CustomerID'),
 order => order.Field<string>('CustomerID'),
 (customer, order) => new {
  id = customer.Field<string>('CustomerID'),
  CompanyName = customer.Field<string>('CompanyName'),
  ContactName = customer.Field<string>('ContactName'),
  OrderDate = order.Field<DateTime>('OrderDate'),
  ShipCountry = order.Field<string>('ShipCountry')
 })).ToList();

The following query joins three DataTable objects — customersTable, ordersTable, and orderDetailsTable — and sorts the result according to the OrderID field:

//---three tables join---
var query2 =
 (from customer in customersTable.AsEnumerable()
 join order in ordersTable.AsEnumerable() on
 customer.Field<string>('CustomerID') equals order.Field<string> ('CustomerID')
 join orderDetail in orderDetailsTable.AsEnumerable() on
 order.Field<int>('OrderID') equals orderDetail.Field<int>('OrderID')
 orderby order.Field<int>('OrderID')
 select new {
  id = customer.Field<string>('CustomerID'),
  CompanyName = customer.Field<string>('CompanyName'),
  ContactName = customer.Field<string>('ContactName'),
  OrderDate = order.Field<DateTime>('OrderDate'),
  ShipCountry = order.Field<string>('ShipCountry'),
  OrderID = orderDetail.Field<int>('OrderID'),
  ProductID = orderDetail.Field<int>('ProductID')
 }).ToList();

As evident from the query, the Customers table is related to the Orders table via the CustomerID field, and the Orders table is related to the Order Details table via the OrderID field.

Figure 14-9 shows the result of the query.

Figure 14-9

Typed DataSet

So far you've used the Field() extension method to access the field of a DataTable object. For example, the following program uses LINQ to DataSet to query all the customers living in the USA. The result is then reshaped using an anonymous type:

SqlConnection conn;
SqlCommand comm;
SqlDataAdapter adapter;
DataSet ds = new DataSet();
conn = new SqlConnection(@'Data Source=.SQLEXPRESS;' +
 'Initial Catalog=Northwind;Integrated Security=True');
comm = new SqlCommand('SELECT * FROM Customers', conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds, 'Customers');
var query1 =
 (from customer in ds.Tables[0].AsEnumerable()
 where customer.Field<string>('Country') == 'USA'
 select new {
  CustomerID = customer.Field<string>('CustomerID'),
  CompanyName = customer.Field<string>('CompanyName'),
  ContactName = customer.Field<string>('ContactName'),
  ContactTitle = customer.Field<string>('ContactTitle')
 }).ToList();
dataGridView1.DataSource = query1;

As your query gets more complex, the use of the Field() extension method makes the query unwieldy. A good way to resolve this is to use the typed DataSet feature in ADO.NET. A typed DataSet provides strongly typed methods, events, and properties and so this means you can access tables and columns by name, instead of using collection-based methods.

To add a typed DataSet to your project, first add a DataSet item to your project in Visual Studio 2008 (see Figure 14-10). Name it TypedCustomersDataset.xsd.

Figure 14-10