How to write code for Server side pagination in ASP.NET Core

Question

I want to implement Server side pagination for loading of some data I want to be loaded into browser. It's working fine Client side with PageList in MVC but I don't know how to do in Asp.net Core Server side.

This is my Class There I want to show all proporties , even photo (image)

public class HouseDTO
    {
        [Key]
        public int HouseId { get; set; }
        public Nullable<decimal> Price { get; set; }
        public string LiveArea { get; set; }
        public string RoomAmount { get; set; }
        public string HouseType { get; set; }
        public string ImageName { get; set; }
    } 

And then my Repisitory

public interface IHouseRepository
{

  public IEnumerable<HouseDTO> GetAllHouses()

}

 public class HouseRepository : IHouseRepository
 {

    private ApplicationDbContext db;

    public HouseRepository(ApplicationDbContext db)
    {
            this.db = db;
    }

    public IEnumerable<HouseDTO> GetAllHouses()
    {
            return db.Houses;
    }
}

And this is my Controller

public class AdvController : Controller
{

   private IHouseRepository db;
   private IHostingEnvironment hostingEnvirnment;

   public AdvController(IHouseRepository db, IHostingEnvironment hostingEnvirnment)
   {
      this.db = db;
      this.hostingEnvirnment = hostingEnvirnment;

   }

   public IActionResult Index()
   {
     var model = db.GetAllHouses();  // How can I do this to Server side pagination?
     return View(model);
   } 
}

So How can create Server side Pagination for this action?

public IActionResult Index()
{
   var model = db.GetAllHouses();   
   return View(model);
}

I would greatly appreciate it if you help me.

Answer 1

Server Side Pagination in .NET Core MVC 8.0

View :

@model IEnumerable<Food>
@*
     
*@
@{
}
<div style="margin-left:20px;margin-right:20px;margin-top:15px;">
       
    <table id="foodTable" class="display">

        <thead class="table-content">
            <tr class="table-row">
                <th scope="col">Food Id</th>
                <th scope="col">Food Name</th>
                <th scope="col">Food Subline</th>
                <th scope="col">Mobile</th>
                <th scope="col">Email</th>
                <th scope="col">First Name</th>
                <th scope="col">Last Name</th>
                <th scope="col">Country</th>
                <th scope="col"> City </th>
                <th scope="col"> Zip Code</th>
                <th scope="col"> Delivary </th>
                <th scope="col"> Address </th>
            </tr>
        </thead>
        <tbody class="table-content">
           
        </tbody>
    </table>

</div>

@section scripts {
    <link rel="stylesheet" href="https://cdn.datatables.net/1.13.7/css/jquery.dataTables.min.css" />
    <script src="https://code.jquery.com/jquery-3.7.0.js"></script>
    <script src="https://cdn.datatables.net/1.13.7/js/jquery.dataTables.min.js"></script>
    <script>
        var $j = jQuery.noConflict();
        $j(document).ready(function () {
            $j('#foodTable').DataTable({
                "processing": true,
                "serverSide": true,
                "ajax": {
                    "url": "/Home/LoadData",
                    "type": "POST",
                    "datatype": "json",
                    "contentType": "application/json",
                    "data": function (d) {
                        d.draw = d.draw || 1;   
                        return JSON.stringify(d);
                    }
                },
                "columns": [
                    { "data": "food_id" },
                    { "data": "food_name" },
                    { "data": "food_subline" },
                    { "data": "mobile" },
                    { "data": "email" },
                    { "data": "first_name" },
                    { "data": "last_name" },
                    { "data": "country" },
                    { "data": "city" },
                    { "data": "zip_code" },
                    { "data": "delivery_charges" },
                    { "data": "address" }
                ]
            });
        });
    </script>
    <script>
        function redirect(event) {
            if (!confirm("Do you really want to delete the food ?")) event.preventDefault();
        }
    </script>
}

Controller Action Method :

[Authorize]
public async Task<IActionResult> JQueryDataTbServerSidePagination() {
    return View();
}

Controller IActionResult Method To Load Data :

 [HttpPost]
  public IActionResult LoadData([FromBody] DataTableRequest request)
  {
      try
      {
          var draw = request.Draw;
          var start = request.Start;
          var length = request.Length;
          var sortColumn = request.Columns[request.Order[0].Column].Data;
          var sortColumnDirection = request.Order[0].Dir;
          var searchValue = request.Search.Value;

          var pageSize = length != 0 ? length : 10;
          var skip = start != 0 ? start : 0;

          var data = context.Foods
              .Where(x => EF.Functions.Like(x.food_name, "%" + searchValue + "%"));

          if (!string.IsNullOrEmpty(sortColumn))
          {
              data = sortColumnDirection == "asc"
                  ? data.OrderBy(x => EF.Property<object>(x, sortColumn))
                  : data.OrderByDescending(x => EF.Property<object>(x, sortColumn));
          }

          var recordsFiltered = data.Count();
          var jsonData = data.Skip(skip).Take(pageSize).ToList();

          return Json(new
          {
              draw = draw,
              recordsTotal = context.Foods.Count(),
              recordsFiltered = recordsFiltered,
              data = jsonData
          });
      }
      catch (Exception ex)
      {
          return Json(new { error = ex.Message });
      }
  }

Model Class :

public class Food
 {
     [Key]
     public int food_id { get; set; }
     [Required]
     public string food_name { get; set; }
     [Required]
     public string food_subline { get; set; }
     [Required]
     public int mobile { get; set; }
     [Required]
     public string email { get; set; }
     [Required]
     public string first_name { get; set; }
     [Required]
     public string last_name { get; set; }
     [Required]
     public string country { get; set; }
     [Required]
     public string city { get; set; }
     [Required]
     public int zip_code { get; set; }
     [Required]
     public int delivery_charges { get; set; }
     [Required]
     public string address { get; set; }
     public string image { get; set; } = "";
     [NotMapped]
     public IFormFile photo { get; set; }
 }

Answer 2

Here's a simple steps to do server side pagination

@model UsersPaginationModel<User>
@*

*@
@{
}
<div class="table-responsive">
    <table class="table">
        <thead class="table-content">
            <tr class="table-row">
                <th class="text-center" scope="col"> <a asp-action="SortByID" asp-route-toggleSort="@(ViewData["ToggleSort"] ?? false)"> User Id </a> </th>
                <th class="text-center" scope="col"> <a asp-action="SortByFName" asp-route-toggleSort="@(ViewData["ToggleSortFname"] ?? false)"> First Name </a> </th>
                <th class="text-center" scope="col"> <a asp-action="SortByLName" asp-route-toggleSort="@(ViewData["ToggleSortLname"] ?? false)"> Last Name </a> </th>
                <th class="text-center" scope="col"> <a asp-action="SortByUName" asp-route-toggleSort="@(ViewData["ToggleSortUname"] ?? false)"> Username </a> </th>
                <th class="text-center" scope="col"> Email </th>
                <th class="text-center" scope="col"> Password </th>
                <th class="text-center" scope="col"> IsActive </th>

            </tr>
        </thead>
        <tbody>
            @for (var i = 0; i < Model.Data.Count; i++)
            {
                <tr>
                    <td scope="col">@Model.Data[i].user_id</td>
                    <td class="text-center">@Model.Data[i].first_name</td>
                    <td class="text-center">@Model.Data[i].last_name</td>
                    <td class="text-center">@Model.Data[i].username</td>
                    <td class="text-center">@Model.Data[i].email</td>
                    <td class="text-center">@Model.Data[i].password</td>

                    <td class="text-center">
                        <form method="post" action="onActiveButtonClick">
                            <a asp-controller="Home" type="submit" asp-route-user_id="@Model.Data[i].user_id" asp-action="onActiveButtonClick" onclick="redirect(event)">
                                @if (Model.Data[i].isActive)
                                {
                                    <i class="bi bi-wifi"></i>
                                }
                                else
                                {
                                    <i class="bi bi-wifi-off"></i>
                                }
                            </a>
                        </form>
                    </td>
                </tr>
            }
        </tbody>
    </table>


    // Buttons to Navigate

    <div class="d-flex justify-content-center" style="margin-top:20px">
        <ul class="pagination">
            @for (int i = 1; i <= Math.Ceiling((double)Model.TotalItems / Model.PageSize); i++)
            {
                <li class="page-item @(i == Model.Page ? "active" : "")">
                    <a class="page-link" href="/Home/HomePage?page=@i&pageSize=@Model.PageSize">@i</a>
                </li>
            }
        </ul>
    </div>

</div>

ViewModel to pass Model/List and other variables :

 public class UsersPaginationModel<T>
 {
    public List<T> Data { get; set; }
    public int Page { get; set; }
    public int PageSize { get; set; }
    public int TotalItems { get; set; }
 }

Controller Action :

    [Authorize]
    public async Task<IActionResult>
    HomePage(int page = 1, int pageSize = 6)
    {
       int skip = (page - 1) * pageSize;

       string q = "SELECT * FROM Users ORDER BY user_id DESC OFFSET @skip ROWS FETCH NEXT @pageSize ROWS ONLY";
       var users = context.Users.FromSqlRaw(q, new SqlParameter("@skip", skip), new SqlParameter("@pageSize", pageSize)).ToList();

       int totalItems = context.Users.Count();
       var userPaginationViewModel = new UsersPaginationModel<UserModelClass>
         {
              Data = users,
              Page = page,
              PageSize = pageSize,
              TotalItems = totalItems
         };
          return View(userPaginationViewModel);
     }

Answer 3

I might be a bit late for the party but I wrote a lightweight package to address this issue by giving you the toolkit to build your DB queries using Skip() and Take() as the other answers suggested.

This might be helpful for someone googling around: https://github.com/YannikG/dotnet-pageable-data

Answer 4

Take() and Skip() over results of db.Houses is the way to go.

Like this:

// Skip (n) pages and take (x) elements from required page.
return db.Houses.Skip(page*countPerPage).Take(countPerPage);
// As suggested in comments for the answer above, specified code should belong to 
// repository method. Initially implemented as a template to be copypasted 
// and reused according to your needs.

make sure that page numbering in query is 0-based: page = 0 if page not specified; page = 0 if you require page #1; page = 1 if you need page #2 etc. And countPerPage meaning is obvious :)

Answer 5

You can use Skip() and Take(). Make a repository method that will take current position (to skip) and give parameter to Take. Something like:

public House GetPaged(currentPosition)
{
  return db.Houses.Skip(currentPosition).Take(20);
}