Pagination with Entity Framework Core and Microsoft MVVM in WinUI 3

In this article we present a LINQ-based pagination solution for list controls as well as a pager user interface, all in a WinUI 3 application. We use SQLite as data provider and a Windows Community Toolkit DataGrid as host control, but the solution applies to any Entity Framework Core (EF Core) store and any WinUI 3 ItemsControl. For the implementation of the MVVM pattern, we chose the usual suspect: Microsoft MVVM Toolkit.

Here’s how the sample page looks like:

This is a new XAML page that we added to the sample app for our previous article on using the DataGrid in WinUI 3.

The heart of our pagination infrastructure is PaginatedList<T> – a subclass of List<T>.

It holds a specific page from a query result as a list of items can be bound to any ItemsControl as ItemsSource. A PaginatedList<T> instance not only embeds the (partial) result of the query but it also exposes the current page number, and the total number of pages for the whole query (quite convenient for a pager UI). The page size (number of records wanted) is not a property but is provided to the constructor.

Here’s how the class definition looks like:

public class PaginatedList<T> : List<T>
{
    public int PageIndex { get; private set; }

    public int PageCount { get; private set; }

    private PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
    {
        PageIndex = pageIndex;
        PageCount = (int)Math.Ceiling(count / (double)pageSize);
        AddRange(items);
    }

    public static async Task<PaginatedList<T>> CreateAsync(
	IQueryable<T> source, 
	int pageIndex, 
	int pageSize)
    {
        int count = await source.CountAsync();
        List<T> items = await source
	.Skip((pageIndex - 1) * pageSize)
	.Take(pageSize)
	.ToListAsync();

        return new PaginatedList<T>(items, count, pageIndex, pageSize);
    }
}

We’re using some of the asynchronous EF Core IQueryable Extensions here, but feel free to add a synchronous version if that better fits your use case.

The PaginatedList is used inside the XAML page’s ViewModel (a Microsoft MVVM Toolkit’s ObservableObject) to produce a List<Mountain> to which the DataGrid is bound. The ViewModel also exposes the current page number and the total number of pages. These are used by the pager on top. Here’s the ViewModel’s code:

public class PaginationPageViewModel : ObservableObject
{
    private int _pageSize = 10;
    private int _pageNumber;
    private int _pageCount;
    private List<Mountain> _mountains;

    public int PageNumber
    {
        get => _pageNumber;
        private set => SetProperty(ref _pageNumber, value);
    }

    public int PageCount
    {
        get => _pageCount;
        private set => SetProperty(ref _pageCount, value);
    }

    public List<Mountain> Mountains
    {
        get => _mountains;
        private set => SetProperty(ref _mountains, value);
    }

    private async Task GetMountains(int pageIndex, int pageSize)
    {
        using MountainDbContext dbContext = new();
        PaginatedList<Mountain> pagedMountains = await PaginatedList<Mountain>.CreateAsync(
            dbContext.Mountains
                .OrderBy(m => m.Rank),
            pageIndex,
            pageSize);
        PageNumber = pagedMountains.PageIndex;
        PageCount = pagedMountains.PageCount;
        Mountains = pagedMountains;
    }
}

Here’s how the ‘current page of Mountains’ is bound to the DataGrid:

<ctWinUI:DataGrid x:Name="DataGrid"
                    ItemsSource="{x:Bind ViewModel.Mountains, Mode=OneWay}"
                    AutoGenerateColumns="False"
                    CanUserSortColumns="False"
                    SelectionMode="Single"
                    IsReadOnly="True"
                    RowDetailsVisibilityMode="Collapsed">
    <ctWinUI:DataGrid.Columns>
        <ctWinUI:DataGridTextColumn Header="Rank"
                                    Binding="{Binding Rank}" />
        <ctWinUI:DataGridComboBoxColumn Header="Mountain"
                                        Binding="{Binding Name}" />
        <!-- More columns -->
    </ctWinUI:DataGrid.Columns>
</ctWinUI:DataGrid>

The ViewModel also exposes 4 commands -implementors of IAsyncRelayCommand– to allow refreshing the DataGrid with a new logical page:

public IAsyncRelayCommand FirstAsyncCommand { get; }

public IAsyncRelayCommand PreviousAsyncCommand { get; }

public IAsyncRelayCommand NextAsyncCommand { get; }

public IAsyncRelayCommand LastAsyncCommand { get; }

We would have loved to use one of the new WinUI Pager controls for user interface, but tody these are available in WinUI 2 only. Therefor we brewed our own CommandBar-based pagination control with the canonical navigation buttons to the first, previous, next, and last pages:

<CommandBar DefaultLabelPosition="Right">
    <AppBarButton ToolTipService.ToolTip="First"
                    Icon="Previous"
                    Command="{x:Bind ViewModel.FirstAsyncCommand, Mode=OneWay}" />
    <AppBarButton ToolTipService.ToolTip="Previous"
                    Icon="Back"
                    Command="{x:Bind ViewModel.PreviousAsyncCommand, Mode=OneWay}" />
    <AppBarElementContainer>
        <TextBlock Text="Page" />
    </AppBarElementContainer>
    <AppBarElementContainer>
        <TextBlock Text="{x:Bind ViewModel.PageNumber, Mode=OneWay}" />
    </AppBarElementContainer>
    <! -- And so on ... -->

Here’s how the AsyncRelayCommands are initialized in the ViewModel with their respective Execute and CanExecute logic:

FirstAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(1, _pageSize),
    () => _pageNumber != 1
);
PreviousAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageNumber - 1, _pageSize),
    () => _pageNumber > 1
);
NextAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageNumber + 1, _pageSize),
    () => _pageNumber < _pageCount
);
LastAsyncCommand = new AsyncRelayCommand(
    async () => await GetMountains(_pageCount, _pageSize),
    () => _pageNumber != _pageCount
);

Each time a new page is fetched and displayed, we need to tell the commands that their CanExecute property was updated so that the corresponding buttons in the pager will enable or disable themselves:

FirstAsyncCommand.NotifyCanExecuteChanged();
PreviousAsyncCommand.NotifyCanExecuteChanged();
// And so on ...

To improve the user experience, we enhanced PaginatedList<T> to implement some edge cases. When the result of the full query is empty (page count is zero), we set the current page number to zero:

int count = await source.CountAsync();
if (count == 0)
{
   // No results -> return page 0.
   return new PaginatedList<T>(new List<T>(), 0, 0, pageSize);
}

This is how the pager then looks like:

When the requested page is out of range (has no records) then PaginatedList<T> returns the last page:

List<T> items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
if (items.Count == 0)
{
    // Requested page is out of range -> return last page.
    pageIndex = (int)Math.Ceiling(count / (double)pageSize);
    items = await source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToListAsync();
}

return new PaginatedList<T>(items, count, pageIndex, pageSize);

Here’s how the pager looks like when we try to navigate to page 1000:

Last but not least, we allow the user to change the page size. We first added the property and a list of possible values to the ViewModel:

public List<int> PageSizes => new() { 5, 10, 20, 50, 100 };

public int PageSize
{
    get => _pageSize;
    set
    {
        SetProperty(ref _pageSize, value);
        Refresh();
    }
}

Then we added a ComboBox and a TextBlock to the AppBar, via AppBarElementContainer instances:

<AppBarSeparator />
<AppBarElementContainer VerticalContentAlignment="Center">
    <ComboBox ItemsSource="{x:Bind ViewModel.PageSizes}"
                SelectedItem="{x:Bind ViewModel.PageSize, Mode=TwoWay}" />
</AppBarElementContainer>
<AppBarElementContainer VerticalContentAlignment="Center">
    <TextBlock Text="rows per page"
                Margin="8 0" />
</AppBarElementContainer>

Here’s how that part of the UI looks like:

Finally we implemented the Refresh() method – triggered by a change of the PageSize property. We simply navigate to the first page. Before that we make sure that the command can execute by setting the page number to zero:

private void Refresh()
{
    _pageNumber = 0;
    FirstAsyncCommand.Execute(null);
}

With a handful lines of code and a little help from our friends Microsoft MVVM Toolkit and EF Core we just implemented a WinUI 3 pagination use case. Our sample app lives here on GitHub.

Enjoy!