Pagination

Part 08 - Pagination

Pagination

Pagination is one of the most important concepts while building RESTful APIs. An ideal API endpoint would allow it’s consumers to get only a specific number of records in one go. In this way, we are not giving load to our Database Server, the CPU on which the API is hosted, or the network bandwidth. This is a highly crucial feature for any API. especially the public APIs.

Newtonsoft Package
install newtonsoft.json package

Notes:
notes.

XPagedListMvcCore
install package XPagedListMvcCore package

Notes:
none.

BPX.Utils project reference
add project reference to BPX.Utils

Notes:
none.

Base Repository
using BPX.DAL.Context;

namespace BPX.DAL.Repositories
{
    public abstract class BaseRepository
    {
        protected readonly EFContext efContext;

        public BaseRepository(EFContext efContext)
        {
            this.efContext = efContext;
        }

        public void SaveDBChanges()
        {
            efContext.SaveChanges();
        }
    }
}

Notes:
none.

Eatery Repository
using BPX.DAL.Context;
using BPX.Domain.DbModels;
using BPX.Domain.FilterModels;
using BPX.Utils;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System.Linq.Expressions;
using X.PagedList;

namespace BPX.DAL.Repositories
{
    public class EateryRepository : BaseRepository, IEateryRepository
    {
        public EateryRepository(EFContext efContext) : base(efContext)
        {
        }

        public Eatery? GetRecordById(int id)
        {
            return efContext.Eaterys.Where(c => c.EateryId.Equals(id)).SingleOrDefault();
        }

        public IPagedList<Eatery> GetPaginatedRecords(PagingParams pagingParams)
        {
            // sanitize the pagingParams
            PagingParams ppObj = new PagingParams().SanitizePagingParams(pagingParams);

            // get model : IQueryable
            IQueryable<Eatery> model = efContext.Eaterys;

            // apply statusFlag
            model = efContext.Eaterys.Where(c => c.StatusFlag.ToUpper().Equals(ppObj.StatusFlag.ToUpper()));

            // apply generic search
            if (ppObj.SearchForString.Length > 0)
            {
                model = model.Where(c => c.Name.ToUpper().StartsWith(ppObj.SearchForString.ToUpper()));
            }

            // apply advanced search using filters
            if (ppObj.FilterJson.Length > 0)
            {
                EateryFM? eateryFM = JsonConvert.DeserializeObject<EateryFM>(ppObj.FilterJson);

                if (eateryFM != null)
                {
                    if (eateryFM.Name != null)
                        model = model.Where(c => c.Name.ToUpper().StartsWith(eateryFM.Name.Trim().ToUpper()));
                    else if (eateryFM.Address != null)
                        model = model.Where(c => c.Address.ToUpper().StartsWith(eateryFM.Address.Trim().ToUpper()));
                    else if (eateryFM.Phone != null)
                        model = model.Where(c => c.Phone.ToUpper().StartsWith(eateryFM.Phone.Trim().ToUpper()));
                }
            }

            // apply sort by column, sort order
            model = ppObj.SortByColumn.ToUpper() switch
            {
                "NAME" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.Name) : model.OrderBy(c => c.Name),
                "ADDRESS" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.Address) : model.OrderBy(c => c.Address),
                "PHONE" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.Phone) : model.OrderBy(c => c.Phone),
                _ => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.EateryId) : model.OrderBy(c => c.EateryId),
            };

            // return ToPagedList()
            return model.ToPagedList(ppObj.PageNumber, ppObj.PageSize);
        }

        public IQueryable<Eatery> GetRecordsByFilter(Expression<Func<Eatery, bool>> filter)
        {
            return efContext.Eaterys.Where(filter);
        }

        public void InsertRecord(Eatery entity)
        {
            efContext.Eaterys.Add(entity);
        }

        public void UpdateRecord(Eatery entity)
        {
            efContext.Entry(entity).State = EntityState.Modified;
        }
    }

    public interface IEateryRepository : IRepository<Eatery>
    {
    }
}

Notes:
none.

IRepository Interface
using BPX.Utils;
using System.Linq.Expressions;
using X.PagedList;

namespace BPX.DAL.Repositories
{
    public interface IRepository<T> where T : class
    {
        T? GetRecordById(int id);

        IPagedList<T> GetPaginatedRecords(PagingParams pagingParams);

        IQueryable<T> GetRecordsByFilter(Expression<Func<T, bool>> filter);

        void InsertRecord(T entity);

        void UpdateRecord(T entity);
    }
}

Notes:
none

Role Repository
using BPX.DAL.Context;
using BPX.Domain.DbModels;
using BPX.Domain.FilterModels;
using BPX.Utils;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System.Linq.Expressions;
using X.PagedList;

namespace BPX.DAL.Repositories
{
    public class RoleRepository : BaseRepository, IRoleRepository
    {
        public RoleRepository(EFContext efContext) : base(efContext)
        {
        }

        public IPagedList<Role> GetPaginatedRecords(PagingParams pagingParams)
        {
            // sanitize the pagingParams
            PagingParams ppObj = new PagingParams().SanitizePagingParams(pagingParams);

            // get model : IQueryable
            IQueryable<Role> model = efContext.Roles;

            // apply statusFlag
            model = efContext.Roles.Where(c => c.StatusFlag.ToUpper().Equals(ppObj.StatusFlag.ToUpper()));

            // apply generic search
            if (ppObj.SearchForString.Length > 0)
            {
                model = model.Where(c => c.Name.ToUpper().StartsWith(ppObj.SearchForString.ToUpper()));
            }

            // apply advanced search using filters
            if (ppObj.FilterJson.Length > 0)
            {
                RoleFM? roleFM = JsonConvert.DeserializeObject<RoleFM>(ppObj.FilterJson);

                if (roleFM != null)
                {
                    if (roleFM.Name != null)
                        model = model.Where(c => c.Name.ToUpper().StartsWith(roleFM.Name.Trim().ToUpper()));
                }
            }

            // apply sort by column, sort order
            model = ppObj.SortByColumn.ToUpper() switch
            {
                "NAME" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.Name) : model.OrderBy(c => c.Name),
                _ => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.RoleId) : model.OrderBy(c => c.RoleId),
            };

            // return ToPagedList()
            return model.ToPagedList(ppObj.PageNumber, ppObj.PageSize);
        }

        public Role? GetRecordById(int id)
        {
            return efContext.Roles.Where(c => c.RoleId.Equals(id)).SingleOrDefault();
        }

        public IQueryable<Role> GetRecordsByFilter(Expression<Func<Role, bool>> filter)
        {
            return efContext.Roles.Where(filter);
        }

        public void InsertRecord(Role entity)
        {
            efContext.Roles.Add(entity);
        }

        public void UpdateRecord(Role entity)
        {
            efContext.Entry(entity).State = EntityState.Modified;
        }
    }

    public interface IRoleRepository : IRepository<Role>
    {
    }
}

Notes:
none

User Repository
using BPX.DAL.Context;
using BPX.Domain.DbModels;
using BPX.Domain.FilterModels;
using BPX.Utils;
using Microsoft.EntityFrameworkCore;
using Newtonsoft.Json;
using System.Linq.Expressions;
using X.PagedList;

namespace BPX.DAL.Repositories
{
    public class UserRepository : BaseRepository, IUserRepository
    {
        public UserRepository(EFContext efContext) : base(efContext)
        {
        }

        public IPagedList<User> GetPaginatedRecords(PagingParams pagingParams)
        {
            // sanitize the pagingParams
            PagingParams ppObj = new PagingParams().SanitizePagingParams(pagingParams);

            // get model : IQueryable
            IQueryable<User> model = efContext.Users;

            // apply statusFlag
            model = model.Where(c => c.StatusFlag.ToUpper().Equals(ppObj.StatusFlag.ToUpper()));

            // apply generic search
            if (ppObj.SearchForString.Length > 0)
            {
                model = model.Where(c => c.LastName.ToUpper().StartsWith(ppObj.SearchForString.ToUpper())
                                || c.FirstName.ToUpper().StartsWith(ppObj.SearchForString.ToUpper())
                                || c.Email.ToUpper().StartsWith(ppObj.SearchForString.ToUpper()));
            }

            // apply advanced search using filters
            if (ppObj.FilterJson.Length > 0)
            {
                UserFM? userFM = JsonConvert.DeserializeObject<UserFM>(ppObj.FilterJson);

                if (userFM != null)
                {
                    if (userFM.FirstName != null)
                        model = model.Where(c => c.FirstName.ToUpper().StartsWith(userFM.FirstName.Trim().ToUpper()));
                    else if (userFM.LastName != null)
                        model = model.Where(c => c.LastName.ToUpper().StartsWith(userFM.LastName.Trim().ToUpper()));
                }
            }

            // apply sort by column, sort order
            model = ppObj.SortByColumn.ToUpper() switch
            {
                "FIRSTNAME" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.FirstName) : model.OrderBy(c => c.FirstName),
                "LASTNAME" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.LastName) : model.OrderBy(c => c.LastName),
                "EMAIL" => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.Email) : model.OrderBy(c => c.Email),
                _ => (ppObj.SortOrder.ToUpper().Equals(DbSortOrder.Descending.ToUpper())) ? model.OrderByDescending(c => c.UserId) : model.OrderBy(c => c.UserId),
            };

            // return ToPagedList()
            return model.ToPagedList(ppObj.PageNumber, ppObj.PageSize);
        }

        public User? GetRecordById(int id)
        {
            return efContext.Users.Where(c => c.UserId.Equals(id)).SingleOrDefault();
        }

        public IQueryable<User> GetRecordsByFilter(Expression<Func<User, bool>> filter)
        {
            return efContext.Users.Where(filter);
        }

        public void InsertRecord(User entity)
        {
            efContext.Users.Add(entity);
        }

        public void UpdateRecord(User entity)
        {
            efContext.Entry(entity).State = EntityState.Modified;
        }
    }

    public interface IUserRepository : IRepository<User>
    {
    }
}

Notes:
none

UserRole Repository
using BPX.DAL.Context;
using BPX.Domain.DbModels;
using BPX.Utils;
using Microsoft.EntityFrameworkCore;
using System.Linq.Expressions;
using X.PagedList;

namespace BPX.DAL.Repositories
{
    public class UserRoleRepository : BaseRepository, IUserRoleRepository
    {
        public UserRoleRepository(EFContext efContext) : base(efContext)
        {
        }

        public IPagedList<UserRole> GetPaginatedRecords(PagingParams pagingParams)
        {
            throw new NotImplementedException();
        }

        public UserRole? GetRecordById(int id)
        {
            throw new NotImplementedException();
        }

        public IQueryable<UserRole> GetRecordsByFilter(Expression<Func<UserRole, bool>> filter)
        {
            return efContext.UserRoles.Where(filter);
        }

        public void InsertRecord(UserRole entity)
        {
            efContext.UserRoles.Add(entity);
        }

        public void UpdateRecord(UserRole entity)
        {
            efContext.Entry(entity).State = EntityState.Modified;
        }
    }

    public interface IUserRoleRepository : IRepository<UserRole>
    {
    }
}

Notes:
none

Eatery Filter Model
namespace BPX.Domain.FilterModels
{
    public class EateryFM
    {
        public EateryFM()
        {
            this.Name = string.Empty;
            this.Address = string.Empty;
            this.Phone = string.Empty;
        }

        public string Name { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
    }
}

Notes:
none

Role Filter Model
namespace BPX.Domain.FilterModels
{
    public class RoleFM
    {
        public RoleFM()
        {
            this.Name = string.Empty;
        }

        public string Name { get; set; }
    }
}

Notes:
none

User Filter Model
namespace BPX.Domain.FilterModels
{
    public class UserFM
    {
        public UserFM()
        {
            this.FirstName = string.Empty;
            this.LastName = string.Empty;
        }

        public string FirstName { get; set; }
        public string LastName { get; set; }
    }
}

Notes:
none

DbRecordStatus
namespace BPX.Utils
{
    public static class DbRecordStatus
    {
        // variables for the record status
        public const string Active = "A";
        public const string Inactive = "I";
        public const string Archived = "R";
    }
}

Notes:
none

DbSortOrder
namespace BPX.Utils
{
    public static class DbSortOrder
    {
        // variables for the record status
        public const string Ascending = "ASC";
        public const string Descending = "DESC";
    }
}

Notes:
none

PagingParams
namespace BPX.Utils
{
    public class PagingParams
    {
        public int PageNumber { get; set; }
        public int PageSize { get; set; }
        public string StatusFlag { get; set; }
        public string SortByColumn { get; set; }
        public string SortOrder { get; set; }
        public string SearchForString { get; set; }
        public string FilterJson { get; set; }

        public PagingParams()
        {
            this.PageNumber = 1;
            this.PageSize = 10;
            this.StatusFlag = DbRecordStatus.Active;
            this.SortByColumn = "DefaultColumn";
            this.SortOrder = DbSortOrder.Ascending;
            this.SearchForString = string.Empty;
            this.FilterJson = string.Empty;
        }

        public PagingParams SanitizePagingParams(PagingParams pagingParams)
        {
            pagingParams.StatusFlag = pagingParams.StatusFlag == null ? DbRecordStatus.Active : pagingParams.StatusFlag.Trim().Length.Equals(0) ? DbRecordStatus.Active : pagingParams.StatusFlag.Trim();
            pagingParams.SortByColumn = pagingParams.SortByColumn == null ? "DefaultColumn" : pagingParams.SortByColumn.Trim().Length.Equals(0) ? "DefaultColumn" : pagingParams.SortByColumn.Trim();
            pagingParams.SortOrder = pagingParams.SortOrder == null ? DbSortOrder.Ascending : pagingParams.SortOrder.Trim().Length.Equals(0) ? DbSortOrder.Ascending : pagingParams.SortOrder.Trim();
            pagingParams.SearchForString = pagingParams.SearchForString == null ? string.Empty : pagingParams.SearchForString.Trim();
            pagingParams.FilterJson = pagingParams.FilterJson == null ? string.Empty: pagingParams.FilterJson.Trim();

            return pagingParams;
        }
    }
}

Notes:
none

Test Controller
using BPX.DAL.Repositories;
using BPX.Utils;
using Microsoft.AspNetCore.Mvc;

namespace BPX.Website.Controllers
{
    public class TestController : BaseController<TestController>
    {
        public RoleRepository roleRepository;
        public UserRepository userRepository;
        public UserRoleRepository userRoleRepository;
        public EateryRepository eateryRepository;

        public TestController(ILogger<TestController> logger, IRoleRepository roleRepository, IUserRepository userRepository, IUserRoleRepository userRoleRepository, IEateryRepository eateryRepository) : base(logger)
        {
            this.roleRepository = (RoleRepository) roleRepository;
            this.userRepository = (UserRepository)userRepository;
            this.userRoleRepository = (UserRoleRepository)userRoleRepository;
            this.eateryRepository = (EateryRepository)eateryRepository;
        }

        public IActionResult Index()
        {
            var rslt1 = userRepository.GetPaginatedRecords(new PagingParams());

            return View();
        }

        public IActionResult List(PagingParams pagingParams)
        {
            var rslt1 = userRepository.GetPaginatedRecords(pagingParams);

            return View();
        }
    }
}

Notes:
none

Index.cshtml
@{
    ViewData["Title"] = "Test page";
}

<form id="formForPaging" action="/Test/List" method="post">
    <input type="hidden" id="pageNumber" name="pageNumber" value="2" />
    <input type="hidden" id="pageSize" name="pageSize" value="5" />
    <input type="hidden" id="statusFlag" name="statusFlag" value="" />
    <input type="hidden" id="sortByColumn" name="sortByColumn" value="" />
    <input type="hidden" id="sortOrder" name="sortOrder" value="" />
    <input type="hidden" id="searchForString" name="searchForString" value="Ca" />
    <input type="hidden" id="filterJson" name="filterJson" value="" />

    <input type="submit" value="submit for paging" />
</form>

Notes:
none

List.cshtml
@{
    ViewData["Title"] = "Test page";
}

<h4>Test List Page</h4>

Notes:
none



 


Ginger CMS
the future of cms, a simple and intuitive content management system ...

ASP.NET MVC Application
best practices like Repository, LINQ, Dapper, Domain objects ...

CFTurbine
cf prototyping engine, generates boilerplate code and views ...

Search Engine LITE
create your own custom search engine for your web site ...

JRun monitor
monitors the memory footprint of JRun engine and auto-restarts a hung engine ...

Validation Library
complete validation library for your web forms ...