Browse DevX
Sign up for e-mail newsletters from DevX


XLinq Part 3: Combining DLinq and XLinq for Query and Display Power : Page 3

DLinq provides mapping capabilities between .NET objects and databases, letting you treat relational data as classes that you can query with LINQ and transform with XLinq to simplify the process of extracting data, storing it in objects, and displaying those objects onscreen.





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 <Category> element. Therefore, you need to first get all the products that belong to the parent category and add them as attributes of the <Product> elements, which will then be added as children of the <Category> 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 <Product> elements.

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

<Categories> <Category ID="5" Name="Bottom Brackets" ModifiedDate="1998-06-01T00:00:00"> <Product Name="HL Bottom Bracket" Number="BB-9108" Price="121.4900" /> <Product Name="LL Bottom Bracket" Number="BB-7421" Price="53.9900" /> <Product Name="ML Bottom Bracket" Number="BB-8107" Price="101.2400" /> </Category> <Category ID="6" Name="Brakes" ModifiedDate="1998-06-01T00:00:00"> <Product Name="Front Brakes" Number="FB-9873" Price="106.5000" /> <Product Name="Rear Brakes" Number="RB-9231" Price="106.5000" /> </Category> ... ... </Categories>

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