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.