Edit

Creating a DataView Object (LINQ to DataSet)

  • Article

There are two ways to create a DataView in the LINQ to DataSet context. You can create a DataView from a LINQ to DataSet query over a DataTable, or you can create it from a typed or un-typed DataTable. In both cases, you create the DataView by using one of the AsDataView extension methods; DataView is not directly constructible in the LINQ to DataSet context.

After the DataView has been created, you can bind it to a UI control in a Windows forms application or an ASP.NET application, or change the filtering and sorting settings.

DataView constructs an index, which significantly increases the performance of operations that can use the index, such as filtering and sorting. The index for a DataView is built both when the DataView is created and when any of the sorting or filtering information is modified. Creating a DataView and then setting the sorting or filtering information later causes the index to be built at least twice: once when the DataView is created, and again when any of the sort or filter properties are modified.

For more information about filtering and sorting with DataView, see Filtering with DataView and Sorting with DataView.

Creating DataView from a LINQ to DataSet Query

A DataView object can be created from the results of a LINQ to DataSet query, where the results are a projection of DataRow objects. The newly created DataView inherits the filtering and sorting information from the query it is created from.

Note

In most cases, the expressions used for filtering and sorting should not have side effects and must be deterministic. Also, the expressions should not contain any logic that depend on a set number of executions, as the sorting and filtering operations may be executed any number of times.

Creating a DataView from a query that returns anonymous types or queries that perform join operations is not supported.

Only the following query operators are supported in a query used to create DataView:

Note that when a DataView is created from a LINQ to DataSet query the Select method must be the final method called in the query. This is shown in the following example, which creates a DataView of online orders sorted by total due:

DataTable orders = _dataSet.Tables["SalesOrderHeader"];

EnumerableRowCollection<DataRow> query =
    from order in orders.AsEnumerable()
    where order.Field<bool>("OnlineOrderFlag")
    orderby order.Field<decimal>("TotalDue")
    select order;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;

Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of Boolean)("OnlineOrderFlag") = True _
    Order By order.Field(Of Decimal)("TotalDue") _
    Select order

Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view

You can also use the string-based RowFilter and Sort properties to filter and sort a DataView after it has been created from a query. Note that this will clear the sorting and filtering information inherited from the query. The following example creates a DataView from a LINQ to DataSet query that filters by last names that start with 'S'. The string-based Sort property is set to sort on last names in ascending order and then first names in descending order:

DataTable contacts = _dataSet.Tables["Contact"];

EnumerableRowCollection<DataRow> query = from contact in contacts.AsEnumerable()
                                         where contact.Field<string>("LastName").StartsWith("S")
                                         select contact;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;

view.Sort = "LastName desc, FirstName asc";

Dim contacts As DataTable = dataSet.Tables("Contact")

Dim query = _
    From contact In contacts.AsEnumerable() _
    Where contact.Field(Of String)("LastName").StartsWith("S") _
    Select contact

Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
view.Sort = "LastName desc, FirstName asc"

Creating a DataView from a DataTable

In addition to being created from a LINQ to DataSet query, a DataView object can be created from a DataTable by using the AsDataView method.

The following example creates a DataView from the SalesOrderDetail table and sets it as the data source of a BindingSource object. This object acts as a proxy for a DataGridView control.

DataTable orders = _dataSet.Tables["SalesOrderDetail"];

DataView view = orders.AsDataView();
bindingSource1.DataSource = view;

dataGridView1.AutoResizeColumns();

Dim orders As DataTable = dataSet.Tables("SalesOrderDetail")

Dim view As DataView = orders.AsDataView()
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()

Filtering and sorting can be set on the DataView after it has been created from a DataTable. The following example creates a DataView from the Contact table and sets the Sort property to sort on last names in ascending order and then first names in descending order:

DataTable contacts = _dataSet.Tables["Contact"];

DataView view = contacts.AsDataView();

view.Sort = "LastName desc, FirstName asc";

bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();

Dim contacts As DataTable = dataSet.Tables("Contact")

Dim view As DataView = contacts.AsDataView()

view.Sort = "LastName desc, FirstName asc"

bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()

However, there is a performance loss that comes with setting the RowFilter or Sort property after the DataView has been created from a query, because DataView constructs an index to support filtering and sorting operations. Setting the RowFilter or Sort property rebuilds the index for the data, adding overhead to your application and decreasing performance. When possible, it is better to specify the filtering and sorting information when you first create the DataView and avoid modifying it afterwards.

See also