To understand this article, we will create a sample application that displays employee information by grouping the data from Employee and Dept table.

Steps

1.      From the start menu > All Programs > Open Visual Studio 2008.

2.      Create a new Asp.Net website by clicking New > Website.

3.      Use a language of your choice, i have selected C# as the language.

4.      Next, create a new SQL express database with Employee and Dept table.

As we all know, in order to work with LINQDataSource control, we need to first design our LINQ to SQL or LINQ Entity classes.

Designing the LINQ to SQL classes

I assume you have already created a SqlExpress database in App_Data folder with Employee and Department table.

1.      Open Server Explorer, Expand the database tables.

2.      Select and drag Employee and Dept table into the dbml file(LINQ to SQL designer). Click Save. The LINQ to SQL Objects and the data context class will be created automatically.

Refer the below figure.

Using GroupBy with LINQDataSource Control

To understand grouping data with LINQDataSource, we will fetch the maximum employee age in each department and populate it on the GridView.

<asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns="False" DataSourceID="LinqDataSource1" >

         <Columns>   

         <asp:BoundField DataField="Department" HeaderText="Department"

         ReadOnly="True" SortExpression="DeptID" />

         <asp:BoundField DataField="MaxAge" HeaderText="MaxAge"

         ReadOnly="True"  />

        </Columns>

  </asp:GridView>

    <asp:LinqDataSource ID="LinqDataSource1" runat="server"

        ContextTypeName="DataClassesDataContext" TableName="Employees"

        GroupBy="DeptID"

        Select="new(key as Department,Max(Age) as MaxAge)" >         

    </asp:LinqDataSource>

This will give the following output.

In the above code, if we see the Select property of LINQDataSource i have used a keyword called key. We can use this keyword to fetch the data column specified in GroupBy property. Just specify an alias to the keyword to display it in gridview. The LINQDataSource control is configured to group and fetch the DeptID.

You can change the GroupBy property to below to fetch the Department name instead.

Refer below,

<asp:LinqDataSource ID="LinqDataSource1" runat="server"

        ContextTypeName="DataClassesDataContext" TableName="Employees"

        GroupBy="Dept.Department"

        Select="new (key as Department,Max(Age) as MaxAge)">

    </asp:LinqDataSource>

Grouping on Multiple Columns with LINQDataSource Control

We can also group the data on multiple columns using LINQDataSource control’s GroupBy property. For example, to fetch the maximum age in each department on different locations we need to group data on Dept and Location.

 <asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns="False" DataSourceID="LinqDataSource1" >

         <Columns>   

         <asp:BoundField DataField="Department" HeaderText="Department"

         ReadOnly="True" SortExpression="DeptID" />

         <asp:BoundField DataField="MaxAge" HeaderText="MaxAge"

         ReadOnly="True"  />

          <asp:BoundField DataField="City" HeaderText="City"

         ReadOnly="True"  />     

        </Columns>

        </asp:GridView>

<asp:LinqDataSource ID="LinqDataSource1" runat="server"

        ContextTypeName="DataClassesDataContext" TableName="Employees"

        GroupBy="new(Location,Dept.Department)"

        Select="new (key.Location as City,key.Department as Department, Max(Age) as MaxAge)" >

    </asp:LinqDataSource>

Fetching the Grouped Rows in LINQDatasource control

In order to fetch the grouped rows, we can use the keyword called “it” in Select property which will return us the collection of individual rows that are grouped. This keyword should be given an alias similar to key keyword.

Refer the below code,

<asp:GridView ID="GridView1" runat="server"

        AutoGenerateColumns="False" DataSourceID="LinqDataSource1" >

         <Columns>   

               <asp:BoundField DataField="Department" HeaderText="Department"

                ReadOnly="True" SortExpression="DeptID" />

              <asp:BoundField DataField="MaxAge" HeaderText="MaxAge"

              ReadOnly="True"  />

               <asp:BoundField DataField="City" HeaderText="City"

               ReadOnly="True"  />

              <asp:TemplateField HeaderText="GroupedRows">

               <ItemTemplate>

                     <asp:GridView runat="server" DataSource='<%# Eval("Employees") %>'></asp:GridView>

               </ItemTemplate>

              </asp:TemplateField>

        </Columns>

        </asp:GridView>

 <asp:LinqDataSource ID="LinqDataSource1" runat="server"

        ContextTypeName="DataClassesDataContext" TableName="Employees"

        GroupBy="new(Dept.Department,Location)"

        Select="new (key.Location as City,key.Department as Department, it as Employees,Max(Age) as MaxAge)">

    </asp:LinqDataSource>

When the above code is executed, we will get an output similar to below figure.