The OpenAccessLinqDataSource control enables you to group data by one or more columns. You use the GroupBy option to specify which properties are used for consolidating data records that have the same values. For example, if you set the Key Member to Make, all the records in the query that have the same Make property value are returned as a single consolidated record. The GroupBy statement is used in conjunction with the aggregate functions to group the result set by one or more columns. This topic illustrates how to use the OpenAccessLinqDataSource control to group and aggregate data.
||You cannot insert, update or delete records when you are working with grouped data.|
Grouping and Aggregating Data
The GroupBy tab on the second page of the wizard (the Configure Data Selection dialog) allows you to group records.
To create a new GroupBy expression:
- In the Configure Data Selection dialog of the OpenAccessLinqDataSource Wizard, switch to the GroupBy tab.
- Select the Key Member. This is the column you want to use to group the data. For example, if you set the Key Member to Make, all the records in the query that have the same Make property value are returned as a single consolidated record.
- Select the order of the grouped records using the Order Groups By option. You have three possible options here: <None>, Key and Key Descending.
- The GroupBy is often used with aggregation methods. If you want to use aggregation methods, the next step is to select the aggregation Member. The Member option specifies the column that will be used in the aggregation function.
- The OpenAccessLinqDataSource provides four different aggregation Functions: Average, Min, Max and Sum. Note that depending on the Member type, some of the functions could not be available.
- Specify an alias for the aggregation method or use the default one.
- Finally, click the Add button to add the configured aggregation function to the GroupBy statement.
One special item in the Key Member drop-down is the < Custom > entry.
It allows you to perform custom grouping. Generally, you need to perform two steps here:
- Specify a Custom Group. This is the column you want to use to group the data.
- Specify a custom Select statement.
For a complete UI reference of the GroupBy tab page, check out the GroupBy Settings topic.