Export (0) Print
Expand All

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.

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

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;

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;

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();

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();

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

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();

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'";

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.

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:

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;

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 = "";
Show:
© 2014 Microsoft