LinqDataSource Web Server Control Overview

The LinqDataSource control exposes Language-Integrated Query (LINQ) to Web developers through the ASP.NET data-source control architecture. LINQ provides a unified programming model for querying and updating data from different types of data sources, and extends data capabilities directly into the C# and Visual Basic languages. LINQ simplifies the interaction between object-oriented programming and relational data by applying the principles of object-oriented programming to relational data. For more information about LINQ, see Language-Integrated Query (LINQ).

By using declarative markup, you can create a LinqDataSource control that connects to data from either a database or an in-memory data collection such as an array. In the declarative text, you can write all the conditions that are required to retrieve, filter, order, and group the data. When you retrieve data from a SQL database table, you can also configure a LinqDataSource control to handle updating, inserting, and deleting data. The control does this without requiring you to write the SQL commands to perform these tasks. The LinqDataSource class also provides an event model that enables you to handle customized scenarios.

This topic contains:

• Scenarios

• Background

• Code Examples

• Class Reference

Scenarios

You use the LinqDataSource control when you create a Web page that retrieves or modifies data and you want to take advantage of the unified programming model that is provided by LINQ. You can simplify the code in a Web page by enabling the LinqDataSource control to automatically create the commands for interacting with the data.

Back to top

Background

The LinqDataSource control gives you a way to connect a data control to a wide variety of data sources. This includes database data, data-source classes, and in-memory collections. The LinqDataSource control lets you to specify database-like retrieval tasks (selecting, filtering, grouping, and ordering) against all these types of data sources. You can specify modification tasks (updating, deleting, and inserting) against database tables.

You can connect the LinqDataSource control to any kind of data collection that is stored in a public field or property. The declarative markup and code to perform data operations are the same for all data sources. You do not have to use different syntax when you interact with data from a database table or data from a data collection like an array.

For an introduction to the LinqDataSource control, see Walkthrough: Retrieving, Updating, Inserting, and Deleting Data with the LinqDataSource and DetailsView Controls.

Connecting to Data from a Database

When you interact with data from a database, you do not connect the LinqDataSource control directly to the database. Instead, you interact with entity classes that represent the database and the tables. You can generate the entity classes through the Object Relational Designer or by running the SqlMetal.exe utility. For more information, see Object Relational Designer (O/R Designer) or Code Generation Tool (SqlMetal.exe). The entity classes that you create will typically be located in the App_Code folder of the Web application. The O/R Designer or the SqlMetal.exe utility will generate one class that represents the database and one class for each table in the database.

The class that represents the database is responsible for retrieving and setting values in the data source. The LinqDataSource control reads and sets the properties in the class that represents the data table. To support update, insert, and delete operations, the database class must derive from the DataContext class and the table class must refer to a Table<TEntity> class.

You connect the LinqDataSource control to a database class by setting the ContextTypeName property to the name of the class that represents the database. You connect the LinqDataSource control to a particular table by setting the TableName property to the name of the class that represents the data table. For example, to connect to the Contacts table in the AdventureWorks database, you set the ContextTypeName property to AdventureWorksDataContext (or whatever name that you specify for the database object). You set the TableName property to Contacts. The following example shows the markup for a LinqDataSource control that connects to the AdventureWorks database.

<asp:LinqDataSource 
    ContextTypeName="AdventureWorksDataContext" 
    TableName="Contacts" 
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>

The example does not show the class that is generated by the O/R Designer, because the generated code is too long for this topic. However, the generated code must exist for the example to work. For more information, see Code Generation in LINQ to SQL.

Connecting to Data from an In-memory Collection

When you connect to in-memory data collection like an array, you set the ContextTypeName property to the name of the class that contains the collection. You set the TableName property to the name of the collection itself.

The following example shows a class that contains an array of string values.

Public Class MovieLibrary
    Dim _availableGenres() As String = {"Comedy", "Drama", "Romance"}

    Public ReadOnly Property AvailableGenres() As String()
        Get
            Return _availableGenres
        End Get
    End Property

End Class

public class MovieLibrary
{
    string[] _availableGenres = { "Comedy", "Drama", "Romance" };

    public MovieLibrary()
    {
    }

    public string[] AvailableGenres
    {
        get
        {
            return _availableGenres;
        }
    }
}

The following example shows a LinqDataSource control that reads the list of movie genres from the class in the previous example. To retrieve the array of genres, you set the ContextTypeName property to MovieLibrary and set the TableName property to AvailableGenres.

<asp:LinqDataSource 
    ContextTypeName="MovieLibrary" 
    TableName="AvailableGenres" 
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>

Using the LinqDataSource Control with Data-bound Controls

To display the data from a LinqDataSource control, you can bind a data-bound control to the LinqDataSource control. For example, you bind a DetailsView control, a GridView control, or a ListView control to a LinqDataSource control. To do this, you set the DataSourceID property of the data-bound control to the ID of the LinqDataSource control. The following example shows a GridView control that displays all the data from a LinqDataSource control.

<asp:LinqDataSource 
    runat="server"
    ContextTypeName="AdventureWorksDataContext" 
    TableName="Contacts" 
    ID="LinqDataSource1">
</asp:LinqDataSource>
<asp:GridView 
    ID="GridView1" 
    runat="server"
    DataSourceID="LinqDataSource1" >
</asp:GridView>

The data-bound control automatically creates the user interface to display the data from the LinqDataSource control. It also can provide the interface for sorting and paging the data. When data modifications are enabled, a data-bound control provides the interface for updating, inserting, and deleting records.

You can restrict which data (properties) are displayed by configuring the data-bound control not to automatically generate data control fields. You can then define those fields explicitly in the data-bound control. The LinqDataSource control retrieves all the properties, but the data-bound control displays only the properties that you specify. The following example shows a GridView control that displays only the Name and StandardCost properties from the Products table in the AdventureWorks database. The AutoGenerateColumns property is set to false.

<asp:LinqDataSource 
    ContextTypeName="AdventureWorksDataContext" 
    TableName="Products" 
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>
<asp:GridView 
    DataSourceID="LinqDataSource1" 
    AutoGenerateColumns="false"
    ID="GridView1" 
    runat="server">
    <Columns>
        <asp:BoundField DataField="Name" />
        <asp:BoundField DataField="StandardCost" />
    </Columns>
</asp:GridView>

If you have to restrict which properties are returned in the query, you define those properties by setting the Select property of the LinqDataSource control.

Comparing the LinqDataSource Control to other Data Source Controls

By using the LinqDataSource control, you can write less code to perform data operations in comparison to working with the SqlDataSource or the ObjectDataSource controls. The LinqDataSource control can infer information about the data source that you are connecting to and dynamically create the commands for selecting, updating, inserting, and deleting data. When you use the LinqDataSource control, you must also learn only one programming model to interact with different types of data sources.

Comparing the SqlDataSource Control

Unlike the SqlDataSource control, which works only with relational database tables, the LinqDataSource control enables you to connect to data that is stored in in-memory collections. When you use the SqlDataSource control, you must explicitly set the SelectCommand, UpdateCommand, InsertCommand, and DeleteCommand properties to SQL queries. However, with the LinqDataSource control you do not have to explicitly set these commands, because the LinqDataSource control uses LINQ to SQL to create them automatically. If you want to modify which columns are selected from a data source, you do not have to write a complete SQL Select command. Instead, you provide the names of the columns you want to return in the query in the Select property.

When you update or insert data, you do not have to create parameters for every value that will be saved in the database. The LinqDataSource control can create update commands that include the appropriate values by matching the DataField property to the property names in the entity class.

Comparing the ObjectDataSource Control

When you use the ObjectDataSource control, you must manually create the object that represents the data and then write the methods for interacting with the data. You must then match the SelectMethod, UpdateMethod, InsertMethod, and DeleteMethod properties to the methods that perform those functions. In the LinqDataSource control, you use the O/R Designer to automatically create the classes that represent the data. You do not have to write code to specify which columns exist in the database table or how to select, update, insert, and delete data. You can also use the LinqDataSource control to interact directly with a data collection like an array. In that case, you do not have to create a class to handle the logic for interacting with the data collection.

Selecting Data

If you do not specify a value for the Select property of the LinqDataSource control, all the properties in the data source class are retrieved. For example, the LinqDataSource control returns a value for each column in a database table.

You can restrict which properties are retrieved from the data source by setting the Select property to the names of the properties that you want. If you want to return only one property, set the Select property to that property. For example, to return only the values in the City column from a database table, set the Select property to City. The LinqDataSource control will return a List<T> collection that contains correctly typed items from the property. If the City property is typed as text (string), selecting the City property returns a List<T> collection of string values.

To retrieve only some of the properties from the data class, you use the new function in the Select property and specify which columns to return. The new function is required because you are dynamically creating a class that contains only the properties that you have specified. For example, if you want to retrieve the City and PostalCode properties from a data source that contains complete addresses, you set the Select property to new(City, PostalCode). The LinqDataSource control will return a List<T> collection that contains instances of a class that includes those properties.

You do not have to use the new function when you select only one property, because the object that is returned is a simple collection of values for that property. However, for multiple properties, the LinqDataSource control must create a new class that includes the properties that you specify.

Calculating Values with the Select Clause

You can calculate values in a Select clause. For example, to calculate a line-item total for an order, set the Select property to new(SalesOrderDetailID, OrderQty * UnitPrice As LineItemTotal). The As keyword enables you to assign a name (alias) to the calculated value. For more information, see Walkthrough: Selecting and Filtering a Subset of Data with the LinqDataSource and GridView Controls.

The following example shows how to use a LinqDataSource control to retrieve a subset of data. In the example, the Select property is set to assign an alias to the returned values and to calculate a value.

<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="OrderDetails" 
    Select="new(SalesOrderDetailID As DetailID, 
      OrderQty * UnitPrice As LineItemTotal, 
      DateCreated As SaleDate)"
    ID="LinqDataSource1" 
    runat="server">
</asp:LinqDataSource>

Filtering Data with the Where Clause

You can filter the returned data to retrieve only the records that meet particular criteria. You do this by setting the Where property to the conditions that must be met for a record to be included in the returned data. If you do not specify a value for the Where property, all the records in the data source are retrieved. You filter by creating a filter expression, which uses a comparison to determine whether a record should be included. The comparison can be to a static value or to a variable value that you specify with a parameter placeholder.

Creating a Where Clause with Static Values

When you compare the value in a property to a static value, you define the Where property by using the property and the static value. For example, to return only the records that have a ListPrice value greater than 1000, set the Where property to ListPrice > 1000.

You can use the && or and operator for logical AND and the || or or operators for logical OR. For example, set the Where property to ListPrice > 1000 || UnitCost > 500 || DaysToManufacture > 3 to return records with a ListPrice value greater than 1000 or a UnitCost value greater than 500 or a DaysToManufacture value greater than 3. To specify that all conditions must be true to return a record, you would set the Where property to ListPrice > 1000 && UnitCost > 500 && DaysToManufacture > 3.

When you compare string values, you must enclose the conditions in single quotation marks and enclose the literal value in double quotation marks. For example, set the Where property to 'Category = "Sports"' to retrieve only the records with the Category column equal to "Sports".

The following example shows a LinqDataSource control that retrieves data that has been filtered by both a string value and a numeric value.

<asp:LinqDataSource
  ContextTypeName="ExampleDataContext"
  TableName="Product"
  Where='Category = "Sports" && Weight < 10'
  ID="LinqDataSource1"
  runat="server"
</asp:LinqDataSource>

Creating a Parameterized Where Clause

If you want to compare a property value to a value that is known only at run time, you define a parameter in the WhereParameters property collection. For example, if you want to filter by using a value that is provided by the user, you create a parameter that represents the value. The LinqDataSource control creates the Where clause with the current value of the parameter.

The following example shows a LinqDataSource control that retrieves data based on the user's selection in a control named DropDownList1.

<asp:DropDownList AutoPostBack="true" ID="DropDownList1" runat="server">
  <asp:ListItem Value="Sports">Sports</asp:ListItem>
  <asp:ListItem Value="Garden">Garden</asp:ListItem>
  <asp:ListItem Value="Auto">Auto</asp:ListItem>
</asp:DropDownList>
<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="Products" 
    AutoGenerateWhereClause="true"
    ID="LinqDataSource1" 
    runat="server">
  <WhereParameters>
    <asp:ControlParameter 
      Name="Category" 
      ControlID="DropDownList1" 
      Type="String" />
  </WhereParameters>
</asp:LinqDataSource>
<asp:GridView 
    DataSourceID="LinqDataSource1"
    ID="GridView1" 
    runat="server">
</asp:GridView>

The LinqDataSource control automatically creates the Where clause when you set the AutoGenerateWhereClause property to true. This option is helpful when you have several parameters, because you do not have to specify each condition in the Where property. Instead you add the parameters in the WhereParameters property collection and the LinqDataSource control creates a Where clause that includes each parameter.

When you set the AutoGenerateWhereClause property to true, the name of the parameters must match the name of the corresponding properties. For example, to check the value of a parameter against the Category property, the parameter must be named Category. All comparisons are for equality; you cannot test whether a value is greater than or less than the parameter value. When you specify more than one parameter in the WhereParameters collection, the parameters are linked with a logical AND.

If you have to check for inequality or link conditions with a logical OR, you set the AutoGenerateWhereClause property to false. You can then define the conditions in the Where property. You include a placeholder in the Where property for each parameter.

The following example shows how to test for inequality when you filter the data.

<asp:DropDownList 
    AutoPostBack="true" 
    ID="DropDownList1" 
    runat="server">
  <asp:ListItem Value="0">0</asp:ListItem>
  <asp:ListItem Value="25">25</asp:ListItem>
  <asp:ListItem Value="100">100</asp:ListItem>
  <asp:ListItem Value="400">400</asp:ListItem>
</asp:DropDownList>
<asp:LinqDataSource 
    ContextTypeName="ExampleDataContext" 
    TableName="Products" 
    Where="Price > @UserPrice"
    ID="LinqDataSource1" 
    runat="server">
  <WhereParameters>
    <asp:ControlParameter 
      Name="UserPrice" 
      DefaultValue="0" 
      ControlID="DropDownList1" 
      Type="Int32" />
  </WhereParameters>
</asp:LinqDataSource>
<asp:GridView 
    DataSourceID="LinqDataSource1"
    ID="GridView1" 
    runat="server">
</asp:GridView>

For more information, see AutoGenerateWhereClause.

Grouping and Aggregating Data

You can group data in order to consolidate the data for records that have common values in one or more columns. You do this by setting the GroupBy property to the name of the properties that you want to use for consolidating the data. For example, to group records with the same city value from a table that contains address information, set GroupBy to City.

You can calculate values on the grouped data, such as the average or sum of a property, by using aggregate functions in combination with the GroupBy property. You retrieve the values that are used for grouping by referencing the Key object.

The following example shows how to group the data on a property named ProductCategory. The grouped values are retrieved by including Key in the Select property. The Average and Count aggregate methods are also included in the Select property.

<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>

You can retrieve the individual records that belong to a group by using the It keyword. For more information, see How to: Group and Aggregate Data Using the LinqDataSource Control.

Aggregate Methods

The following table lists the aggregate methods that are available when you use the LinqDataSource control.

• For more information, see How to: Group and Aggregate Data Using the LinqDataSource Control.

Updating, Inserting, and Deleting Data

You can configure the LinqDataSource control to automatically create the commands for updating, inserting, and deleting data. To enable automatic data updates, set the EnableUpdate, EnableInsert, or EnableDelete property to true.

If you want to programmatically set any values to be updated, you can create event handlers for the Updating, Inserting, or Deleting events. In the handler, you can set a value before the data operation starts. For more information, see How to: Update, Insert, and Delete Data with the LinqDataSource Control.

Sorting Data

The LinqDataSource object supports two ways to sort data from a query. You can sort the data by static values when you develop the Web page. You can also enable users to dynamically sort the data at run time.

To order the data according to static values, assign the name of a property to the OrderBy property. To let users sort the data at run time, set the AutoSort property to true (which is the default value). Then pass a sort expression to the LinqDataSource control. A data-bound control such as the GridView control will pass a sort expression when the its AllowSorting property is set to true.

To specify more than one column name in the OrderBy property, separate the names with a comma. For example, if you specify "LastName, FirstName", the records will first be ordered by LastName and records that contain matching values in the LastName field will be sorted by FirstName.

You can use both ways of sorting when you want to first return the data in a particular order, and then let users change that order. In that case, you set the AutoSort property to true and set the OrderBy property to the name of a property.

The following example shows a LinqDataSource control that first orders the records by LastName, then FirstName, and then MiddleName. The LinqDataSource control is also configured to enable users to dynamically sort the rows. You can bind a data control such as the GridView control to the LinqDataSource control to display the data and enable users to specify a sort order.

<asp:LinqDataSource
  ContextTypeName="ExampleDataContext"
  TableName="Contact"
  OrderBy="LastName, FirstName, MiddleName"
  AutoSort="true"
  ID="LinqDataSource1"
  runat="server"
</asp:LinqDataSource>

Using Parameters to Evaluate Values Dynamically

When you use the LinqDataSource control, you use parameters to specify values that must be interpreted or converted at run time. Unlike using other data source controls, you do not have to specify each value that is part of the update, insert, or delete operation. The LinqDataSource control automatically sets those values and uses LINQ to SQL to generate the SQL commands. You specify parameters only to provide default values or to convert empty values. You can also use the WhereParameters and OrderByParameters parameter collections to order or filter data based on values at run time. For more information about LINQ to SQL, see LINQ to SQL.

The LinqDataSource control contains the following parameter collections:

• DeleteParameters

• GroupByParameters

• InsertParameters

• OrderByParameters

• SelectParameters

• UpdateParameters

• WhereParameters.

For an example of how to use the WhereParameters collection to let users dynamically filter the records that are returned from a LinqDataSource control, see Filtering Data with the Where Clause previously in this topic.

Concurrency Control

When you enable users to update or delete data, you might want to make sure that the data in the data source has not been changed by another process before you continue with the update or delete. If you do not check whether the values have changed, you can unintentionally overwrite values set by another process and leave the data in an inconsistent state.

The LinqDataSource control enables you to determine whether the data has changed. The control stores the original data values in view state in the Web page. When the Web page is posted back, the page contains both the original values and any updated values. The LinqDataSource control uses LINQ to SQL to compare the original values with the values currently in the data source. If the values are the same, the data has not changed, and LINQ to SQL updates or deletes the data. If the data has changed, LINQ to SQL raises an exception.

You can manually specify which column values are checked by setting the UpdateCheck property of a column in the entity class. When O/R Designer generates code for a database table, it sets the UpdateCheck property to Never for columns that are managed by the database. The value for any other column is set to Always, which means that it is always included in a concurrency check. Set the UpdateCheck property to Never to prevent that column from being included in concurrency checking. Setting the property to Never also prevents values from that column from being stored in view state. Set the UpdateCheck property to WhenChanged to check the value against the data source only when the user has changed the value.

If you must store sensitive data in view state, you should encrypt view state. For more information, see Encrypt ViewState in ASP.NET 2.0.

Optimizing Concurrency Checks for SQL Server Data

Storing many values in view state can expand the size of a Web page and potentially expose sensitive data to users. If you are working with data from a SQL Server database, you can improve the performance and security of a Web page by creating a column in the database that contains a timestamp value. SQL Server automatically updates a timestamp column every time that the record is modified. In that case, the LinqDataSource control does not have to compare every column in a record to determine whether the record has changed. Instead, it can compare only the timestamp in view state with the timestamp in the database.

If you add a timestamp column in a SQL Server database, the O/R Designer automatically creates the entity class so that only the timestamp is stored in view state. For more information, see Walkthrough: Using a Timestamp with the LinqDataSource Control to Check Data Integrity.

Using Stored Procedures

To use the LinqDataSource control to retrieve data from a stored procedure, you create a handler for the Selecting event. In the event handler, you call the method of the data-context class that represents the stored procedure. You then set the result of the stored procedure to the Result property of the LinqDataSourceSelectEventArgs object. If you want to enable automatic update, insert, and delete operations, the type returned from the method must match the type that is specified in the TableName property.

The following example shows to assign the Result property to the object that is returned from a method that represents a stored procedure.

Protected Sub LinqDataSource_Selecting(ByVal sender As Object, _
        ByVal e As LinqDataSourceSelectEventArgs)
    Dim exampleContext As ExampleDataContext = New ExampleDataContext()
    e.Result = exampleContext.GetRegisteredCustomers()
End Sub

protected void LinqDataSource_Selecting(object sender, 
        LinqDataSourceSelectEventArgs e)
{
    ExampleDataContext exampleContext = new ExampleDataContext();
    e.Result = exampleContext.GetRegisteredCustomers();
}

For more information about creating data context methods, see How to: Create DataContext Methods Mapped to Stored Procedures and Functions (O/R Designer).

Validating Values When Modifying Data

Before you modify a data record, you might have to validate the values. Typically, you include validation checks in the data class and not in the Web page. By centralizing the validation checks in the data class, you make sure that the validation checks can be used regardless of how the data class is used. For more information about how to add validation to a class, see How to: Add Validation to Entity Classes and Walkthrough: Adding Validation to Entity Classes.

Validation exceptions are thrown before data is updated, inserted, or deleted. To retrieve validation exceptions, you can create handlers for the Inserting, Updating, and Deleting events. For more information, see the LinqDataSourceUpdateEventArgs, LinqDataSourceInsertEventArgs, and LinqDataSourceDeleteEventArgs classes.

LinqDataSource Control Events

The LinqDataSource control raises events at specific points during its processing to enable you to customize how data is selected, inserted, updated, or deleted.

When it selects data, the LinqDataSource control raises the following events (in this order):

1. Selecting

2. ContextCreating (if needed)

3. ContextCreated (if needed)

4. Selected

5. ContextDisposing

If you programmatically create the context object in the Selecting event and you do not have to store original values in the view state, the ContextCreating and ContextCreated events are skipped

When it updates, inserts, or deletes data, the LinqDataSource control raises the following events (in this order):

1. ContextCreating

2. ContextCreated

3. Inserting or Updating, or Deleting

4. Inserted or Updated, or Deleted

5. ContextDisposing

The order of the events for update, delete, and insert operations differs from select operations. This is because the LinqDataSource control must get an instance of the data object and pass that instance to the Inserting, Updating, or Deleting events.

The Selecting, Inserting, Updating, and Deleting events are raised before the data operation is executed. You create event handlers for these events to validate and modify values before the operation, or to cancel the operation. For example, if the database contains a column that stores which user last modified a record, you can use the Updating event to set the user name value programmatically.

The Selected, Inserted, Updated, and Deleted events are raised after the operation finishes. You create event handlers for these events to catch exceptions and to examine values that are returned from the operation.

The ContextCreating event is raised before the LinqDataSource control creates the type specified in the ContextTypeName property. You handle this event if you want to create the context type programmatically. For example, you might do this if the constructor method of the context type requires parameters. The ContextCreated event is raised after the type specified in the ContextTypeName property is created. You handle this event to catch exceptions or to examine the context object that was created. The ContextDisposing event is raised before the LinqDataSource control disposes the context type specified in the ContextTypeName property. You handle this event in order to cancel disposing an object that is very expensive to create (in time or resources). You can also use the event to perform clean-up that is specific to the object before the object is destroyed.

Back to top

Code Examples

Walkthrough: Retrieving, Updating, Inserting, and Deleting Data with the LinqDataSource and DetailsView Controls

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

Walkthrough: Using a Timestamp with the LinqDataSource Control to Check Data Integrity

How to: Group and Aggregate Data Using the LinqDataSource Control

How to: Update, Insert, and Delete Data with the LinqDataSource Control

Back to top

Class Reference

The following table lists the key classes that relate to the LinqDataSource class.

Back to top

See Also

Other Resources

Language-Integrated Query (LINQ)

LINQ to SQL

Object Relational Designer (O/R Designer)