ASP.net Core 3.1 Razor page Paging control not working with dapper dataset

Question

I am new to asp.net core and i am trying to use pager control LazZiya.TagHelpers in ASP.net core 3.1 Razor Page project for paging to display new items, for some reason it shows all the news items (total 20) on the news page but pager control seems to show correct numbers

Its seems this paging control only works with EF and not Dapper, i tried to make it work with dapper which didnt work?. Below is code related to Dapper as well as working code using EF. I would appreciate if i can make it work with Dapper also (which seems not to be possible with my limited knowledge)

For testing purpose i set page size to 3 so it should show 3 items on page rather it shows all 20 items on page,

It seems pager control is not binding to List eNewsList= await NewsService.GetNews(1);

     public class NewsModel : PageModel
{
    public IEnumerable<News> NewsList { get; set; }

    public INewsService NewsService { get; }

    public NewsModel(INewsService newsService)
    {
        NewsService = newsService ?? throw new ArgumentNullException(nameof(newsService));
    }

    [BindProperty]
    public IEnumerable<News> eNewsList { get; set; }

    [BindProperty]
    public int TotalRecords { get; set; }

    [BindProperty]
    public int PageNo { get; set; }

    [BindProperty]
    public int PageSize { get; set; }
    // public 

    public async Task OnGet( int p=1, int s=3)
    {

        eNewsList = await NewsService.GetNews(1);     
        TotalRecords = eNewsList.Count();
        eNewsList.Skip((p - 1) * s).Take(s);
        PageNo = p;
        PageSize = s;

        //NewsList.ToList();
    }


}

Code

<div class="container">
    <div class="row">
        @foreach (var item in Model.eNewsList)
        {
            <div class="col-xl-4 col-lg-4 col-md-6">
                <div class="card">
                    <img src="images/NewsImages/@item.NewsImage" class="card-img-top" alt="...">
                    <div class="card-body">
                        <h5 class="card-title">@item.NewsHeading</h5>
                        @if (item.NewsBrief.Length > 50)
                        {
                            <p class="card-text">@item.NewsBrief.Substring(0, 50)...</p>}
                        else
                        {
                            <p class="card-text">@item.NewsBrief</p>
                        }
                        <a href="/news/@item.NewsID/@item.NewsHeading.Replace(" ", "-").ToLower()" class="btn btn-primary">Read More</a>
                    </div>
                </div>
            </div>
        }

        <div class="row">
            <div class="col-12">
                <paging  total-records="Model.TotalRecords"
                        page-no="Model.PageNo"
                        page-size="Model.PageSize">
                </paging>
            </div>
        </div>

    </div>
</div>

Dapper code

 // Get all News
        public async Task<List<News>> GetNews(int langID)
        {
            IEnumerable<News> newslist;
            using (var conn = new SqlConnection(_configuration.Value))
            {
                string query = "select * from dbo.News Where LanguageID ="+ langID;
                        query = query +" AND NewsActive =1 AND NewsVisible=1 order by NewsDate Desc";

                conn.Open();
                try
                {
                    newslist = await conn.QueryAsync<News>(query, commandType: CommandType.Text);

                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }

            }
            return newslist.ToList();
        }

I am not sure how to bind data eNewslist to pager control

I am using Pager control from http://www.ziyad.info/en/articles/21-Paging_TagHelper_ASP_NET_Core

Below code works when i use it with EF

Working Code using EF

<div class="container">
    <div class="row">
        @foreach (var item in Model.eNewsList)
        {
            <div class="col-xl-4 col-lg-4 col-md-6">
                <div class="card">
                    <img src="https://www.habtoor.com/images/NewsImages/@item.NewsImage" class="card-img-top" alt="...">
                    <div class="card-body">
                        <h5 class="card-title">@item.NewsHeading</h5>
                        @if (item.NewsBrief.Length > 50)
                        {
                            <p class="card-text">@item.NewsBrief.Substring(0, 50)...</p>}
                        else
                        {
                            <p class="card-text">@item.NewsBrief</p>
                        }
                        <a href="/news/@item.NewsID/@item.NewsHeading.Replace(" ", "-").ToLower()" class="btn btn-primary">Read More</a>
                    </div>
                </div>
            </div>
        }

        <div class="row">
            <div class="col-12">
                <paging total-records="Model.TotalRecords"
                        page-no="Model.P"
                        page-size="Model.S"
                        show-prev-next="true"
                        show-total-pages="false"
                        show-total-records="false"
                        show-page-size-nav="false"
                        show-gap="true"
                        show-first-numbered-page="true"
                        show-last-numbered-page="true"
                        gap-size="2"
                        max-displayed-pages="8"
                        query-string-key-page-no="p"
                        query-string-key-page-size="s"
                        query-string-value="@@(Request.QueryString.Value)"
                        page-size-nav-block-size="10"
                        page-size-nav-max-items="5"
                        page-size-nav-on-change="get"
                        page-size-nav-form-method="this.form.submit();"
                        class="row"
                        class-paging-control-div="col"
                        class-info-div="col"
                        class-page-size-div="col"
                        class-paging-control="pagination"
                        class-active-page="disabled"
                        class-disabled-jumping-button="disabled"
                        class-total-pages="badge badge-secondary"
                        class-total-records="badge badge-info"
                        text-page-size="Items per page:"
                        text-total-pages="pages"
                        text-total-records="records"
                        text-first="&laquo;"
                        text-last="&raquo;"
                        text-previous="&lsaquo;"
                        text-next="&rsaquo;"
                        sr-text-first="First"
                        sr-text-last="Last"
                        sr-text-previous="Previous"
                        sr-text-next="Next">
                </paging>
            </div>
        </div>

    </div>
</div>

Code behind

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using BookListRazor.Data;
using BookListRazor.Model;
using Microsoft.EntityFrameworkCore;

namespace BookListRazor.Pages
{
    public class NewsModel : PageModel
    {
        // for EF
        private readonly ApplicationDbContext _db;

        public IEnumerable<News> NewsList { get; set; }

        public INewsService NewsService { get; }

        //constructor
        public NewsModel(INewsService newsService, ApplicationDbContext db)
        {
            //for EF
            _db = db;

            //For Dapper
            NewsService = newsService ?? throw new ArgumentNullException(nameof(newsService));
        }


        [BindProperty]
        public IEnumerable<News> eNewsList { get; set; }

        [BindProperty]
        public int TotalRecords { get; set; }

        [BindProperty(SupportsGet = true)]
        public int P { get; set; }

        [BindProperty(SupportsGet = true)]
        public int S { get; set; }

        // public 

        public async Task OnGet()
        {

            //code block for pager to work with EF only
            //pager doesnt work with dapper
            P = 1;
            S = 3;

            //var pageQS=1;
            if (!String.IsNullOrEmpty(HttpContext.Request.Query["p"]))
            {
                P = int.Parse(HttpContext.Request.Query["p"]);
            }

            var query = await _db.News.OrderByDescending(x => x.NewsDate).Where(x => x.LanguageID == 1 && x.NewsActive==true && x.NewsVisible==true).ToListAsync();
            TotalRecords = query.Count();

            //steps for paging
            eNewsList = await _db.News.OrderByDescending(x=>x.NewsDate).Where(x =>x.LanguageID==1).Skip((P - 1) * S).Take(S).ToListAsync();


            // Dapper without pagging
           // NewsList = await NewsService.GetNews(1);
        }


    }
}

Answer 1

Below is a simplified implementation for the backend paging.

public IEnumerable<News> eNewsList { get; set; }

public int TotalRecords { get; set; }

[BindProperty(SupportsGet = true)]
public int P { get; set; }

[BindProperty(SupportsGet = true)]
public int S { get; set; }

public async Task OnGetAsync()
{
    var query = await NewsService.GetNews(1);

    TotalRecords = query.Count();

    eNewsList = await NewsService.GetNews(1).Skip((P - 1) * S).Take(S).ToListAsync();
}

Please consider below notices as well:

1 - Don't fetch the whole dataset, get only the amount to be displayed at once

Since I don't know what is NewsService.GetNews(1) I suppose it is returning a full set of records.

Returning a full set of records is not recommended because it will consume a lot of memory, bandwidth and processing time. I recommend using kind of EF linq implementations to avoid over processing issues. e.g.:

var records = dbContext.Set<News>()
                       .AsNoTracking()
                       .Where(**some search filter**);

Such implementation will be converted to a query that will not be executed till you call .ToList() or .ToListAsync(). This way you make sure you are rturning only requested amount of records.

And when you call .Count() it will return only the records count without ferching the whole dataset.

2 - Do records ordering before paging

If you are using any kind of paging it is highly important that you order the records before paging the items, so you get the next page of records with the same order e.g.

var records = await NewsSerive.GetNews(1)
                    .OrderBy(x => x.Name)
                    .Skip((P - 1)*S).Take(S)
                    .ToListAsync();

3 - Page size settings

Another note regarding PagingTagHelper, if you want to change the page values to something other than the default values use [page-size-dropdown-items] attribute as described in the demo page