Generic Filter, Sorting, Grouping And Paging For Any View Model In Server Side

This will help you to apply generic filtering on any properties, Sorting on any fields, grouping on any fields and paging on generic collections. It will avoid the network traffic and reuse the code extensively.

Recently, I was working on a Web API application which is consumed by Angular UI for displaying multiple grids while loading the website itself. And my application is using a lot of grids and tables on the UI which has all the operations like Filtering, Sorting, Grouping, and Paging also. But to do this operation on the client side, I see a lot of code has to be written on the front-end side for each page or view.
 
Here, the problem is to bind the different models in different components and repeat the similar code for filtering, sorting, grouping, and paging for each model; but each model has different fields and different data types.
 
For example, 
 
<,<=,>,>=,!=,== filters we can be applied on the numeric and date datatypes, but for string data type, we need to use different filters, like - starts with, ends with, contains, is empty, is not empty etc. 
 
Then, for unit testing, I need to write a lot of code. Down the line, it will create a big headache for maintaining this business logic for each model because my ViewModelsare keeps on increasing.
 
Then, I thought of refactoring this by using Generics and Reflection to make it simple and we can use this code across the application. Please see below what I did.
 
Step 1
 
Create a FilterUtility class like below for applying the generic filter.
  1. public class FilterUtility  
  2. {  
  3.       
  4. }  
Step 2
 
Then, inside the FilterUtility class create an enum for different filter option. I have mentioned common filters. You can still add from your own if you need. 
  1.         /// <summary>  
  2.         /// Enums for filter options  
  3.         /// same sequence UI is following  
  4.         /// </summary>  
  5.         public enum FilterOptions  
  6.         {  
  7.             StartsWith = 1,  
  8.             EndsWith,  
  9.             Contains,  
  10.             DoesNotContain,  
  11.             IsEmpty,  
  12.             IsNotEmpty,  
  13.             IsGreaterThan,  
  14.             IsGreaterThanOrEqualTo,  
  15.             IsLessThan,  
  16.             IsLessThanOrEqualTo,  
  17.             IsEqualTo,  
  18.             IsNotEqualTo  
  19.         }  

Step 3

Create another class called FilterParams for different filter parameters inside the FilterUtility class like below.
  1.         /// <summary>  
  2.         /// Filter parameters Model Class  
  3.         /// </summary>  
  4.         public class FilterParams  
  5.         {  
  6.             public string ColumnName { getset; } = string.Empty;  
  7.             public string FilterValue { getset; } = string.Empty;  
  8.             public FilterOptions FilterOption { getset; } = FilterOptions.Contains;  
  9.         }  

Step 4

Create a generic Filter<T> class which will be responsible for filtering the data inside the FilterUtility class.
 
In the below class I have mentioned two methods i.e. FilteredData() and FilterData().
 
FilterData() method will actually filter the data based on FilterOptions using reflection. Some filter options are applicable to specific data types. Like for string data type we use starts with, ends with, contains etc.
 
The FilteredData() method is responsible for handling multiple filters in the complex type, because the above code can handle multiple filters at the same time. Since I have used generics here, I need to use the reflection to get the property name of any complex type.
  1.         /// <summary>  
  2.         /// This is generic class   
  3.         /// responsible for filtering the data  
  4.         /// </summary>  
  5.         /// <typeparam name="T"></typeparam>  
  6.         public class Filter<T>  
  7.         {  
  8.             public static IEnumerable<T> FilteredData(IEnumerable<FilterParams> filterParams, IEnumerable<T> data)  
  9.             {  
  10.   
  11.                 IEnumerable<string> distinctColumns = filterParams.Where(x => !String.IsNullOrEmpty(x.ColumnName)).Select(x => x.ColumnName).Distinct();  
  12.   
  13.                 foreach (string colName in distinctColumns)  
  14.                 {  
  15.                     var filterColumn = typeof(T).GetProperty(colName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);  
  16.                     if (filterColumn != null)  
  17.                     {  
  18.                         IEnumerable<FilterParams> filterValues = filterParams.Where(x => x.ColumnName.Equals(colName)).Distinct();  
  19.   
  20.                         if (filterValues.Count() > 1)  
  21.                         {  
  22.                             IEnumerable<T> sameColData = Enumerable.Empty<T>();  
  23.   
  24.                             foreach (var val in filterValues)  
  25.                             {  
  26.                                 sameColData = sameColData.Concat(FilterData(val.FilterOption, data, filterColumn, val.FilterValue));  
  27.                             }  
  28.   
  29.                             data = data.Intersect(sameColData);  
  30.                         }  
  31.                         else  
  32.                         {  
  33.                             data = FilterData(filterValues.FirstOrDefault().FilterOption, data, filterColumn, filterValues.FirstOrDefault().FilterValue);  
  34.                         }  
  35.                     }  
  36.                 }  
  37.                 return data;  
  38.             }  
  39.             private static IEnumerable<T> FilterData(FilterOptions filterOption, IEnumerable<T> data, PropertyInfo filterColumn, string filterValue)  
  40.             {  
  41.                 int outValue;  
  42.                 DateTime dateValue;  
  43.                 switch (filterOption)  
  44.                 {  
  45.                     #region [StringDataType]  
  46.   
  47.                     case FilterOptions.StartsWith:  
  48.                         data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().StartsWith(filterValue.ToString().ToLower())).ToList();  
  49.                         break;  
  50.                     case FilterOptions.EndsWith:  
  51.                         data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().EndsWith(filterValue.ToString().ToLower())).ToList();  
  52.                         break;  
  53.                     case FilterOptions.Contains:  
  54.                         data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().Contains(filterValue.ToString().ToLower())).ToList();  
  55.                         break;  
  56.                     case FilterOptions.DoesNotContain:  
  57.                         data = data.Where(x => filterColumn.GetValue(x, null) == null ||  
  58.                                          (filterColumn.GetValue(x, null) != null && !filterColumn.GetValue(x, null).ToString().ToLower().Contains(filterValue.ToString().ToLower()))).ToList();  
  59.                         break;  
  60.                     case FilterOptions.IsEmpty:  
  61.                         data = data.Where(x => filterColumn.GetValue(x, null) == null ||  
  62.                                          (filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString() == string.Empty)).ToList();  
  63.                         break;  
  64.                     case FilterOptions.IsNotEmpty:  
  65.                         data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString() != string.Empty).ToList();  
  66.                         break;  
  67.                     #endregion  
  68.  
  69.                     #region [Custom]  
  70.   
  71.                     case FilterOptions.IsGreaterThan:  
  72.                         if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  73.                         {  
  74.                             data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) > outValue).ToList();  
  75.                         }  
  76.                         else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  77.                         {  
  78.                             data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) > dateValue).ToList();  
  79.   
  80.                         }  
  81.                         break;  
  82.   
  83.                     case FilterOptions.IsGreaterThanOrEqualTo:  
  84.                         if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  85.                         {  
  86.                             data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) >= outValue).ToList();  
  87.                         }  
  88.                         else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  89.                         {  
  90.                             data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) >= dateValue).ToList();  
  91.                             break;  
  92.                         }  
  93.                         break;  
  94.   
  95.                     case FilterOptions.IsLessThan:  
  96.                         if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  97.                         {  
  98.                             data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) < outValue).ToList();  
  99.                         }  
  100.                         else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  101.                         {  
  102.                             data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) < dateValue).ToList();  
  103.                             break;  
  104.                         }  
  105.                         break;  
  106.   
  107.                     case FilterOptions.IsLessThanOrEqualTo:  
  108.                         if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  109.                         {  
  110.                             data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) <= outValue).ToList();  
  111.                         }  
  112.                         else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  113.                         {  
  114.                             data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) <= dateValue).ToList();  
  115.                             break;  
  116.                         }  
  117.                         break;  
  118.   
  119.                     case FilterOptions.IsEqualTo:  
  120.                         if (filterValue == string.Empty)  
  121.                         {  
  122.                             data = data.Where(x => filterColumn.GetValue(x, null) == null  
  123.                                             || (filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() == string.Empty)).ToList();  
  124.                         }  
  125.                         else  
  126.                         {  
  127.                             if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  128.                             {  
  129.                                 data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) == outValue).ToList();  
  130.                             }  
  131.                             else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  132.                             {  
  133.                                 data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) == dateValue).ToList();  
  134.                                 break;  
  135.                             }  
  136.                             else  
  137.                             {  
  138.                                 data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() == filterValue.ToLower()).ToList();  
  139.                             }  
  140.                         }  
  141.                         break;  
  142.   
  143.                     case FilterOptions.IsNotEqualTo:  
  144.                         if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))  
  145.                         {  
  146.                             data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) != outValue).ToList();  
  147.                         }  
  148.                         else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))  
  149.                         {  
  150.                             data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) != dateValue).ToList();  
  151.                             break;  
  152.                         }  
  153.                         else  
  154.                         {  
  155.                             data = data.Where(x => filterColumn.GetValue(x, null) == null ||  
  156.                                              (filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() != filterValue.ToLower())).ToList();  
  157.                         }  
  158.                         break;  
  159.                         #endregion  
  160.                 }  
  161.                 return data;  
  162.             }  
  163.         }  
Step 5: Create a separate class called SortingUtility like below.
  1. public class SortingUtility  
  2. {  
  3.   
  4. }  
Step 6
 
Create an enum for SortOrders inside the SortingUtlity class like below.
  1. public enum SortOrders  
  2.         {  
  3.             Asc = 1,  
  4.             Desc = 2  
  5.         }  

Step 7

Create a another child class for sorting for the specific parameter inside the SortingUtility.
  1. public class SortingParams  
  2.         {  
  3.             public SortOrders SortOrder { getset; } = SortOrders.Asc;  
  4.             public string ColumnName { getset; }  
  5.         }  
Step 8
 
Create a child class for sorting and grouping the columns inside the SortingUtility class.
  1.         /// <summary>  
  2.         /// Enum for Sorting order  
  3.         /// Asc = Ascending  
  4.         /// Desc = Descending  
  5.         /// </summary>  
  6.   
  7.         public class Sorting<T>  
  8.         {  
  9.             /// <summary>  
  10.             /// Actual grouping will be done in ui,   
  11.             /// from api we will send sorted data based on grouping columns  
  12.             /// </summary>  
  13.             /// <param name="data"></param>  
  14.             /// <param name="groupingColumns"></param>  
  15.             /// <returns></returns>  
  16.             public static IEnumerable<T> GroupingData(IEnumerable<T> data, IEnumerable<string> groupingColumns)  
  17.             {  
  18.                 IOrderedEnumerable<T> groupedData = null;  
  19.   
  20.                 foreach (string grpCol in groupingColumns.Where(x => !String.IsNullOrEmpty(x)))  
  21.                 {  
  22.                     var col = typeof(T).GetProperty(grpCol, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);  
  23.                     if (col != null)  
  24.                     {  
  25.                         groupedData = groupedData == null ? data.OrderBy(x => col.GetValue(x, null))  
  26.                                                         : groupedData.ThenBy(x => col.GetValue(x, null));  
  27.                     }  
  28.                 }  
  29.   
  30.                 return groupedData ?? data;  
  31.             }  
  32.             public static IEnumerable<T> SortData(IEnumerable<T> data, IEnumerable<SortingParams> sortingParams)  
  33.             {  
  34.                 IOrderedEnumerable<T> sortedData = null;  
  35.                 foreach (var sortingParam in sortingParams.Where(x=> !String.IsNullOrEmpty(x.ColumnName)))  
  36.                 {  
  37.                     var col = typeof(T).GetProperty(sortingParam.ColumnName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);  
  38.                     if (col != null)  
  39.                     {  
  40.                         sortedData = sortedData == null ? sortingParam.SortOrder == SortOrders.Asc ? data.OrderBy(x => col.GetValue(x, null))  
  41.                                                                                                    : data.OrderByDescending(x => col.GetValue(x, null))  
  42.                                                         : sortingParam.SortOrder == SortOrders.Asc ? sortedData.ThenBy(x => col.GetValue(x, null))  
  43.                                                                                             : sortedData.ThenByDescending(x => col.GetValue(x, null));  
  44.                     }  
  45.                 }  
  46.                 return sortedData ?? data;  
  47.             }  
  48.   
  49.         }  

The above class has two methods, GroupingData() which will be responsible for grouping more than two columns and SortData() method which will be responsible for sorting the data on multiple columns using reflection. Since I have used generics here so I need to use the reflection to get the property name of any complex type. 

Step 9
 
Create another class for generic paging on the server side like below. It has 3 properties i.e. PageIndex, TotalPages, TotalItems.
 
One parametrized constructor is also required for calculating the number of items in the collections. Also, I have added two boolean properties to identify the next page or the previous page.
 
Lastly, I have created a static method to return the paginated list of generic type.
  1. public class PaginatedList<T> : List<T>  
  2.     {  
  3.         public int PageIndex { getprivate set; }  
  4.         public int TotalPages { getprivate set; }  
  5.         public int TotalItems { getprivate set; }  
  6.   
  7.         public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)  
  8.         {  
  9.             PageIndex = pageIndex;  
  10.             TotalItems = count;  
  11.             TotalPages = (int)Math.Ceiling(count / (double)pageSize);  
  12.   
  13.             this.AddRange(items);  
  14.         }  
  15.   
  16.         public bool HasPreviousPage  
  17.         {  
  18.             get  
  19.             {  
  20.                 return (PageIndex > 1);  
  21.             }  
  22.         }  
  23.   
  24.         public bool HasNextPage  
  25.         {  
  26.             get  
  27.             {  
  28.                 return (PageIndex < TotalPages);  
  29.             }  
  30.         }  
  31.   
  32.         public static async Task<PaginatedList<T>> CreateAsync(IList<T> source, int pageIndex, int pageSize)  
  33.         {  
  34.             var count =  source.Count;  
  35.             var items =  source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();  
  36.             return new PaginatedList<T>(items, count, pageIndex, pageSize);  
  37.         }  
  38.     }  
Step 10
 
Create an input model for applying the above options in one shot like below. It has some properties which have some significance as I mentioned in the comment section.
  1.     /// <summary>  
  2.     /// This class contains properites used for paging, sorting, grouping, filtering and will be used as a parameter model  
  3.     ///   
  4.     /// SortOrder   - enum of sorting orders  
  5.     /// SortColumn  - Name of the column on which sorting has to be done,  
  6.     ///               as for now sorting can be performed only on one column at a time.  
  7.     ///FilterParams - Filtering can be done on multiple columns and for one column multiple values can be selected  
  8.     ///               key :- will be column name, Value :- will be array list of multiple values  
  9.     ///GroupingColumns - It will contain column names in a sequence on which grouping has been applied   
  10.     ///PageNumber   - Page Number to be displayed in UI, default to 1  
  11.     ///PageSize     - Number of items per page, default to 25  
  12.     /// </summary>  
  13.     public class PaginatedInputModel  
  14.     {  
  15.         public IEnumerable<SortingUtility.SortingParams> SortingParams {setget;}  
  16.         public IEnumerable<FilterUtility.FilterParams> FilterParam { getset; }  
  17.         public IEnumerable<string> GroupingColumns { getset; } = null;  
  18.         int pageNumber = 1;  
  19.         public int PageNumber { get { return pageNumber; } set { if (value > 1) pageNumber = value; } }  
  20.   
  21.         int pageSize = 25;  
  22.         public int PageSize { get { return pageSize; } set { if (value > 1) pageSize = value; } }  
  23.     }  
Step 11
 
In your common repository, you can get the data from any source like from DB using EF or ADO.net. Here in the below example, I stored the data in in-memory caching; from that I am applying all filters, sorting, grouping, and paging. I have used here SampleViewModel. You can use your ViewModel instead of that. Please see below for the details.
  1. static readonly MemoryCache objCache = new MemoryCache(new MemoryCacheOptions());  
  2. public async Task<PaginatedList<SampleViewModel>> PagingMethod(PaginatedInputModel pagingParams)  
  3.         {  
  4.             List<SampleViewModel> sampleList = null;  
  5.  
  6.             #region [Caching]  
  7.             if (objCache.Get("SampleCacheId") != null)  
  8.             {  
  9.                 sampleList = (List<SampleViewModel>)objCache.Get("SampleCacheId");  
  10.             }  
  11.             else  
  12.             {  
  13.                 sampleList = _context.SampleViewModel.FromSql("usp_AllJobs").ToList();  
  14.                 objCache.Set("SampleCacheId", sampleList, new MemoryCacheEntryOptions().SetAbsoluteExpiration(TimeSpan.FromMinutes(10)));  
  15.             }  
  16.             #endregion  
  17.  
  18.             #region [Filter]  
  19.             if (pagingParams != null && pagingParams.FilterParam.Any())  
  20.             {  
  21.                 sampleList = FilterUtility.Filter<SampleViewModel>.FilteredData(pagingParams.FilterParam, sampleList).ToList() ?? sampleList;  
  22.             }  
  23.             #endregion  
  24.  
  25.             #region [Sorting]  
  26.             if (pagingParams != null && pagingParams.SortingParams.Count() > 0 && Enum.IsDefined(typeof(SortingUtility.SortOrders), pagingParams.SortingParams.Select(x => x.SortOrder)))  
  27.             {  
  28.                 sampleList = SortingUtility.Sorting<SampleViewModel>.SortData(sampleList, pagingParams.SortingParams).ToList();  
  29.             }  
  30.             #endregion  
  31.  
  32.             #region [Grouping]  
  33.             if (pagingParams != null && pagingParams.GroupingColumns != null && pagingParams.GroupingColumns.Count() > 0)  
  34.             {  
  35.                 sampleList = SortingUtility.Sorting<SampleViewModel>.GroupingData(sampleList, pagingParams.GroupingColumns).ToList() ?? sampleList;  
  36.             }  
  37.             #endregion  
  38.  
  39.             #region [Paging]  
  40.             return await PaginatedList<SampleViewModel>.CreateAsync(sampleList, pagingParams.PageNumber, pagingParams.PageSize);  
  41.             #endregion  
  42.         }  

Before calling this function please validate the paging input model for the invalid parameters for SortingParams, FilterParams, PageSize, PageNumber etc.

This code snippet you can use inside the Web API, ASP.NET MVC application, which is already unit tested. It will definitely improve the performance by avoiding the network traffic. Also, it will avoid lots of code writing in the front end to achieve this. This will be helpful for any complex type for any properties. Please let me know if you are facing any problems.