Grouping and Aggregating Data
In addition to its extensible event-driven architecture, the LinqDataSource control also exposes attributes through which you can declaratively group and aggregate data. This section shows a simple example that groups all the products by category, and displays the category, the products' average price, and the number of products in that category.
<%@ Page Language="C#" %>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Using LinqDataSource Control to group and aggregate
data</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListView DataSourceID="productsSource"
ID="productsView" runat="server">
<LayoutTemplate>
<table id="tblLayout" border="1" runat="server">
<thead>
<tr>
<th><b>Product Category</b></th>
<th><b>Average List Price</b></th>
<th><b>Record Count</b></th>
</tr>
</thead>
<tr runat="server" id="itemPlaceholder" ></tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr>
<td><%# Eval("Key.Name") %></td>
<td><%# Eval("AverageListPrice") %></td>
<td><%# Eval("RecordCount") %></td>
</tr>
</ItemTemplate>
</asp:ListView>
<asp:LinqDataSource ID="productsSource" runat="server"
ContextTypeName="AdventureWorksDatabaseDataContext"
TableName="Products" Select="new(Key, Average(ListPrice) As
AverageListPrice, Count() As RecordCount)"
GroupBy="ProductSubcategory">
</asp:LinqDataSource>
</div>
</form>
</body>
</html>
The LinqDataSource control defined toward the end of the preceding code has a new attribute, named
GroupBy, which is set to the ProductSubcategory object. Having set the
GroupBy property, you can then retrieve the value using the
Key property, which contains an object that exposes whatever value you use to group the data—in this case, the product subcategory name.
 | |
| Figure 4. Grouping and Aggregating Data: The LinqDataSource control lets you group and aggregate data by declaratively setting the GroupBy property. |
Using the ProductSubcategory value as the
Key property, you can calculate the average list price of all products that share a ProductSubcategory value. The query also returns a count of the number of records associated with each ProductSubcategory value.
Select="new(Key, Average(ListPrice) As
AverageListPrice, Count() As RecordCount)"
Note that the aggregate functions have aliases so you can reference them from the data bound control. Here's the snippet of code that references the previously-defined aggregate functions from the ListView control:
<td><%# Eval("Key.Name") %></td>
<td><%# Eval("AverageListPrice") %></td>
<td><%# Eval("RecordCount") %></td>
Again, note that the
Key.Name property contains the same value as the
ProductSubcategory.Name property.
Figure 4 shows the resulting page.
Using the advanced LinqDataSource control features you've seen here, you can easily create fully-functional editable data displays that use CRUD functionality to modify data. You can handle the CRUD events that the LinqDataSource control generates, and you can provide high-level displays such as dashboards by grouping and aggregating data. The LinqDataSource control's extensible nature lets you customize the runtime behavior as much as you like, simplifying the process of creating rich data-driven web applications.
Previous Page
