How to implement Incremental loading in WinUI3 using IncrementalLoadingCollection from CommunityToolkit

Question

I have been working on a project based on C# WinUI 3, I have a ListView in my application which loads a list of data from the database using MySQL. since there is more thank 10k data I have to implement incremented loading in my application. I tried implementing the code from WinUI3 : Add contents during runtime to ListView during scroll, When ever I open the application it initially loads only the give number of data, but when I navigate to another page and come back, the page loads the entire data (10k). Here is the video of the related issue:Demo Video

This is the code that I have used:

Course.cs

using System;

namespace Fees_DBASC.Models.DataModels
{
    public class Course
    {
        public Course() { }

        public Course(int id, string name, int semesters, DateTime timestamp)
        {
            Id = id;
            Name = name;
            Semesters = semesters;
            Timestamp = timestamp;
        }

        public int Id
        {
            get;
            set;
        }

        public string Name
        {
            get;
            set;
        }

        public int Semesters
        {
            get;
            set;
        }

        public DateTime Timestamp
        {
            get;
            set;
        }
    }
}

CourseIncrementalSource.cs

using CommunityToolkit.Common.Collections;
using Fees_DBASC.Core.Database;
using Fees_DBASC.Models.DataModels;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Threading;
using System.Threading.Tasks;

namespace Fees_DBASC.Data.Sources
{
    public class CourseIncrementalSource : IIncrementalSource<Course>
    {
        public CourseIncrementalSource() { }

        public async Task<IEnumerable<Course>> GetPagedItemsAsync(int pageIndex, int pageSize, CancellationToken cancellationToken = default)
        {
            List<Course> items = new List<Course>();

            // Establish a connection to the MySQL database
            string connectionString = GlobalDatabaseConfiguration.Url;
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                await connection.OpenAsync();

                // Create a MySQL command to retrieve the items
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = "SELECT * FROM courses ORDER BY id LIMIT @startIndex, @pageSize";
                command.Parameters.AddWithValue("@startIndex", pageIndex * pageSize);
                command.Parameters.AddWithValue("@pageSize", pageSize);

                // Execute the command and retrieve the data
                using (MySqlDataReader reader = (MySqlDataReader)await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        // Map the data to a MyDataItem object
                        Course item = new Course();
                        item.Id = reader.GetInt32(0);
                        item.Name = reader.GetString("name");

                        items.Add(item);
                    }
                }
            }
            return items;
        }
    }
}

Courses.cs (A WinUi3 page)

using CommunityToolkit.WinUI;
using Fees_DBASC.Data.Sources;
using Fees_DBASC.Models.DataModels;
using Microsoft.UI.Xaml;
using Microsoft.UI.Xaml.Controls;
using Microsoft.UI.Xaml.Input;

namespace Fees_DBASC.Views.Pages
{
    public sealed partial class Courses : Page
    {

        public Courses()
        {
            this.InitializeComponent();
            this.Loaded += Courses_Loaded;
        }

        private void Courses_Loaded(object sender, RoutedEventArgs e)
        {
            var collection = new IncrementalLoadingCollection<CourseIncrementalSource, Course>(25, null, null, null);
            CourseList.ItemsSource = collection;
        }

        private void OpenContextMenu(object sender, RightTappedRoutedEventArgs e)
        {
            ListView listView = (ListView)sender;
            bool singleItemSelected = (listView.SelectedItems.Count == 1);
            Delete.IsEnabled = (listView.SelectedItems.Count > 0);
            Edit.IsEnabled = singleItemSelected;

            ContextMenu.ShowAt(listView, e.GetPosition(listView));
            //var a = ((FrameworkElement)e.OriginalSource).DataContext;

        }
    }
}

Any idea how to fix this issue, I have gone through several websites but no answers were found.

Answer 1

Finally got it fixed by adding await Task.Delay(1, cancellationToken); in the function. I'm not sure what was causing the issue but adding a small delay like 1ms fixed it.

The new code looks like:

public async Task<IEnumerable<Course>> GetPagedItemsAsync(int pageIndex, int pageSize, CancellationToken cancellationToken = default)
{
    using MySqlConnection connection = new(GlobalDatabaseConfiguration.Url);
    await connection.OpenAsync(cancellationToken);
    MySqlCommand command = connection.CreateCommand();
    command.CommandText = "SELECT * FROM courses ORDER BY id LIMIT @startIndex, @pageSize";
    command.Parameters.AddWithValue("@startIndex", pageIndex * pageSize);
    command.Parameters.AddWithValue("@pageSize", pageSize);
    using MySqlDataReader reader = (MySqlDataReader)await command.ExecuteReaderAsync(cancellationToken);
    while (await reader.ReadAsync(cancellationToken))
    {
        Course course = new()
        {
            Id = reader.GetInt32("id"),
            Name = reader.GetString("name"),
            Semesters = reader.GetInt32("semesters"),
            Timestamp = DateTime.Parse(reader.GetString("timestamp"))
        };
        courseList.Add(course);
    }

    //This line fixed it.
    await Task.Delay(1, cancellationToken);
    return (from course in courseList select course).Skip(pageIndex * pageSize).Take(pageSize);
}