Working Filtering on SPGridView and Child-properties

Finally, I have my filtering working!! :D

Yesterday while I drove home (about an hours drive), I thought of a different approach to my problem; why not try to handle the stuff while applying the filter. Trick, at least for me, was to figure out how to do this.

Basically, it comes down to NOT supplying the FilteredDataSourcePropertyName and FilteredDataSourcePropertyFormat for the SPGridView and setting the AutoGenerateWhereClause to true.

Then, at the same spot where the filter is re-applied when not wanting to lose the filter when sorting, handle the filter!

For now, still only one filter can be active at a time, opposed to SharePoint-lists, which can have multiple filters active at the same time.

My DataBound-event of my SPGridView now looks as follows:

            //First check if there is a filter applied
            if (string.IsNullOrEmpty(gridview.FilterFieldName) == false)
            {
                //Then re-apply the filter with exceptions for specified columns
                if (gridview.FilterFieldName == "Country")
                {
                    if (linqDataSource.WhereParameters["ChildID"] == null)
                    { linqDataSource.WhereParameters.Add("ChildID", System.Data.DbType.Int32, this.dataContext.Childs.Where(c => c.Country == gridview.FilterFieldValue).Select(c => c.ChildID).First().ToString()); }
                }
                else if (gridview.FilterFieldName.EndsWith("ID"))
                {
                    if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
                    { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, System.Data.DbType.Int32, gridview.FilterFieldValue); }
                }
                else
                {
                    if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
                    { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, gridview.FilterFieldValue); }
                }
            }
            else if (linqDataSource.WhereParameters.Count > 0)
            { linqDataSource.WhereParameters.Clear(); }

So here’s what I’m doing. First I check to see if a filter should be applied:

            //First check if there is a filter applied
            if (string.IsNullOrEmpty(gridview.FilterFieldName) == false)
            { ...

Next, I reapply the filter, but here I also handle my exceptions (for example filtering on integer-values) and my Child-properties. For filtering on the Child-properties, I catch the requested FilterFieldName and add a custom WhereParameter to the LinqDataSource:

                //Then re-apply the filter with exceptions for specified columns
                if (gridview.FilterFieldName == "Country")
                {
                    if (linqDataSource.WhereParameters["ChildID"] == null)
                    { linqDataSource.WhereParameters.Add("ChildID", System.Data.DbType.Int32, this.dataContext.Childs.Where(c => c.Country == gridview.FilterFieldValue).Select(c => c.ChildID).First().ToString()); }
                }

Notice that I enter a DbType in the WhereParameter. Because I filter on an integer, I get the error that Operator ‘==’ incompatible with operand types ‘Int32’ and ‘String’ when I ommit this step.

I also handle the ID-fields to include the DbType in the WhereParameter.

                else if (gridview.FilterFieldName.EndsWith("ID"))
                {
                    if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
                    { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, System.Data.DbType.Int32, gridview.FilterFieldValue); }
                }

For the other columns, I just add a standard WhereParameter where I use the gridview.FilterFieldName als Parameter-name and gridview.FilterFieldValue as it’s value.

                else
                {
                    if (linqDataSource.WhereParameters[gridview.FilterFieldName] == null)
                    { linqDataSource.WhereParameters.Add(gridview.FilterFieldName, gridview.FilterFieldValue); }
                }
            }

And if there should be no filtering done, I remove all WhereParameters. Otherwise, clearing a filter will still give filtered results.

            else if (linqDataSource.WhereParameters.Count > 0)
            { linqDataSource.WhereParameters.Clear(); }