Using the Windows Community Toolkit DataGrid with WinUI 3 and Entity Framework Core

In this article we demonstrate the Windows Community Toolkit DataGrid in a desktop application powered by the Windows App SDK and a Sqlite relational database. We’ll cover

  • populating the DataGrid,
  • sorting rows via a click on a column header,
  • filtering rows on predefined criteria,
  • grouping rows,
  • searching for rows,
  • instant theme switching,

Here’s how our sample app looks like on Windows 10:

 DatabaseSearch

Recently we started migrating some UWP apps to WinUI 3. Some of these apps have 3rd party DataGrid controls, and we wanted to know whether Community Toolkit DataGrid is a decent candidate to replace these – after all: it’s free. We had no idea how this DataGrid control would behave in a WinUI 3 desktop app, since there are no official samples yet: 

WinUIControlsGallery

On top of that the Community Toolkit DataGrid control is still in its first version which was a port from Silverlight to UWP. When you look at its source you’ll notice that a lot of the code is already 3 years old. Would it run on the brand new Windows App SDK?

For all these reasons we were a bit reluctant to immediately start using this DataGrid in a production WinUI 3 desktop application. So we decided to test drive it first in a more representative setting. This test drive became the sample app that we’re describing in this article.

Migrating the Community Toolkit Sample app

We started our adventure by migrating the UWP DataGrid sample page from the Community Toolkit Sample app to WinUI 3. It has all the functionality we need (filtering, grouping, sorting, theming) and more (editing), and it’s Open Sourced. Here’s how the original looks like:

CommunityToolkitSampleApp

The migration was easier than expected and basically boiled down to updating the “windows.ui.” namespace references to “microsoft.ui.” all over the place. We then applied some cosmetic changes, like adding transparency to the column header background and adding elegance to the CommandBar on top. We also couldn’t resist modernizing the C# syntax here and there.

The Home page of our sample app is the result of the migration from an UWP/WinRT XAML page to WinUI 3/.NET 5:

HomeSort

All features of the original Toolkit demo were successfully ported to our own sample app. You can for example apply a filter to the displayed records:

HomeFilter

You can group records:

HomeGroup

You can sort the records by clicking on a column header:

HomeSearch

The icon in the top right corner op the page allows you to immediately switch between light and dark theme. As long as you stick to ‘lightweight styling’ (i.e. just overriding theme color resources) the following one-liner will do the trick:

Root.RequestedTheme = Root.RequestedTheme == ElementTheme.Light 
	? ElementTheme.Dark 
	: ElementTheme.Light;

From the moment you start to programmatically modify resources, or retemplate (parts of) a control, this instant theme-switching becomes problematic. Not only the control’s XAML but also the defined animations refer to expected colors, opacities, and more – and it’s super hard to override all of these. But let’s focus on DataGrid’s core features.

It takes some development and design effort to implement sorting, filtering, and especially grouping with the Community Toolkit DataGrid. In third party control toolkits such as the ones from DevExpress, Syncfusion, or Telerik [no ranking, just alphabetic order] such features can be configured declaratively and they come with a built-in UI. Nevertheless we were happy with the result of this first page, and decided to build a more modern/representative version of it by bringing some new components to the equation, like

  • Microsoft MVVM Toolkit to replace the custom change propagation code in Models and ViewModels,
  • a Sqlite relational database to replace the CSV file, and
  • Entity Framework Core as an Object-Relational Mapper to run LINQ queries against the data.

Here’s an overview of the NuGet packages – ignore the version numbers, they were all upped multiple times since we made the screenshot:

NuGetPackages

We skipped editing and validation in our sample app, but still all Models and ViewModels were renamed and became children of MVVM Toolkit’s ObservableObject class:

public class Mountain : ObservableObject
{
    private uint _rank;
    private string _name;
    // more fields ...

    // Key

    [Key]
    public int Id { get; set; }

    // Fields

    public uint Rank
    {
        get => _rank;
        set => SetProperty(ref _rank, value);
    }

    // more properties ...

}

For more details on Microsoft MVVM Toolkit, check this introduction. In the rest of this article we’ll focus on Entity Framework (EF) Core, since that is most probably new to UWP developers who are migrating to Windows App SDK.

Configuring Entity Framework Core

For the enterprise sample we replaced the .csv file with a relational Sqlite database. An Entity Framework DbContext was defined to host a table with Mountain entities – a DbSet<Mountain>. Everything is persisted in the app’s local folder:

public class MountainDbContext : DbContext
{
    public DbSet<Mountain> Mountains { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string path = Path.Combine(
	ApplicationData.Current.LocalFolder.Path, 
	"mountains.db");

        string connectionStringBuilder = new
            SqliteConnectionStringBuilder()
        {
            DataSource = path
        }
            .ToString();

        SqliteConnection connection = new SqliteConnection(connectionStringBuilder);
        optionsBuilder.UseSqlite(connection);
    }
}

Before accessing it, the ViewModel ensures that the database is created:

dbContext.Database.EnsureCreated();

This checks whether the database exists -it may of may not have been deployed with your app- and if not it creates an empty one. Our sample app then reads the original .CSV file and populates the Mountains table with it. Now we can write LINQ queries against the data, such as this one to get all of the Mountains:

public async Task<IEnumerable<Mountain>> AllMountainsAsync()
{
    using (MountainDbContext dbContext = new())
    {
        return await dbContext.Mountains
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();
    }
}

Observe that EF Core comes with an asynchronous version of ToList(). When you’re not modifying the content, then it’s a good idea to disable EF change tracking, that’s what the AsNoTracking() does.

Making a read-only DataGrid

The DataGrid definition is similar to the one in the Home page, except that we made the grid read-only since we’re not huge fans of editable DataGrids:

<ctWinUI:DataGrid x:Name="DataGrid"
                    AutoGenerateColumns="False"
                    CanUserSortColumns="True"
                    Sorting="DataGrid_Sorting"
                    LoadingRowGroup="DataGrid_LoadingRowGroup"
                    SelectionMode="Single"
                    IsReadOnly="True">
    <ctWinUI:DataGrid.Columns>
        <ctWinUI:DataGridTextColumn Header="Rank"
                                    Binding="{Binding Rank}"
                                    Tag="Rank" />
        <ctWinUI:DataGridComboBoxColumn Header="Mountain"
                                        Binding="{Binding Name}"
                                        Tag="Name" />
        <!-- More columns ... -->
    </ctWinUI:DataGrid.Columns>
</ctWinUI:DataGrid>

It does not make sense to allow selecting individual cells, so we implemented “row-selection mode” by turning the focus brushes for individually selected cells transparent:

<SolidColorBrush x:Key="DataGridCellFocusVisualPrimaryBrush">Transparent</SolidColorBrush>
<SolidColorBrush x:Key="DataGridCellFocusVisualSecondaryBrush">Transparent</SolidColorBrush>

Be aware that Community Toolkit DataGrid does not support the {x:Bind} syntax for column property bindings, even when you provide bindings for DataContext and ItemsSource:

xBind

Drawing a Template Column

The apps that we’re migrating use templated columns, so we looked for an excuse to define a DataGridTemplateColumn in this sample app. We decided to display the height of the Mountain as a Slider. Here’s the template, observe that the use of {x:Bind} is supported, at least when you declare the DataType :

<ctWinUI:DataGridTemplateColumn Header="Height"
                                Tag="Height">
    <ctWinUI:DataGridTemplateColumn.CellTemplate>
        <DataTemplate x:DataType="models:Mountain">
            <Grid Background="Transparent"
                    ToolTipService.ToolTip="{x:Bind HeightDescription}">
                <Slider Minimum="7200"
                        Maximum="8848"
                        Value="{x:Bind Height}"
                        IsHitTestVisible="False"
                        IsTabStop="False" />
            </Grid>
        </DataTemplate>
    </ctWinUI:DataGridTemplateColumn.CellTemplate>
</ctWinUI:DataGridTemplateColumn>

Here’s how the page now looks like:

DatabaseTemplate

We kept IsEnabled to true to allow the Slider to keep its accent color instead of being grayed out, and set both IsHitTestVisible and IsTabStop to false to prevent user interaction.

Applying a Filter

Here are the LINQ queries that correspond to the filter options:

case FilterOptions.Rank_High:
    return await dbContext.Mountains
	.Where(m => m.Rank > 50)
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();

case FilterOptions.Height_High:
    return await dbContext.Mountains
	.Where(m => m.Height > 8000)
	.OrderBy(m => m.Rank)
	.AsNoTracking()
	.ToListAsync();

As already mentioned, third party libraries have built-in UI to allow your end user to define and apply his own filters. Check this article for an example. Here’s how a filtered dataset looks like in our sample app – with less rows, and an extra indicator on the command button to notify the user that a filter is applied:

DatabaseFilter

Sorting by clicking a column header

For the column sort feature, we wanted to avoid a huge switch statement with a different LINQ expression for each column – as in the original sample. We went for a solution that takes the name of the column and the sort direction as parameter:

public async Task<IEnumerable<Mountain>> SortedMountainsAsync(
	string sortBy, 
	bool ascending)
{
    using (MountainDbContext dbContext = new())
    {
        return await dbContext.Mountains
	.OrderBy(sortBy, !ascending)
	.AsNoTracking()
	.ToListAsync();
    }
}

The OrderBy() in the previous code is an -admittedly cryptic- extension method that builds the appropriate LINQ expression:

public static IOrderedQueryable<TEntity> OrderBy<TEntity>(
    this IQueryable<TEntity> source,
    string orderByProperty,
    bool desc)
{
    string command = desc ? "OrderByDescending" : "OrderBy";
    Type type = typeof(TEntity);
    PropertyInfo property = type.GetProperty(orderByProperty);
    ParameterExpression parameter = Expression.Parameter(
            type,
            "p");
    MemberExpression propertyAccess = Expression.MakeMemberAccess(
            parameter,
            property);
    LambdaExpression orderByExpression = Expression.Lambda(
            propertyAccess,
            parameter);
    MethodCallExpression resultExpression = Expression.Call(
            typeof(Queryable),
            command,
            new Type[] { type, property.PropertyType },
            source.Expression, Expression.Quote(orderByExpression));
    return (IOrderedQueryable<TEntity>)source
            .Provider
            .CreateQuery<TEntity>(resultExpression);
}

When working with EF or EF Core, it’s always good to have IQueryable extensions like this hanging around.

Implementing the Mode

Clicking on a column header (or on one of the command bar buttons) changes the ItemsSource of the DataGrid but also changes its UI (arrow indicators in the column header, and command bar decorators above the grid). You have to make sure that all ‘old’ arrow indicators are removed when you apply a filter -something that was overlooked in the official Toolkit Sample App.

We centralized the UI logic behind a “mode switch” in a PropertyChangedCallback for ItemsSourceProperty:

protected override void OnNavigatedTo(NavigationEventArgs e)
{
    _token = DataGrid.RegisterPropertyChangedCallback(
	ctWinUI.DataGrid.ItemsSourceProperty,
	DataGridItemsSourceChangedCallback);
    base.OnNavigatedTo(e);
}

protected override void OnNavigatedFrom(NavigationEventArgs e)
{
    DataGrid.UnregisterPropertyChangedCallback(
	ctWinUI.DataGrid.ItemsSourceProperty, 
	_token);
    base.OnNavigatedFrom(e);
}

private void DataGridItemsSourceChangedCallback(DependencyObject sender, DependencyProperty dp)
{
    // Remove Sort Indicators.
    if (dp == ctWinUI.DataGrid.ItemsSourceProperty)
    {
        foreach (var column in (sender as ctWinUI.DataGrid).Columns)
        {
            column.SortDirection = null;
        }
    }

    // Other display mode dependent UI logic
    // ...
}

Here’s how we implemented the button decorators in the command bar to indicate the active mode – as an Icon in an AppBarElementContainer that has a negative margin:

<CommandBar DefaultLabelPosition="Right"
            Background="Transparent"
            VerticalAlignment="Center">
    <AppBarButton Icon="Filter"
                    Label="Filter"
                    Width="80">
        <AppBarButton.Flyout>
            <MenuFlyout>
                <!-- Menu items -->
            </MenuFlyout>
        </AppBarButton.Flyout>
    </AppBarButton>
    <AppBarElementContainer x:Name="FilterIndicator"
                            Visibility="Collapsed"
                            Margin="-16 0 0 0">
        <FontIcon Glyph=""
                    FontSize="12"
                    Foreground="Coral"
                    VerticalAlignment="Top" />
    </AppBarElementContainer>

    <!-- More buttons and indicators -->

</CommandBar>

In the future InfoBadge might be a nice alternative but that control is not yet pushed to WinUI 3.

Searching for records

Here’s our initial LINQ query behind the search feature:

return await dbContext.Mountains
            .Where(m => m.Name.Contains(queryText))
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

At first sight no surprises here, except that … it’s case sensitive:

DatabaseSearchWithContains

The LINQ Provider for Sqlite (in contrast to the one for SQL Server) translates Contains() to a case sensitive comparison. The following modification to the query only makes things worse:

return await dbContext.Mountains
            .Where(m => m.Name.Contains(queryText, StringComparison.InvariantCulture)) // Crashes at runtime
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

It will crash at runtime since the LINQ Provider does not know how to translate this C# to its SQL syntax. Again you’ll find enough extension methods to solve this … but in this case EF Core itself has one: EF.Functions.Like(). Here’s how it’s used to generate a SQL LIKE:

return await dbContext.Mountains
            .Where(m => EF.Functions.Like(m.Name, $"%{queryText}%"))
            .OrderBy(m => m.Rank)
            .AsNoTracking()
            .ToListAsync();

Here’s how the result of the non-case-sensitive search looks like in the sample app:

DatabaseSearch

Grouping rows

This is the first version of our LINQ query for the grouping feature:

IEnumerable<GroupInfoCollection<string, Mountain>> query = dbContext.Mountains
                .OrderBy(m => m.Range)
                .ThenBy(m => m.Rank)
                .AsNoTracking()
                .GroupBy(m => m.Range, (key, list) => new GroupInfoCollection<string, Mountain>(key, list));

Bad luck: the LINQ Provider for Sqlite does not know how to translate this C# into SQL syntax, and crashes at runtime:

MissingGroupByLinq

We opted to bail out of the Sqlite Provider with a ToList() in the middle of the expression. The GroupBy() is then not applied to the query (an IQueryable) but to the result of the query (an IEnumerable). The responsibility was passed to the native .NET 5 LINQ provider, who knows how to handle this:

IEnumerable<GroupInfoCollection<string, Mountain>> query = dbContext.Mountains
                .OrderBy(m => m.Range)
                .ThenBy(m => m.Rank)
                .AsNoTracking()
                .ToList()
                .GroupBy(m => m.Range, (key, list) => new GroupInfoCollection<string, Mountain>(key, list));

Here’s how grouping looks like in our sample app:

DatabaseGroup

Showing Row Details

The command bar has a button to reveal (or hide) the details of the selected row:

DatabaseDetails

We prefer this pattern over the ‘every-click-opens-details-row’ that’s built into the DataGrid. Firstly because you cannot unselect a row – clicking on the selected row does not unselect it. The only way to close the details row is selecting another row – which then opens a new details row. Secondly we believe that in most cases it’s better to display the details not inside the DataGrid but in a separate place (navigate to another page, open a dialog, …).

Stability

Running V1 of a ported Silverlight control in an application ecosystem that’s only in v0.8: what could possibly go wrong? Smile 

When browsing the open DataGrid related issues on GitHub, you encounter some stability issues. When we started building the sample app, we ran into some of these. When rapidly scrolling, switching mode, and clicking column headers, the DataGrid control gave up rather quickly. For the sake of completeness: with the same behavior we also managed to crash all of the 3rd party sample apps.

We were relieved to observe that most if not all of the performance and stability issues were solved with the release of Windows App SDK 0.8.2! From that release on the DataGrid is behaving as expected.

Conclusion

As far as we are concerned, Community Toolkit DataGrid on WinUI 3 has passed the tests, and is ready for prime time. Some of the features -like filtering and grouping- require more design and development effort than you would have with third party components. This is a challenge, but it’s also an opportunity for you to come up with a friendly non-technical user experience for these scenarios. Here are a couple of examples (NOT included in the sample app) of custom filtering and grouping that would even be hard with third party grids.

  • Removing the Lagers from a list of beer styles. This is a filter on a substring in a hidden column:

Sample

  • Grouping a list of hops by origin. This is a grouping on an n-to-n relation:

Sample2

It’s great for the end user to see the most relevant filtering and groupings of the data and just select one from a menu.

Our sample app lives here on GitHub.

Enjoy!

1 thought on “Using the Windows Community Toolkit DataGrid with WinUI 3 and Entity Framework Core

  1. Martin

    Thank you for the greate article! I am experimenting with the DataGrid too, and found this post very helpful.

    I was wondering what the simplest way would be to display a Tooltip for the column header.

    Another topic I was not able to solve yet is how to display a “MenuFlyout” if the user clicks on a certain cells. The “MenuFlyout” should be displayed next to the clicked cell, but I was not able to get the instance of the clicked cell yet.
    I also made a post about this in stackoverflow: https://stackoverflow.com/questions/69852715/winui-datagrid-from-communitytoolkit-how-to-get-the-currently-selected-cell

    Like

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s