How to: Group and Aggregate Data Using the LinqDataSource Control

Artikkeli
10/22/2014

The LinqDataSource control enables you to group data from a data source by one or more data columns. You group records by a column when you want to retrieve information about all the records in a data source that share a value in the grouped column. Frequently, you use aggregate functions on grouped data to calculate values such as the sum, average, or count.

To define which data columns are used for grouping the data, you set the GroupBy property of the LinqDataSource control. When you want to use the aggregate functions, you also specify a value for the Select property to define the aggregate functions.

Note

You cannot update, insert, or delete records when the data that you are working with is grouped.

Grouping and Aggregating Data by One Column

You can group data based on a single column by specifying the column in the GroupBy property.

To group and aggregate by one column

Set the ContextTypeName of the LinqDataSource control to a data source.
Set the TableName property to the property in the data source object that contains the data to group.
Set the GroupBy property to the column you want to use to group the data.
Set the Select property to include an aggregate function and the column that is used for grouping the data.

The property that is specified in the GroupBy property is retrieved through an object named Key. You must assign names (aliases) to aggregate functions by using the As keyword so that a data-bound control can reference the property created by the aggregate function. The LinqDataSourceView object raises an exception if an aggregate function does not have a name.

The following example shows a LinqDataSource control that uses a column named ProductCategory to group data, and displays the results by using a GridView control. It selects the ProductCategory values through the Key property. It then calculates the average for the ListPrice and Cost properties for products with the same ProductCategory value. It also returns a count of the number of records for each ProductCategory value.
```
<asp:LinqDataSource 
  ContextTypeName="ExampleDataContext" 
  TableName="Products" 
  GroupBy="ProductCategory"
  Select="new(Key, 
    Average(ListPrice) As AverageListPrice, 
    Average(Cost) As AverageCost, 
    Count() As RecordCount)"
  ID="LinqDataSource1" 
  runat="server">
</asp:LinqDataSource>
<asp:GridView 
  DataSourceID="LinqDataSource1" 
  ID="GridView1" 
  runat="server">
</asp:GridView>
```

Grouping and Aggregating by More Than One Column

To group data by more than one column, you must use the new function when you set the GroupBy property.

To group by more than one column

Set the ContextTypeName property and the TableName property as described in the previous procedure.
Set the GroupBy property using syntax like new(column1, column2), where column1 and column2 are the names of the columns you want to use for grouping the data. You can provide as many columns as needed.

Set the Select property to include the Key property and any aggregate functions you need.

The following example shows a LinqDataSource control that is configured to group by two columns and displays the result in a DataList control. The Key object contains two properties, ProductCategory and Color.

<asp:LinqDataSource 
  ContextTypeName="ExampleDataContext" 
  TableName="Products" 
  GroupBy="new(ProductCategory,Color)"
  Select="new(Key,
    Average(ListPrice) as AverageListPrice, 
    Count() as RecordCount)"
  ID="LinqDataSource1" 
  runat="server">
</asp:LinqDataSource>
<asp:DataList 
  DataSourceID="LinqDataSource1" 
  ID="DataList1" 
  runat="server">
  <ItemTemplate>
    <%# Eval("Key.ProductCategory") %> 
    <%# Eval("Key.Color") %> 
    <%# Eval("AverageListPrice") %> 
    <%# Eval("RecordCount") %> 
  </ItemTemplate>
</asp:DataList>

Retrieving Individual Records when Grouping Data

When you have grouped data by one or more columns, you can retrieve the individual records in each grouping by using the It keyword. This keyword represents the current instance of the data object. The returned data will include both the grouped data and the records that belong to a group.

To retrieve and display grouped data and individual records

Set the ContextTypeName property and the TableName property of the LinqDataSource control.
Set the GroupBy property to the column or columns you want to use for grouping the data.

Set the Select property to include the It keyword and any aggregate functions that you need. The object represented by the It keyword must be renamed with the As keyword.

The following example shows a LinqDataSource control that is configured to group by two columns. The Key property references an object that has two properties, ProductCategory and Color. The object represented by It is renamed to Products. The renamed Products object contains a collection of the individual records in a grouping. Each instance contains all the columns from the Products table.

<asp:LinqDataSource 
  ContextTypeName="ExampleDataContext" 
  TableName="Products" 
  GroupBy="new(ProductCategory,Color)"
  Select="new(Key,
     It As Products,
     Max(ListPrice) As MaxListPrice, 
     Min(ListPrice) As MinListPrice)"
  ID="LinqDataSource1" 
  runat="server">
</asp:LinqDataSource>

The following example shows two ListView controls that display the grouped data and the individual names of products that belong to that group. The nested data-bound control’s DataSource property is set to Products, which is the alias for the it object.

<asp:ListView 
  DataSourceID="LinqDataSource1" 
  ID="ListView1" 
  runat="server">

  <LayoutTemplate>
    <table style="background-color:Teal;color:White" 
      runat="server">
      <thead>
        <tr>
          <th>Product Category</th>
          <th>Color</th>
          <th>Highest Price</th>
          <th>Lowest Price</th>
        </tr>
      </thead>
      <tr runat="server" id="itemPlaceholder">
      </tr>
    </table>

  </LayoutTemplate>

  <ItemTemplate>
    <tr>
      <td><%# Eval("Key.ProductCategory") %></td>
      <td><%# Eval("Key.Color") %></td>
      <td><%# Eval("MaxListPrice") %></td>
      <td><%# Eval("MinListPrice") %></td>
    </tr>
    <tr>
      <td colspan="4" style="width:100%;background-color:White;color:Black" >
        <asp:ListView 
          DataSource='<%# Eval("Products") %>' 
          runat="server" 
          ID="ListView2">

          <LayoutTemplate>
            <div 
              runat="server" 
              id="itemPlaceholder">
            </div>
          </LayoutTemplate>

          <ItemTemplate>
            <%# Eval("ProductName") %><br />
          </ItemTemplate>

        </asp:ListView> 
      </td>
    </tr>
  </ItemTemplate>
</asp:ListView>

Jaa