n Part 2 of this article series, you looked at Linq’s query features, including the standard query operators and their applicability in querying XML data. With that background, this installment switches gears to discuss the basics of DLinq (LINQ to SQL) and its use in querying relational data from a SQL Server database. Specifically, this installment explores leveraging DLinq as a relational query engine and transforming relational output into hierarchical XML output using XLinq. Finally this article also provides an example of using LINQ’s mathematical functions for calculating the value of derived columns.

Basics of DLinq
DLinq, a member of the LINQ family of technologies, provides an abstraction layer that lets you treat relational data as classes (also known as entity classes) queryable through LINQ. Inside the abstraction layer, DLinq translates standard LINQ queries into SQL for execution by the database and then translates the tabular results from the database back into objects you define. Your application is then free to manipulate the objects while DLinq stays in the background tracking your changes automatically. Here are the steps for querying relational data using DLinq.

1. Define and create the entity classes
2. Reference the entity classes from within your calling application
3. Write code against the entity classes

Defining Entity Classes
To get started using DLinq you first define entity classes that are linked to relational data, decorating these classes and their properties with special attributes that associate them with specific relational tables and columns. You can define entity classes in three ways:

1. Using a text editor (such as Notepad) to hand-code the classes manually.
2. Using a command line utility called sqlmetal.
3. Using the DLinq designer, which lets you map relational objects to object definitions. With this method, you can create the objects layer as a shareable class library. To begin, create a project using the LINQ Library project template accessible from the New Project dialog box. You can then open the DLinq designer by adding a new item of type “DLinqObjects.” Then you can drag and drop elements from the server explorer and map them to the classes defined in the designer.

This article concentrates on creating entity classes using the sqlmetal utility (option 2, above). Installing the May 2006 LINQ CTP edition also installs the sqlmetal utility in the Program FilesLINQ PreviewBin folder. To create the entity classes (mapped here to the AdventureWorks database), open a command window, change to the sqlmetal install directory, and enter the following command at a command prompt.

   sqlmetal /server:localhost     /database:AdventureWorks     /user:username /password:password      /code:AdventureWorks.cs

The preceding command creates a class file named AdventureWorks.cs that contains a number of classes (one class for each table) decorated with the required relational data mapping.

As an example, if you open the file, and look at the declaration of the UnitMeasure class, you’ll see a metadata decoration that specify the name of the related database table?UnitMeasure, in this case?and the names of columns that map to class properties.

   [Table(Name="[Production].[UnitMeasure]")]   public partial class UnitMeasure :        System.Data.DLinq.INotifyPropertyChanging,       System.ComponentModel.INotifyPropertyChanged    {      private string _UnitMeasureCode;      private string _Name;      private System.DateTime _ModifiedDate;      ...       ...      [Column(Storage="_UnitMeasureCode",          DBType="NChar(3) NOT NULL", Id=true)]      public string UnitMeasureCode       {         get         {            return this._UnitMeasureCode;         }         set         {            ...         }         ...      }

Notice that this approach establishes the appropriate mappings (table/class, column/property, etc) directly within the code. Sometimes, that’s convenient, but other times you might want to externalize the mappings, storing them in an external file to separate them from the class implementation. To do this, supply a /map switch to the sqlmetal utility with the name of the XML file that you want to hold the resultant mappings. Doing this allows you to make changes to the mappings on the fly directly in the XML file without having to touch the class implementation.

Executing a Simple DLinq Query
Now that you’ve seen how DLinq maps classes to tables and properties to columns, here’s a simple example that uses DLinq to retrieve relational data, transforms that into XML data using XLinq, and finally displays the XML data by data binding with an XmlDataSource control.

To start, create a new Web site named AdvancedXLinq using the LINQ ASP.NET Web Site template. After creating the Web site, add the AdventureWorks.cs file created in the previous section to the App_Code folder of the new Web site. After that, create a new ASP.NET page and modify its code as shown in Listing 1. Note that in addition to the core LINQ and XLinq namespaces such as System.Query and System.Xml.XLinq, you also need to import the System.Data.DLinq namespace to use DLinq features from the code.

The Page_Load event retrieves the connection string from the Web.config file, passing that to the SqlConnection object constructor:

   SqlConnection connection = new SqlConnection(connectionString);

After creating the SqlConnection object, you supply that as an argument to the constructor of the AdventureWorks class:

   AdventureWorks db = new AdventureWorks(connection);                       

With this plumbing in place, you can now query any table in the AdventureWorks database using the familiar LINQ query notation. As an example, you can retrieve all the categories from the ProductCategory table just by using the format db.Production.ProductCategory. This example retrieves all the categories and transforms them into XML format using the XLinq functional construction technique discussed in Part 1 of this series:

   XElement categories = new XElement("Categories",       from category in db.Production.ProductCategory       select new XElement("Category",      new XAttribute("ID", category.ProductCategoryID),      new XAttribute("Name", category.Name),      new XAttribute("Guid", category.Rowguid)));

As discussed in Part 2 of this article series, the from?select query retrieves all the categories. You then transform each of the category values into attributes of an XML element named , which in turn is embedded inside a root element. The preceding code generates the following XML:

             ---     ---   

	?
Figure 1. Categories Output: Here’s the output of Listing 1, showing the list of categories retrieved from the AdventureWorks database.

To databind the returned XML with an XmlDataSource, set its Data property to the output XML:

     categorySource.Data = categories.Xml; 

Finally you set the DataSource property of the GridView control to the ID of the XmlDataSource and then invoke its DataBind() method:

   gridCategories.DataSource =  categorySource;                          gridCategories.DataBind();

Figure 1 shows the output produced by the page when requested from the browser.

Complex Data Binding with XML Data
Now that you’ve seen a simple example, here’s a more complex ASP.NET page that retrieves both Categories and Products data from the AdventureWorks database and performs nested data binding with that XML data. Start by adding an ASP.NET page named NestedDataBinding.aspx to the Web site and modifying its code to match Listing 2.

If you examine the long line of code in Listing 2 (duplicated below) that constructs the XML, you’ll notice that it begins similarly to the previous example:

   XElement categories = new XElement("Categories",       from category in db.Production.ProductSubcategory        orderby category.ProductSubcategoryID       select new XElement("Category",      new XAttribute("ID", category.ProductSubcategoryID),      new XAttribute("Name", category.Name),      new XAttribute("ModifiedDate", category.ModifiedDate),       from product in category.Product orderby product.Name      select new XElement("Product",       new XAttribute("Name", product.Name),      new XAttribute("Number", product.ProductNumber),      new XAttribute("Price", product.ListPrice))));

However the second part of the code differs because it creates the product details as a child element of the element. Therefore, you need to first get all the products that belong to the parent category and add them as attributes of the elements, which will then be added as children of the element. To accomplish this, the code uses the from product in category.Product statement to get a reference to all the products belonging to the current category. It then loops through the references retrieving their values, and adding them as attributes of the elements.

The code in Listing 2 code results in the following XML output:

                                                 ...    ...   

	?
Figure 2. Hierarchical Output: This nested databound output shows the list of categories and the products that belong to each of those categories from the AdventureWorks database.

As in the previous example, you assign the returned XML data to the XmlDataSource control. After that, you set the DataSource property of the GridView control to the ID of the XmlDataSource control:

   gridCategories.DataSource = categorySource;     

You can display all the categories and their corresponding products in a single view by embedding the child products GridView control in the parent categories GridView control. Note that the preceding line of code only sets the DataSource property of the parent categories GridView control. To retrieve all the products contained in a specific category, you set the child products GridView control’s DataSource property to <%# XPathSelect("Product") %>. This XPath expression?retrieves all the elements for the corresponding category and uses them as the data source. Figure 2 shows the generated result when the page is viewed in a browser.

Using Mathematical Functions for Calculated Columns
So far, you’ve seen the output of both simple and complex DLinq query examples used as the input for an XLinq functional construction process. This next example looks at how to leverage LINQ’s mathematical functions, such as Min, Max, Average, and Count to calculate derived column values. To begin, add a new ASP.NET page to the Web site and modify its code to look like Listing 3:

	?
Figure 3. Derived Output from Categories and Products: The output on this page shows the list of categories and related product details, including derived column values such as the average, minimum, and maximum price and the count of products contained in each category.

Listing 3 displays the category id, name, average, minimum and maximum price, and count of all the products contained in a specific category (see Figure 3). You can retrieve the category id and name column values directly from the ProductSubcategory table, but you need to calculate the remaining fields via LINQ’s mathematical functions.

As an example, you get the average price of products in a specific category using the lambda expression category.Product.Average(o => o.ListPrice). Note that lambda expressions are always of the form:

   parameters => expression 

Similarly you calculate the maximum and minimum price of products in a category using lambda expressions. For example, you can easily get the product count using the Count property.

In this installment of this series, you saw how to use DLinq’s query functionality to access relational data. You also saw how to use XLinq’s functional construction technique to convert the returned relational data into XML data, binding it to an XmlDataSource control for display purposes. Finally, you saw some examples of using LINQ’s mathematical functions to calculate the values of derived columns.