Walkthrough: Selecting and Filtering a Subset of Data with the LinqDataSource and GridView Controls

This walkthrough describes how to use the LinqDataSource control to create a set of data that is not limited to the values from a single database table. You will use a LinqDataSource control to select a subset of data from the table, calculate new values from the selected values, and retrieve values from related tables. You will also filter which records are returned. You will then display the new set of values by using a GridView control.

The walkthrough illustrates data access by selecting values primarily from the Product table in the AdventureWorks sample database. The Product table contains columns that are foreign keys to other tables, and you will retrieve data from those related tables.

You will use the Object Relational Designer to create a class that represents the database tables that contain the values. The LinqDataSource control will interact with this generated class to retrieve and update data. 

Prerequisites

To implement the procedures in this walkthrough, you will need:

• Visual Studio 2008 or Visual Web Developer Express.

• An ASP.NET Web site.

• SQL Server Express Edition. If you have SQL Server installed, you can use that, but you must make small adjustments to some of the procedures.

• The AdventureWorks database installed on your computer. To download the AdventureWorks database, see SQL Server 2005 Samples and Sample Databases.

• A connection string in the Web site that connects to the AdventureWorks database.

Creating Classes to Represent Database Entities

To work with database data by using the LinqDataSource control, you create classes that represent database entities. You can use a tool in Visual Studio 2008 to create these classes.

To create classes for AdventureWorks database tables

1. If the Web site does not already have an App_Code folder, in Solution Explorer, right-click the name of the project, click Add ASP.NET Folder, and then click App_Code.

2. Right-click the App_Code folder and then click Add New Item.

   The Add New Item dialog box is displayed.

3. Under Visual Studio installed templates, select LINQ to SQL Classes, name the file AdventureWorks.dbml, and then click Add.

   The Object Relational Designer is displayed.

4. In Server Explorer, drag the Product (Production) table into the Object Relational Designer window.

   The Product (Production) table and its columns are represented in the designer window as an entity named Product.

5. Drag the ProductSubCategory table and the UnitMeasure table into the designer window.

   These tables and their columns are represented as entities in the designer window. The relationship between Product and the two related tables is displayed with the dotted line.

   

6. Save the AdventureWorks.dbml file.

7. In Solution Explorer, open the AdventureWorks.designer.cs or AdventureWorks.designer.vb file.

   The file has classes named AdventureWorksDataContext, Product, ProductSubCategory, and UnitMeasure. The Product class contains properties named ProductSubcategory, UnitMeasure, and UnitMeasure1. These properties are marked as foreign keys by using the AssociationAttribute attribute. They return objects that represent the ProductSubCategory and UnitMeasure tables.

   The UnitMeasure property represents the foreign-key relationship for the value in the SizeUnitMeasureCode column. The UnitMeasure1 property represents the foreign key for the WeightUnitMeasureCode column.

Selecting Which Columns to Retrieve

If you do not specify a value for the Select property, the LinqDataSource control returns all the properties (columns) from the class that represents the database table.

To retrieve a subset of data with the LinqDataSource control

1. In Visual Studio, create a new ASP.NET Web page and switch to Source view.

2. From the Data tab of the toolbox, drag a LinqDataSource control and drop it inside the form element on the Web page.

   You can leave the ID property as LinqDataSource1.

3. Set the ContextTypeName property to AdventureWorksDataContext.

   The AdventureWorksDataContext class is a data context class that represents the AdventureWorks database. It contains a property for each table in the database.

4. Set the TableName property to Products.

5. Set the Select property to the following:

   new(Name, Size, StandardCost, ListPrice, DaysToManufacture)
   

   By setting the Select property, you restrict which properties are retrieved from the Products class. When you select more than one property from a class, you must enclose the properties in the new operator. This is because the LinqDataSource control is not returning an instance of the class specified in the TableName property. Instead, it returns an instance of a dynamically created class that contains only these properties.

Filtering Which Records to Retrieve

If you do not specify a value for the Where property, the LinqDataSource control returns all the records from the database table. You can filter which records are returned by setting the Where property. You can filter by a static value such as ListPrice > 0 to return only records that have value in the ListPrice property greater than zero. You can also assign a value at run time by adding parameters to the WhereParameters collection.

To filter the data at run time with the LinqDataSource control

1. Add a DropDownList control to the Web page and set its AutoPostBack property to true.

   Leave the default name DropDownList1.

2. Add four list items to the DropDownList1 control, and set their values to 0, 25, 100, and 400.

3. Add a WhereParameters element between the opening and closing tag for the LinqDataSource control.

4. Add a ControlParameter control to the WhereParameters collection.

5. Set the ControlID property of the ControlParameter control to DropDownList1, set the Name property to SelectedPrice, and set the Type property to Int32.

   At run time, the SelectedPrice parameter will contain the selected value from DropDownList1.

6. Set the Where property to ListPrice > @SelectedPrice to select only the records whose ListPrice value is greater than the value selected by the user.

   The following example shows the declarative markup for the DropDownList and LinqDataSource controls.

   <asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server">
     <asp:ListItem Value="0"></asp:ListItem>
     <asp:ListItem Value="25"></asp:ListItem>
     <asp:ListItem Value="100"></asp:ListItem>
     <asp:ListItem Value="400"></asp:ListItem>
   </asp:DropDownList>
   <asp:LinqDataSource 
     ContextTypeName="AdventureWorksDataContext" 
     TableName="Products" 
     Where="ListPrice > @SelectedPrice" 
     Select="new(Name, Size, StandardCost, ListPrice, DaysToManufacture)" 
     ID="LinqDataSource1" 
     runat="server">
     <WhereParameters>
       <asp:ControlParameter 
         Name="SelectedPrice" 
         DefaultValue="0" 
         ControlID="DropDownList1" 
         Type="Int32" />
     </WhereParameters>
   </asp:LinqDataSource>
   

Adding a Control to Display Data

You can now add a GridView control and bind it to the LinqDataSource control. The GridView control enables users to view rows of data that are managed by the LinqDataSource control. You will enable users to sort and page through the data.

To display the subset of data in the GridView control

1. In the Data tab of the Toolbox, double-click the GridView control to add it to the page.

2. Set the DataSourceID property of the GridView control to LinqDataSource1.

   This binds the GridView control to the data returned by the LinqDataSource control.

3. Set the AllowPaging and AllowSorting properties to true.

   For sorting and paging to work with a LinqDataSource control, the AutoSort and AutoPage properties must be true. By default, both of these values are true.

4. Set the AutoGenerateColumns property to false.

5. To specify the order of the columns, create a BoundField control for each property (Name, Size, StandardCost, ListPrice, and DaysToManufacture) that you specified in the Select property of the LinqDataSource control.

   The following example shows the declarative markup for the GridView control.

   <asp:GridView 
     AllowPaging="true" 
     AllowSorting="true" 
     AutoGenerateColumns="false" 
     DataSourceID="LinqDataSource1" 
     ID="GridView1" 
     runat="server">
     <Columns>
       <asp:BoundField 
           DataField="Name" 
           HeaderText="Name" 
           SortExpression="Name" />
       <asp:BoundField 
           DataField="Size" 
           HeaderText="Size" 
           SortExpression="Size" />
       <asp:BoundField 
           DataField="StandardCost" 
           HeaderText="Standard Cost" 
           SortExpression="StandardCost" />
       <asp:BoundField 
           DataField="ListPrice" 
           HeaderText="List Price" 
           SortExpression="ListPrice" />
       <asp:BoundField 
           DataField="DaysToManufacture" 
           HeaderText="Days To Manufacture" 
           SortExpression="DaysToManufacture" />
     </Columns>
   </asp:GridView>
   

6. Save the page and press CTRL+F5 to view the page in a browser.

   The GridView control displays the columns for the current records from the Product table. You can sort the records by clicking the column headings, and you can page through the records by clicking page numbers.

Using Data Source Values to Calculate New Values

In addition to selecting values from the database table, you can calculate new values. You might do this if you want to display a value that can only be derived by performing a calculation on a value from the data source. You can then display those values in the GridView control.

To calculate and display new values from data source

1. Change the value of the Select property to the following:

   new(Name, 
       Size, 
       StandardCost, 
       ListPrice, 
       ListPrice - StandardCost as PriceDifference, 
       DaysToManufacture / 5.0 as WeeksToManufacture)
   

   The additional values, PriceDifference and WeeksToManufacture, do not exist in the database. They can be displayed only after a calculation is performed on values in the database.

2. In the GridView control, add BoundField controls for the PriceDifference and WeeksToManufacture columns, and remove the BoundField control for DaysToManufacture.

   The following example shows the declarative markup for the calculated BoundField controls.

       <asp:BoundField 
           DataField="PriceDifference" 
           HeaderText="Price Difference" 
           SortExpression="PriceDifference" />
       <asp:BoundField 
           DataField="WeeksToManufacture" 
           HeaderText="Weeks To Manufacture" 
           SortExpression="WeeksToManufacture" />
   

3. Save the page and press CTRL+F5 to view the page in a browser.

   The GridView control displays the columns for the current records and the calculated values from the Product table.

Selecting Values from Related Tables

You can retrieve values from a table and values from any related tables with one LinqDataSource control. When a table has foreign-key relationship with other tables, the Object Relational Designer generates an entity class that contains properties for each related table. These properties return an object that represents the related table. The object in turn contains properties for all the columns in the related table. You can select values from related tables by accessing the objects in the class that represent the related tables.

To retrieve data from related tables

1. Set the Select property of the LinqDataSource control to the following:

   new(Name, 
       Size, 
       UnitMeasure.Name as SizeMeasureName, 
       Weight, 
       UnitMeasure1.Name as WeightMeasureName, 
       ProductSubcategory.Name as SubCategoryName)
   

   The SizeMeasureName, WeightMeasureName, and SubCategoryName properties contain values from tables that have foreign-key relationships to the Products table.

2. To return only the products that have a size or weight unit of measurement, set the Where property to the following:

   WeightUnitMeasureCode != null || SizeUnitMeasureCode != null
   

   Filtering on these values illustrates how foreign-key relationships are managed by the LinqDataSource control. The || operator performs a logical OR operation. Therefore only records that have a value assigned to at least one of the foreign keys will be returned.

   The following example shows the declarative markup for the LinqDataSource control.

   <asp:LinqDataSource 
     ContextTypeName="AdventureWorksDataContext" 
     TableName="Products" 
     Where="WeightUnitMeasureCode != null || 
            SizeUnitMeasureCode != null"
     Select="new(Name,
                 Size, 
                 UnitMeasure.Name as SizeMeasureName, 
                 Weight, 
                 UnitMeasure1.Name as WeightMeasureName, 
                 ProductSubCategory.Name as SubCategoryName)" 
     ID="LinqDataSource1" 
     runat="server">
   </asp:LinqDataSource>
   

3. In the GridView control, add a BoundField control for each column to display. Those columns are Name, Size, SizeMeasureName, Weight, WeightMeasureName, and SubCategoryName.

   The following example shows the declarative markup for the GridView control.

   <asp:GridView 
     AllowPaging="true" 
     AllowSorting="true" 
     AutoGenerateColumns="false" 
     DataSourceID="LinqDataSource1" 
     ID="GridView1" 
     runat="server">
     <Columns>
       <asp:BoundField 
             DataField="Name" 
             HeaderText="Name" 
             SortExpression="Name" />
       <asp:BoundField 
             DataField="Size" 
             HeaderText="Size" 
             SortExpression="Size" />
       <asp:BoundField 
             DataField="SizeMeasureName" 
             HeaderText="Size Unit of Measurement" 
             SortExpression="SizeMeasureName" />
       <asp:BoundField 
             DataField="Weight" 
             HeaderText="Weight" 
             SortExpression="Weight" />
       <asp:BoundField 
             DataField="WeightMeasureName" 
             HeaderText="Weight Unit of Measurement" 
             SortExpression="WeightMeasureName" />
       <asp:BoundField 
             DataField="SubCategoryName" 
             HeaderText="Subcategory Name" 
             SortExpression="SubCategoryName" />
     </Columns>
   </asp:GridView>
   

4. Save the page and press CTRL+F5 to view the page in a browser.

   The GridView control displays the values from the Product, ProductSubCategory, and UnitMeasure tables. You can sort and page through the records.

Next Steps

In this walkthrough, you have learned how to customize which columns and records are returned in a query by using the LinqDataSource control. You can add more capabilities to the LinqDataSource control, such as the following:

• Enable update, insert, and delete operations. For more information, see Walkthrough: Retrieving, Updating, Inserting, and Deleting Data with the LinqDataSource and DetailsView Controls.

• Group data and aggregate values, such as finding the sum or average of column values. For more information, see How to: Group and Aggregate Data Using the LinqDataSource Control.

• Ensure that the data in the database has not changed between the time that you retrieve the data and when it is updated or deleted. For more information, see Walkthrough: Using a Timestamp with the LinqDataSource Control to Check Data Integrity.

See Also

Concepts

LinqDataSource Web Server Control Overview