Filtering with DataView (LINQ to DataSet)

The ability to filter data using 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 filter data and return subsets of data rows meeting specific filter criteria. In addition to the string-based filtering capabilities DataView also provides the ability to use LINQ expressions for the filtering criteria. LINQ expressions allow for much more complex and powerful filtering operations than the string-based filtering.

There are two ways to filter data using a DataView:

  • Create a DataView from a LINQ to DataSet query with a Where clause.

  • Use the existing, string-based filtering capabilities of DataView.

Creating DataView from a Query with Filtering Information

A DataView object can be created from a LINQ to DataSet query. If that query contains a Where clause, the DataView is created with the filtering information from the query. The expression in the Where clause is used to determine which data rows will be included in the DataView, and is the basis for the filter.

Expression-based filters offer more powerful and complex filtering than the simpler string-based filters. The string-based and expression-based filters are mutually exclusive. When the string-based RowFilter is set after a DataView is created from a query, the expression based filter inferred from the query is cleared.

Note

In most cases, the expressions used for filtering 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 filtering operations might be executed any number of times.

Example

The following example queries the SalesOrderDetail table for orders with a quantity greater than 2 and less than 6; 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()
                                         where order.Field<Int16>("OrderQty") > 2 && order.Field<Int16>("OrderQty") < 6 
                                         select order;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;
Dim orders As DataTable = dataSet.Tables("SalesOrderDetail")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of Int16)("OrderQty") > 2 And _
          order.Field(Of Int16)("OrderQty") < 6 _
    Select order

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

Example

The following example creates a DataView from a query for orders placed after June 6, 2001:

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

EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
                                         where order.Field<DateTime>("OrderDate") > new DateTime(2002, 6, 1) 
                                         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 DateTime)("OrderDate") > New DateTime(2002, 6, 1) _
    Select order

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

Example

Filtering can also be combined with sorting. The following example creates a DataView from a query for contacts whose last name start with "S" and sorted by last name, then first name:

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

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

DataView view = query.AsDataView();

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

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

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

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

Example

The following example uses the SoundEx algorithm to find contacts whose last name is similar to "Zhu". The SoundEx algorithm is implemented in the SoundEx method.

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

string soundExCode = SoundEx("Zhu");

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

DataView view = query.AsDataView();

bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
Dim contacts As DataTable = dataSet.Tables("Contact")
Dim soundExCode As String = SoundEx("Zhu")

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

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

SoundEx is a phonetic algorithm used for indexing names by sound, as they are pronounced in English, originally developed by the U.S. Census Bureau. The SoundEx method returns a four character code for a name consisting of an English letter followed by three numbers. The letter is the first letter of the name and the numbers encode the remaining consonants in the name. Similar sounding names share the same SoundEx code. The SoundEx implementation used in the SoundEx method of the previous example is shown here:

static private string SoundEx(string word)
{
    // The length of the returned code.
    int length = 4;

    // Value to return.
    string value = "";

    // The size of the word to process.
    int size = word.Length;

    // The word must be at least two characters in length.
    if (size > 1)
    {
        // Convert the word to uppercase characters.
        word = word.ToUpper(System.Globalization.CultureInfo.InvariantCulture);

        // Convert the word to a character array.
        char[] chars = word.ToCharArray();

        // Buffer to hold the character codes.
        StringBuilder buffer = new StringBuilder();
        buffer.Length = 0;

        // The current and previous character codes.
        int prevCode = 0;
        int currCode = 0;

        // Add the first character to the buffer.
        buffer.Append(chars[0]);

        // Loop through all the characters and convert them to the proper character code.
        for (int i = 1; i < size; i++)
        {
            switch (chars[i])
            {
                case 'A':
                case 'E':
                case 'I':
                case 'O':
                case 'U':
                case 'H':
                case 'W':
                case 'Y':
                    currCode = 0;
                    break;
                case 'B':
                case 'F':
                case 'P':
                case 'V':
                    currCode = 1;
                    break;
                case 'C':
                case 'G':
                case 'J':
                case 'K':
                case 'Q':
                case 'S':
                case 'X':
                case 'Z':
                    currCode = 2;
                    break;
                case 'D':
                case 'T':
                    currCode = 3;
                    break;
                case 'L':
                    currCode = 4;
                    break;
                case 'M':
                case 'N':
                    currCode = 5;
                    break;
                case 'R':
                    currCode = 6;
                    break;
            }

            // Check if the current code is the same as the previous code.
            if (currCode != prevCode)
            {
                // Check to see if the current code is 0 (a vowel); do not process vowels.
                if (currCode != 0)
                    buffer.Append(currCode);
            }
            // Set the previous character code.
            prevCode = currCode;

            // If the buffer size meets the length limit, exit the loop.
            if (buffer.Length == length)
                break;
        }
        // Pad the buffer, if required.
        size = buffer.Length;
        if (size < length)
            buffer.Append('0', (length - size));

        // Set the value to return.
        value = buffer.ToString();
    }
    // Return the value.
    return value;            
}
Private Function SoundEx(ByVal word As String) As String

    Dim length As Integer = 4
    ' Value to return
    Dim value As String = ""
    ' Size of the word to process
    Dim size As Integer = word.Length
    ' Make sure the word is at least two characters in length
    If (size > 1) Then
        ' Convert the word to all uppercase
        word = word.ToUpper(System.Globalization.CultureInfo.InvariantCulture)
        ' Convert the word to character array for faster processing
        Dim chars As Char() = word.ToCharArray()
        ' Buffer to build up with character codes
        Dim buffer As StringBuilder = New StringBuilder()
        ' The current and previous character codes
        Dim prevCode As Integer = 0
        Dim currCode As Integer = 0
        ' Append the first character to the buffer
        buffer.Append(chars(0))
        ' Loop through all the characters and convert them to the proper character code
        For i As Integer = 1 To size - 1
            Select Case chars(i)

                Case "A", "E", "I", "O", "U", "H", "W", "Y"
                    currCode = 0

                Case "B", "F", "P", "V"
                    currCode = 1

                Case "C", "G", "J", "K", "Q", "S", "X", "Z"
                    currCode = 2

                Case "D", "T"
                    currCode = 3

                Case "L"
                    currCode = 4

                Case "M", "N"
                    currCode = 5

                Case "R"
                    currCode = 6
            End Select

            ' Check to see if the current code is the same as the last one
            If (currCode <> prevCode) Then

                ' Check to see if the current code is 0 (a vowel); do not process vowels
                If (currCode <> 0) Then
                    buffer.Append(currCode)
                End If
            End If
            ' Set the new previous character code
            prevCode = currCode
            ' If the buffer size meets the length limit, then exit the loop
            If (buffer.Length = length) Then
                Exit For
            End If
        Next
        ' Pad the buffer, if required
        size = buffer.Length
        If (size < length) Then
            buffer.Append("0", (length - size))
        End If
        ' Set the value to return
        value = buffer.ToString()
    End If
    ' Return the value
    Return value
End Function

Using the RowFilter Property

The existing string-based filtering functionality of DataView still works in the LINQ to DataSet context. For more information about string-based RowFilter filtering, see Sorting and Filtering Data.

The following example creates a DataView from the Contact table and then sets the RowFilter property to return rows where the contact's last name is "Zhu":

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

DataView view = contacts.AsDataView();

view.RowFilter = "LastName='Zhu'";

bindingSource1.DataSource = view;
dataGridView1.AutoResizeColumns();
Dim contacts As DataTable = dataSet.Tables("Contact")

Dim view As DataView = contacts.AsDataView()
view.RowFilter = "LastName='Zhu'"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()

After a DataView has been created from a DataTable or LINQ to DataSet query, you can use the RowFilter property to specify subsets of rows based on their column values. The string-based and expression-based filters are mutually exclusive. Setting the RowFilter property will clear the filter expression inferred from the LINQ to DataSet query, and the filter expression cannot be reset.

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

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

DataView view = query.AsDataView();

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

view.RowFilter = "LastName='Zhu'";
Dim contacts As DataTable = dataSet.Tables("Contact")

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

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

dataGridView1.AutoResizeColumns()
view.RowFilter = "LastName='Zhu'"

If you want to return the results of a particular query on the data, as opposed to providing a dynamic view of a subset of the data, you can use the Find or FindRows methods of the DataView, rather than setting the RowFilter property. The RowFilter property is best used in a data-bound application where a bound control displays filtered results. Setting the RowFilter property rebuilds the index for the data, adding overhead to your application and decreasing performance. The Find and FindRows methods use the current index without requiring the index to be rebuilt. If you are going to call Find or FindRows only once, then you should use the existing DataView. If you are going to call Find or FindRows multiple times, you should create a new DataView to rebuild the index on the column you want to search on, and then call the Find or FindRows methods. For more information about the Find and FindRows methods see Finding Rows and DataView Performance.

Clearing the Filter

The filter on a DataView can be cleared after filtering has been set using the RowFilter property. The filter on a DataView can be cleared in two different ways:

Example

The following example creates a DataView from a query and then clears the filter by setting RowFilter property to null:

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

EnumerableRowCollection<DataRow> query = from order in orders.AsEnumerable()
                                         where order.Field<DateTime>("OrderDate") > new DateTime(2002, 11, 20) 
                                            && order.Field<Decimal>("TotalDue") < new Decimal(60.00)
                                         select order;

DataView view = query.AsDataView();

bindingSource1.DataSource = view;

view.RowFilter = null;
Dim orders As DataTable = dataSet.Tables("SalesOrderHeader")

Dim query = _
    From order In orders.AsEnumerable() _
    Where order.Field(Of DateTime)("OrderDate") > New DateTime(2002, 11, 20) _
        And order.Field(Of Decimal)("TotalDue") < New Decimal(60.0) _
    Select order

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

Example

The following example creates a DataView from a table sets the RowFilter property, and then clears the filter by setting the RowFilter property to an empty string:

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

DataView view = contacts.AsDataView();

view.RowFilter = "LastName='Zhu'";


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

// Clear the row filter.
view.RowFilter = "";
Dim contacts As DataTable = dataSet.Tables("Contact")

Dim view As DataView = contacts.AsDataView()
view.RowFilter = "LastName='Zhu'"
bindingSource1.DataSource = view
dataGridView1.AutoResizeColumns()

' Clear the row filter.
view.RowFilter = ""

See also