Sorting with DataView (LINQ to DataSet)
The ability to sort data based on specific criteria and then present the data to a client through a UI control is an important aspect of data binding. DataView provides several ways to sort data and return data rows ordered by specific ordering criteria. In addition to its string-based sorting capabilities, DataView also enables you to use Language-Integrated Query (LINQ) expressions for the sorting criteria. LINQ expressions allow for much more complex and powerful sorting operations than string-based sorting. This topic describes both approaches to sorting using DataView.
Creating DataView from a Query with Sorting Information
A DataView object can be created from a LINQ to DataSet query. If that query contains an OrderBy, OrderByDescending, ThenBy, or ThenByDescending clause the expressions in these clauses are used as the basis for sorting the data in the DataView. For example, if the query contains the Order By…and Then By… clauses, the resulting DataView would order the data by both columns specified.
Expression-based sorting offers more powerful and complex sorting than the simpler string-based sorting. Note that string-based and expression-based sorting are mutually exclusive. If the string-based Sort is set after a DataView is created from a query, the expression-based filter inferred from the query is cleared and cannot be reset.
The index for a DataView is built both when the DataView is created and when any of the sorting or filtering information is modified. You get the best performance by supplying sorting criteria in the LINQ to DataSet query that the DataView is created from and not modifying the sorting information, later. For more information, see DataView Performance.
Note
In most cases, the expressions used for sorting should not have side effects and must be deterministic. Also, the expressions should not contain any logic that depends on a set number of executions, because the sorting operations might be executed any number of times.
Example
The following example queries the SalesOrderHeader table and orders the returned rows by the order date; creates a DataView from that query; and binds the DataView to a BindingSource.
DataTable orders = _dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
orderby order.Field<DateTime>("OrderDate")
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Order By order.Field(Of DateTime)("OrderDate") _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
Example
The following example queries the SalesOrderHeader table and orders the returned row by total amount due; creates a DataView from that query; and binds the DataView to a BindingSource.
DataTable orders = _dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query =
from order in orders.AsEnumerable()
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() _
Order By order.Field(Of Decimal)("TotalDue") _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
Example
The following example queries the SalesOrderDetail table and orders the returned rows by order quantity and then by sales order ID; creates a DataView from that query; and binds the DataView to a BindingSource.
DataTable orders = _dataSet.Tables["SalesOrderDetail"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
orderby order.Field<short>("OrderQty"), order.Field<int>("SalesOrderID")
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
Dim orders As DataTable = dataSet.Tables("SalesOrderDetail")
Dim query = _
From order In orders.AsEnumerable() _
Order By order.Field(Of Int16)("OrderQty"), order.Field(Of Integer)("SalesOrderID") _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
Using the String-Based Sort Property
The string-based sorting functionality of DataView still works with LINQ to DataSet. After a DataView has been created from a LINQ to DataSet query, you can use the Sort property to set the sorting on the DataView.
The string-based and expression-based sorting functionality are mutually exclusive. Setting the Sort property will clear the expression-based sort inherited from the query that the DataView was created from.
For more information about string-based Sort filtering, see Sorting and Filtering Data.
Example
The follow example creates a DataView from the Contact table and sorts the rows by last name in descending order, then first name in ascending 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()
Example
The following example queries the Contact table for last names that start with the letter "S". A DataView is created from that query and bound to a BindingSource object.
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"
Clearing the Sort
The sorting information on a DataView can be cleared after it has been set using the Sort property. There are two ways to clear the sorting information in DataView:
Example
The following example creates a DataView from a query and clears the sorting by setting the Sort property to an empty string:
DataTable orders = _dataSet.Tables["SalesOrderHeader"];
EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
orderby order.Field<decimal>("TotalDue")
select order;
DataView view = query.AsDataView();
bindingSource1.DataSource = view;
view.Sort = "";
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")
Dim query = _
From order In orders.AsEnumerable() _
Order By order.Field(Of Decimal)("TotalDue") _
Select order
Dim view As DataView = query.AsDataView()
bindingSource1.DataSource = view
view.Sort = ""
Example
The following example creates a DataView from the Contact table and sets the Sort property to sort by last name in descending order. The sorting information is then cleared by setting the Sort property to null:
DataTable contacts = _dataSet.Tables["Contact"];
DataView view = contacts.AsDataView();
view.Sort = "LastName desc";
bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
// Clear the sort.
view.Sort = null;
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim view As DataView = contacts.AsDataView()
view.Sort = "LastName desc"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()
'Clear the sort.
view.Sort = Nothing