pagination & filtering combo in Razor Pages

There is issue that is troubling me for 2 days. Can't figure it out, so maybe another pair or few pair of fresh eyes can point me in right direction.

I have a repository for CRUD operations

    public async Task<int> GetCount()
    {
        var data = await GetData();
        return data.Count;
    }

    public async Task<List<Item>> GetPaginatedResult(int currentPage, int pageSize = 20)
    {
        var data = await GetData();
        return data.OrderBy(d => d.Day).Skip((currentPage - 1) * pageSize).Take(pageSize).ToList();
    }

    public async Task<List<Item>> GetData()
    {
        return await _db.Set<Item>().ToListAsync();
    }

Item is a simple model class, I have no problems here. Pagination works perfectly until I try to combine pagination with filtering.

I have one dropdown that offer year number (2022, 2021) and another with month (int value).

This is part of code that causing problems:

        var tempData = await repository!.GetPaginatedResult(CurrentPage, PageSize);
        Items = tempData;
        //Items = tempData.Where(s => s.Year == SearchYear && s.Month == Month);
        var sumList = await repository!.GetData();
        //Count = sumList.Where(s => s.Year == SearchYear && s.Month == Month).OrderBy(x => x.Day).Count();
        Count = sumList.OrderBy(x => x.Day).Count();
        //SumValues = sumList.Where(s => s.Year == SearchYear && s.Month == Month).OrderBy(x => x.Day).Sum(s => s.Value);
        SumValues = sumList.Sum(s => s.Value);

This way pagination works, before filtering is applied.

When I use this line instead:

Items = tempData.Where(s => s.Year == SearchYear && s.Month == Month);

there is no errors but strange behaviour. When use commented lines instead current ones I get for example on first page only 9 records shown instead 20 what is PageSize, also after first click on Next pagination button, like Items list is empty.

SumValues, what is just a text showing Sum of filtered records is working fine, everything else is mess.

View is classic for populating table:

     <tbody>
        @foreach(var obj in Model.Items!)
        {
            <tr>
                <td>@obj.Day</td>
                <td>@obj.Month</td>
                <td>@obj.Year</td>
                <td>@obj.ItemName</td>
                <td>@obj.Destination</td>
                <td class = "text-end">@string.Format("{0:#,#}", obj.Quantity)</td>
                <td class = "text-end">@string.Format("{0:#,#.00}", obj.Value)</td>
                <td class = "text-center">Edit Delete</td>
            </tr>
        }
    </tbody>

UPDATE: I changed order of operations as suggested to:

var tempData = await repository!.GetData();
var filteredData = tempData.Where(s => s.Year == SearchYear && s.Month == Month);
Items = await repository!.GetPaginatedResult(filteredData, CurrentPage, PageSize);
Count = filteredData.Count();
SumValues = filteredData.Sum(s => s.Value);

Now when I change selected year and month in search combo boxes it is displayed ok, 20 records. But problem arose when I click Next button for second page, table is empty. According to debug info in Visual Studio tempData has number of records it should, but filteredData becomes empty. Not sure why. :/

string on link for Next button on hover says .../Year=2021&Month=12&CurrentPage=2 like it should

I changed method in repository to:

public Task<List<Item>> GetPaginatedResult(IEnumerable<Item> tempList, int currentPage, int pageSize = 20)
    {
        return Task.FromResult(tempList.OrderBy(d => d.Day).Skip((currentPage - 1) * pageSize).Take(pageSize).ToList());
    }

Code in view regarding page links:

            <ul class="pagination">
                @if (Model.ShowPrevious)
                {
                    <li class="page-item text-center">
                        <a asp-page="/Poetracker/Index" 
                            asp-route-SearchTerm = "@Model.SearchYear"
                            asp-route-Month = "@Model.Month"
                            asp-route-CurrentPage="@(Model.CurrentPage - 1)"  
                            class="btn btn-outline-primary btn-sm"  style = "width:100px"><i class="fas fa-backward"></i>&nbsp;Previous</a>
                            &nbsp;
                    </li>
                }
                @if (Model.ShowNext)
                {
                    <li class="page-item text-center">
                        <a asp-page="/Poetracker/Index" 
                            asp-route-SearchTerm = "@Model.SearchYear"
                            asp-route-Month = "@Model.Month"
                            asp-route-CurrentPage="@(Model.CurrentPage + 1)" 
                            class="btn btn-outline-primary btn-sm"  style = "width:100px"><i class="fas fa-forward"></i>&nbsp;Next</a>
                    </li>
                }
            </ul>

FINAL EDIT: When I changed property name from SearchTerm to SearchYear forgot to update asp-route accordingly. Now work as intended.

Solution

When you are dealing with both filtering and pagination, you need to be careful with order of operations. Only after filtering you will know how many records you want to display anyway. It seems like you first do pagination and then filtering, so f.e. on first page 9 out of 20 records meet your criteria and on second page 0 out of N do. I recommend to reverse the order of operations.