Task 5 – Querying using Expressions

1. Return to the program code file by double clicking on the Program.cs file in Solution Explorer. Find the ObjectQuery method. Each table can now be accessed as a property of the db variable. At this point, querying is almost identical to the previous exercises. Add the following code to retrieve customers in London:

static void ObjectQuery()

{

var db = new NorthwindDataContext();

db.Log = Console.Out;

var results = from c in db.Customers

where c.City == "London"

select c;

foreach (var c in results)

Console.WriteLine("{0}\t{1}", c.CustomerID, c.City);

}

This creates a NorthwindDataContext object (which extends the DataContext class previously used in the first task) that represents the strongly typed connection to the database. As such it is important to note that there is no connection string specified and intellisense shows all the tables specified by the designer.

2. Press Ctrl+F5 to build and run the application. After viewing the results, press any key to terminate the application.

Six results are shown. These are customers in the Northwind Customers table with a City value of London.

3. You also created mappings to other tables when using the designer. The Customer class has a one-to-many mapping to Orders. This next query selects from multiple tables.

static void ObjectQuery()

{

var db = new NorthwindDataContext();

db.Log = Console.Out;

var results = from c in db.Customers

from o in c.Orders

where c.City == "London"

select new { c.ContactName, o.OrderID };

foreach (var c in results)

Console.WriteLine("{0}\t{1}", c.ContactName, c.OrderID);

}

The select statement creates a new object with an anonymous type (a new C# 3.0 feature). The type created holds two pieces of data, both strings with the names of the properties of the original data (in this case ContactName and OrderID). Anonymous types are quite powerful when used in queries. By using these types it saves the work of creating classes to hold every type of result created by various queries.

In the preceding example, the object model can easily be seen by noticing the object relationship shown by writing c.Orders. This relationship was defined in the designer as a one-to-many relationship and now can be accessed in this manner.

4. Press Ctrl+F5 to build and run the application to view the results. Then press any key to terminate the application.

Date: 2015-02-03; view: 739