Grouping and Filtering DataGrids

WPF Version 4.0

DataGrids (and other item collection controls) have considerable support for Grouping, Filtering, and Sorting, and Navigating. The default Sort behavior is exactly what my users want (hallelujah) so I don't need to do anything special for that. I don't have any requirements for programmatic Navigation at this time so that just leaves Grouping and Filtering.

Let's create a DataGrid and populate it with Products from the AdventureWorks2012 sample database. We will add checkboxes to allow us to optionally group by ProductLine and optionally filter out all products that are not Black.

Here is the XAML and the code-behind. I add/remove the group and filter in code but it can be done in XAML with the judicious use of converters. Code behind is easier.

<Window x:Class="GroupingAndFiltering.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="Grouping and Filtering" Height="350" Width="1000">
    <Grid Name="LayoutGrid">
        <Grid.RowDefinitions>
            <RowDefinition Height="Auto"></RowDefinition>
            <RowDefinition></RowDefinition>
        </Grid.RowDefinitions>
        <StackPanel Grid.Row="0" Orientation="Horizontal">
            <TextBlock Text="Group By Product Line?" Margin="0,0,10,0"/>
            <CheckBox Name="GroupCheckBox" Checked="GroupCheckBox_Checked" Unchecked="GroupCheckBox_Unchecked"/>
            <TextBlock Text="Show Black Products Only?" Margin="10,0,10,0"/>
            <CheckBox Name="FilterCheckBox" Checked="FilterCheckBox_Checked" Unchecked="FilterCheckBox_Unchecked"/>
        </StackPanel>
        <DataGrid Name="dg" Grid.Row="1" ItemsSource="{Binding}" AutoGenerateColumns="False" IsReadOnly="true">
            <DataGrid.GroupStyle>
                <GroupStyle>
                    <GroupStyle.HeaderTemplate>
                        <DataTemplate>
                            <Border BorderBrush="Black" BorderThickness="1" CornerRadius="5" Padding="5,5,5,5">
                                <StackPanel Orientation="Horizontal">
                                <TextBlock Text="Product Line Group '" FontWeight="Bold"/>
                                <TextBlock Text="{Binding Path=Name}" FontWeight="Bold"/>
                                <TextBlock Text="', Number of Products = " FontWeight="Bold"/>
                                    <TextBlock Text="{Binding Path=ItemCount}" FontWeight="Bold"/>
                                </StackPanel>
                            </Border>
                        </DataTemplate>
                    </GroupStyle.HeaderTemplate>
                </GroupStyle>
            </DataGrid.GroupStyle>
            <DataGrid.Columns>
                <DataGridTextColumn Header="Name" Binding="{Binding Name}"/>
                <DataGridTextColumn Header="Product Number" Binding="{Binding ProductNumber}"/>
                <DataGridTextColumn Header="Product Line" Binding="{Binding ProductLine}"/>
                <DataGridTextColumn Header="Color" Binding="{Binding Color}"/>
                <DataGridTextColumn Header="Class" Binding="{Binding Class}"/>
                <DataGridTextColumn Header="Style" Binding="{Binding Style}"/>
                <DataGridTextColumn Header="Size" Binding="{Binding Size}"/>
                <DataGridTextColumn Header="Size Unit" Binding="{Binding SizeUnitMeasureCode}"/>
            </DataGrid.Columns>
        </DataGrid>
    </Grid>
</Window>


using System;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Data;
using System.Data.SqlClient;
using System.ComponentModel;

namespace GroupingAndFiltering
{
    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();

            string sConn = "Server=(local); Database=AdventureWorks2012;Trusted_Connection=true";
            SqlConnection conn = new SqlConnection(sConn);
            string sSQL = "SELECT * FROM [Production].[Product] ORDER BY ProductLine, Name";
            SqlCommand comm;
            SqlDataReader DR;
            DataTable DT = new DataTable();
            DataTable UoM = new DataTable();

            conn.Open();
            comm = new SqlCommand(sSQL, conn);
            DR = comm.ExecuteReader();
            DT.Load(DR);
            LayoutGrid.DataContext = DT;
            DR.Close();
            conn.Close();
        }

        private void GroupCheckBox_Checked(object sender, RoutedEventArgs e)
        {
            ICollectionView view = CollectionViewSource.GetDefaultView(LayoutGrid.DataContext);
            view.GroupDescriptions.Clear();
            view.GroupDescriptions.Add(new PropertyGroupDescription("ProductLine"));
        }

        private void GroupCheckBox_Unchecked(object sender, RoutedEventArgs e)
        {
            ICollectionView view = CollectionViewSource.GetDefaultView(LayoutGrid.DataContext);
            view.GroupDescriptions.Clear();
        }

        private void FilterCheckBox_Checked(object sender, RoutedEventArgs e)
        {
            ((DataTable)LayoutGrid.DataContext).DefaultView.RowFilter = "Color='Black'";
        }

        private void FilterCheckBox_Unchecked(object sender, RoutedEventArgs e)
        {
            ((DataTable)LayoutGrid.DataContext).DefaultView.RowFilter = "";
        }
    }
}

Try Sorting and Filtering with no Groups. Now turn Grouping on and try Sorting and Filtering again. See how slow Grouping makes everything? I think it's because you can't virtualize a control that has grouping. This example only has about 500 records - not a lot. In my opinion Grouping makes everything too slow. I am not going to offer it to my users. If you don't have many records there's no point in grouping, but if you have a lot of records it's too slow.

On the other hand, Filtering is very fast (when Grouping is off) and I can see a use for it. For example, I have some screens that have a document type combobox and a location combobox. The list of valid locations depends on the selected documents type. This requirement will be easy to satisfy with a filter on the location dataview.

A word about the filter. You'll notice I implement the group by adding a PropertyGroupDescription object to the View's GroupDescriptions collection. The View also has a FilterDescriptions collection but a closer look at the View shows that the CanFilter property is false. Trying to add a filter to the FilterDescriptions throws an exception. This approach only works if the view's class implements ICollectionView, which DataView does not.

When the View has a DataTable as it's backing store you have to implement filters through the DataView's RowFilter property which is a simple SQL clause such as "Color='Black'". It's actually easier.