Dynamic pagination for .net. ALLOWS: Search by, search by a range of dates, filter multiple elements, limit or unlimit queries.

Pagination.cs

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Linq.Expressions;
using System.Text.Json;
using System.Text.Json.Serialization;
using System.Threading;
using System.Threading.Tasks;
using project.Models.Common.Filters;
using project.Utils;
using static project.Models.Common.PaginationRequest;
namespace project.Models.Common
{
    public class Pagination<TModel> where TModel : class
    {
        const int MaxPageSize = 500;
        private int _pageSize;
        public int PageSize
        {
            get => _pageSize;
            set => _pageSize = (value > MaxPageSize) ? MaxPageSize : value;
        }

        public int CurrentPage { get; set; }
        public int TotalItems { get; set; }
        public int TotalPages { get; set; } 
        public List<TModel> Items { get; set; }

        public Pagination()
        {
            Items = new List<TModel>();
        }

    }
    public static class Pagination
    {
        public static async Task HandleTimezoneDateFilters(PaginationRequest options, int projectId, TimeZoneUtility timeZoneUtility)
        {
            if (options.StartDate != null && options.EndDate != null && projectId > 0)
            {
                options.StartDate = await timeZoneUtility.ConvertToUTC(options.StartDate, projectId);
                options.EndDate = await timeZoneUtility.ConvertToUTC(options.EndDate, projectId);
            }
        }
    }


        public static class DataPagerExtension
    {
        public static async Task<Pagination<TDto>> PaginateAsync<TModel, TDto>(
            this IQueryable<TModel> query,
            PaginationRequest options,
            CancellationToken cancellationToken = default,
            Expression<Func<TModel, TDto>> projection = null
            )
            where TModel : class
            where TDto : class
        {

            var paged = new Pagination<TDto>();

            options.Page = (options.Page <= 0 || options.Unlimited) ? 1 : options.Page;

            paged.CurrentPage = (int)options.Page;
            paged.PageSize = (int)options.Limit;

            var startRow = (options.Page - 1) * options.Limit;
            if (!string.IsNullOrEmpty(options.FiltersJson))
            {
                options.Filters = JsonSerializer.Deserialize<List<FilterCriterion>>(options.FiltersJson);
            }
            
            if (options.Filters?.Count > 0)
            {
                foreach (var filter in options.Filters)
                {
                    if (filter.SelectedValue != null && filter.SelectedValue.Count > 0)
                    {
                        var property = query.ElementType.GetProperty(filter.Name);
                        bool isNullable = property?.PropertyType.IsGenericType == true &&
                                          property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>);

                        string filterQuery;
                        object filterValues;

                        if (filter.SelectedValue.Count == 1)
                        {
                            filterQuery = isNullable
                                ? $"{filter.Name}.Value == @0"  
                                : $"{filter.Name} == @0";      

                            filterValues = filter.SelectedValue.First();
                        }
                        else
                        {                            
                            filterQuery = isNullable
                                ? $"{filter.Name}.HasValue && {filter.Name}.Value IN @0"
                                : $"{filter.Name} IN @0";

                            filterValues = filter.SelectedValue.ToArray(); 
                        }

                        query = query.Where(filterQuery, filterValues);
                    }
                }
            }



            if (options.SearchBy?.Length > 0 && options.Search != null)
            {
                var dynamicQuery = "";
                foreach (var SearchBy in options.SearchBy)
                {
                    if (string.IsNullOrEmpty(dynamicQuery))
                    {
                        dynamicQuery = $"Convert.ToString({SearchBy}).Contains(@0)";
                    }
                    else
                    {
                        dynamicQuery += $" OR Convert.ToString({SearchBy}).Contains(@0)";
                    }
                }
                query = query.Where(dynamicQuery, options.Search);
            }

            var projectId = HttpContextHelper.GetProjectId();
            if (options.StartDate != null && options.EndDate != null && projectId > 0)
            {
                DateRangeFilter dateRangeFilter = new DateRangeFilter
                {
                    StartDate = options.StartDate,
                    EndDate = options.EndDate,
                    AllowOverlap = options.OverlapFilterDates
                };

                if (options.StartDateField != null && options.EndDateField != null)
                {
                    query = query.FilterByRangeDate(dateRangeFilter, options.StartDateField, options.EndDateField);
                }
            }
          
            paged.TotalItems = await query.CountAsync(cancellationToken);

            if (options.OrderBy != null)
            {
                query = query.OrderBy((options.OrderBy ?? "Id") + (options.OrderDESC ? " ASC " : " DESC "));
            }


            if (projection != null)
            {
                paged.Items = await query
                    .Select(projection)
                    .Skip((int)startRow)
                    .Take((int)(options.Unlimited ? paged.TotalItems : options.Limit))
                    .ToListAsync(cancellationToken);
            }
            else
            {
                paged.Items = await query
                    .Skip((int)startRow)
                    .Take((int)(options.Unlimited ? paged.TotalItems : options.Limit))
                    .Select(x => x as TDto)
                    .ToListAsync(cancellationToken);
            }

            paged.TotalPages = options.Unlimited ? 1 : (int)Math.Ceiling(paged.TotalItems / (double)options.Limit);

            return paged;
        }


        public static async Task<Pagination<TModel>> PaginateAsync<TModel>(
            this IQueryable<TModel> query,
            PaginationRequest options,
            CancellationToken cancellationToken)
            where TModel : class
        {

            var paged = new Pagination<TModel>();

            options.Page = (options.Page <= 0 || options.Unlimited) ? 1 : options.Page;

            paged.CurrentPage = (int)options.Page;
            paged.PageSize = (int)options.Limit;


            var startRow = (options.Page - 1) * options.Limit;
            if (!string.IsNullOrEmpty(options.FiltersJson))
            {
                options.Filters = JsonSerializer.Deserialize<List<FilterCriterion>>(options.FiltersJson);
            }
            if (options.Filters?.Count > 0)
            {
                foreach (var filter in options.Filters)
                {
                    if (filter.SelectedValue != null && filter.SelectedValue.Count > 0)
                    {
                        if (filter.SelectedValue.Count == 1 && filter.SelectedValue.First() == 0)
                        {
                            // Si el valor es 0, ignorar el filtro y traer todos los datos
                            continue; // Salta este filtro
                        }
                        else if (filter.SelectedValue.Count == 1)
                        {
                            // Comparar con ==
                            query = query.Where($"{filter.Name} == @0", filter.SelectedValue.First());
                        }
                        else
                        {
                            //query = query.Where("@0.Contains(" + filter.Name + ")", filter.SelectedValue);
                            var values = filter.SelectedValue.Select(x => Convert.ToInt32(x)).ToArray();
                            query = query.Where("(@0).Contains(Convert.ToInt32(" + filter.Name + "))", values);
                        }
                    }
                }
            }

            if (options.SearchBy?.Length > 0 && options.Search != null)
            {
                var dynamicQuery = "";
                foreach (var SearchBy in options.SearchBy)
                {
                    if (string.IsNullOrEmpty(dynamicQuery))
                    {
                        dynamicQuery = $"Convert.ToString({SearchBy}).Contains(@0)";
                    }
                    else
                    {
                        dynamicQuery += $" OR Convert.ToString({SearchBy}).Contains(@0)";
                    }
                }
                query = query.Where(dynamicQuery, options.Search);
            }         

            var projectId = HttpContextHelper.GetProjectId();
            if (options.StartDate != null && options.EndDate != null && projectId > 0)
            {
                DateRangeFilter dateRangeFilter = new DateRangeFilter
                {
                    StartDate = options.StartDate,
                    EndDate = options.EndDate,
                    AllowOverlap = options.OverlapFilterDates
                };

                if (options.StartDateField != null && options.EndDateField != null)
                {
                    query = query.FilterByRangeDate(dateRangeFilter, options.StartDateField, options.EndDateField);
                }
            }         

            paged.TotalItems = await query.CountAsync(cancellationToken);

            if (!string.IsNullOrWhiteSpace(options.OrderBy))
                {
                    query = query.OrderBy(options.OrderBy + (options.OrderDESC ? " ASC " : " DESC "));
                }
                else
                {
                    query = query.OrderBy("Id" + (options.OrderDESC ? " ASC " : " DESC "));
                }


            paged.Items = await query
                                .Skip((int)startRow)
                                        .Take((int)(options.Unlimited ? paged.TotalItems : options.Limit))
                                        .ToListAsync(cancellationToken);

            paged.TotalPages = options.Unlimited ? 1 : (int)Math.Ceiling(paged.TotalItems / (double)options.Limit);

            return paged;
        }

    }

    /// <summary>
    /// Model response for when pagination is required
    /// 1. It will what the controller receives
    /// 2. SearchBy is declared in Listitems Handle service
    /// </summary>
    public class GetPaginationDto<TModelDTO>
    {
        public int CurrentPage { get; set; }

        public int TotalItems { get; set; }

        public int TotalPages { get; set; }

        public List<TModelDTO> Items { get; set; }
    }

    /// <summary>
    /// Model for when pagination is required
    /// 1. It will what the controller receives
    /// 2. SearchBy is declared in Listitems Handle service
    /// </summary>
    public class PaginationRequest
    {
        public int? Page { get; set; }
        public int? Limit { get; set; }
        public bool Unlimited { get; set; }
        public string[] SearchBy { get; set; }
        public string Search { get; set; }
        public string OrderBy { get; set; }
        public bool OrderDESC { get; set; }
        public int ProjectId { get; set; }
        public DateTime? StartDate { get; set; }
        public DateTime? EndDate { get; set; }
        public string? StartDateField { get; set; }
        public string? EndDateField { get; set; }
        public string FiltersJson { get; set; }
        public List<FilterCriterion> Filters { get; set; } = new List<FilterCriterion>();
        public bool? OverlapFilterDates { get; set; }

        public PaginationRequest()
        {
            Page = 1;
            Limit = 25;
            OrderBy = "Id";
            OrderDESC = false;
            Search = null;
            ProjectId = 0;
            Unlimited = false;
            StartDateField = "CreatedAt";
            EndDateField = "CreatedAt";
            Filters = [];
            OverlapFilterDates = false;

        }
        public class FilterCriterion
        {
            [JsonPropertyName("name")]
            public string Name { get; set; }
            [JsonPropertyName("selectedValue")]
            public List<int> SelectedValue { get; set; }
        }
    }
}