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

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.

public class FilterUtility
{
}

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.

/// <summary>
/// Enums for filter options
/// same sequence UI is following
/// </summary>
public enum FilterOptions
{
StartsWith = 1,
EndsWith,
Contains,
DoesNotContain,
IsEmpty,
IsNotEmpty,
IsGreaterThan,
IsGreaterThanOrEqualTo,
IsLessThan,
IsLessThanOrEqualTo,
IsEqualTo,
IsNotEqualTo
}

Step 3

Create another class called FilterParams for different filter parameters inside the FilterUtility class like below.

/// <summary>
/// Filter parameters Model Class
/// </summary>
public class FilterParams
{
public string ColumnName { get; set; } = string.Empty;
public string FilterValue { get; set; } = string.Empty;
public FilterOptions FilterOption { get; set; } = FilterOptions.Contains;
}

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.

/// <summary>
/// This is generic class
/// responsible for filtering the data
/// </summary>
/// <typeparam name="T"></typeparam>
public class Filter<T>
{
public static IEnumerable<T> FilteredData(IEnumerable<FilterParams> filterParams, IEnumerable<T> data)
{
IEnumerable<string> distinctColumns = filterParams.Where(x => !String.IsNullOrEmpty(x.ColumnName)).Select(x => x.ColumnName).Distinct();
foreach (string colName in distinctColumns)
{
var filterColumn = typeof(T).GetProperty(colName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
if (filterColumn != null)
{
IEnumerable<FilterParams> filterValues = filterParams.Where(x => x.ColumnName.Equals(colName)).Distinct();
if (filterValues.Count() > 1)
{
IEnumerable<T> sameColData = Enumerable.Empty<T>();
foreach (var val in filterValues)
{
sameColData = sameColData.Concat(FilterData(val.FilterOption, data, filterColumn, val.FilterValue));
}
data = data.Intersect(sameColData);
}
else
{
data = FilterData(filterValues.FirstOrDefault().FilterOption, data, filterColumn, filterValues.FirstOrDefault().FilterValue);
}
}
}
return data;
}
private static IEnumerable<T> FilterData(FilterOptions filterOption, IEnumerable<T> data, PropertyInfo filterColumn, string filterValue)
{
int outValue;
DateTime dateValue;
switch (filterOption)
{
#region [StringDataType]
case FilterOptions.StartsWith:
data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().StartsWith(filterValue.ToString().ToLower())).ToList();
break;
case FilterOptions.EndsWith:
data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().EndsWith(filterValue.ToString().ToLower())).ToList();
break;
case FilterOptions.Contains:
data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower().Contains(filterValue.ToString().ToLower())).ToList();
break;
case FilterOptions.DoesNotContain:
data = data.Where(x => filterColumn.GetValue(x, null) == null ||
(filterColumn.GetValue(x, null) != null && !filterColumn.GetValue(x, null).ToString().ToLower().Contains(filterValue.ToString().ToLower()))).ToList();
break;
case FilterOptions.IsEmpty:
data = data.Where(x => filterColumn.GetValue(x, null) == null ||
(filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString() == string.Empty)).ToList();
break;
case FilterOptions.IsNotEmpty:
data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString() != string.Empty).ToList();
break;
#endregion
#region [Custom]
case FilterOptions.IsGreaterThan:
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) > outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) > dateValue).ToList();
}
break;
case FilterOptions.IsGreaterThanOrEqualTo:
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) >= outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) >= dateValue).ToList();
break;
}
break;
case FilterOptions.IsLessThan:
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) < outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) < dateValue).ToList();
break;
}
break;
case FilterOptions.IsLessThanOrEqualTo:
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) <= outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) <= dateValue).ToList();
break;
}
break;
case FilterOptions.IsEqualTo:
if (filterValue == string.Empty)
{
data = data.Where(x => filterColumn.GetValue(x, null) == null
|| (filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() == string.Empty)).ToList();
}
else
{
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) == outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) == dateValue).ToList();
break;
}
else
{
data = data.Where(x => filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() == filterValue.ToLower()).ToList();
}
}
break;
case FilterOptions.IsNotEqualTo:
if ((filterColumn.PropertyType == typeof(Int32) || filterColumn.PropertyType == typeof(Nullable<Int32>)) && Int32.TryParse(filterValue, out outValue))
{
data = data.Where(x => Convert.ToInt32(filterColumn.GetValue(x, null)) != outValue).ToList();
}
else if ((filterColumn.PropertyType == typeof(Nullable<DateTime>)) && DateTime.TryParse(filterValue, out dateValue))
{
data = data.Where(x => Convert.ToDateTime(filterColumn.GetValue(x, null)) != dateValue).ToList();
break;
}
else
{
data = data.Where(x => filterColumn.GetValue(x, null) == null ||
(filterColumn.GetValue(x, null) != null && filterColumn.GetValue(x, null).ToString().ToLower() != filterValue.ToLower())).ToList();
}
break;
#endregion
}
return data;
}
}

Step 5: Create a separate class called SortingUtility like below.

public class SortingUtility
{
}

Step 6

Create an enum for SortOrders inside the SortingUtlity class like below.

public enum SortOrders
{
Asc = 1,
Desc = 2
}

Step 7

Create a another child class for sorting for the specific parameter inside the SortingUtility.

public class SortingParams
{
public SortOrders SortOrder { get; set; } = SortOrders.Asc;
public string ColumnName { get; set; }
}

Step 8

Create a child class for sorting and grouping the columns inside the SortingUtility class.

/// <summary>
/// Enum for Sorting order
/// Asc = Ascending
/// Desc = Descending
/// </summary>
public class Sorting<T>
{
/// <summary>
/// Actual grouping will be done in ui,
/// from api we will send sorted data based on grouping columns
/// </summary>
/// <param name="data"></param>
/// <param name="groupingColumns"></param>
/// <returns></returns>
public static IEnumerable<T> GroupingData(IEnumerable<T> data, IEnumerable<string> groupingColumns)
{
IOrderedEnumerable<T> groupedData = null;
foreach (string grpCol in groupingColumns.Where(x => !String.IsNullOrEmpty(x)))
{
var col = typeof(T).GetProperty(grpCol, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
if (col != null)
{
groupedData = groupedData == null ? data.OrderBy(x => col.GetValue(x, null))
: groupedData.ThenBy(x => col.GetValue(x, null));
}
}
return groupedData ?? data;
}
public static IEnumerable<T> SortData(IEnumerable<T> data, IEnumerable<SortingParams> sortingParams)
{
IOrderedEnumerable<T> sortedData = null;
foreach (var sortingParam in sortingParams.Where(x=> !String.IsNullOrEmpty(x.ColumnName)))
{
var col = typeof(T).GetProperty(sortingParam.ColumnName, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public);
if (col != null)
{
sortedData = sortedData == null ? sortingParam.SortOrder == SortOrders.Asc ? data.OrderBy(x => col.GetValue(x, null))
: data.OrderByDescending(x => col.GetValue(x, null))
: sortingParam.SortOrder == SortOrders.Asc ? sortedData.ThenBy(x => col.GetValue(x, null))
: sortedData.ThenByDescending(x => col.GetValue(x, null));
}
}
return sortedData ?? data;
}
}

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.

public class PaginatedList<T> : List<T>
{
public int PageIndex { get; private set; }
public int TotalPages { get; private set; }
public int TotalItems { get; private set; }
public PaginatedList(List<T> items, int count, int pageIndex, int pageSize)
{
PageIndex = pageIndex;
TotalItems = count;
TotalPages = (int)Math.Ceiling(count / (double)pageSize);
this.AddRange(items);
}
public bool HasPreviousPage
{
get
{
return (PageIndex > 1);
}
}
public bool HasNextPage
{
get
{
return (PageIndex < TotalPages);
}
}
public static async Task<PaginatedList<T>> CreateAsync(IList<T> source, int pageIndex, int pageSize)
{
var count = source.Count;
var items = source.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
return new PaginatedList<T>(items, count, pageIndex, pageSize);
}
}

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.

/// <summary>
/// This class contains properites used for paging, sorting, grouping, filtering and will be used as a parameter model
///
/// SortOrder - enum of sorting orders
/// SortColumn - Name of the column on which sorting has to be done,
/// as for now sorting can be performed only on one column at a time.
///FilterParams - Filtering can be done on multiple columns and for one column multiple values can be selected
/// key :- will be column name, Value :- will be array list of multiple values
///GroupingColumns - It will contain column names in a sequence on which grouping has been applied
///PageNumber - Page Number to be displayed in UI, default to 1
///PageSize - Number of items per page, default to 25
/// </summary>
public class PaginatedInputModel
{
public IEnumerable<SortingUtility.SortingParams> SortingParams {set; get;}
public IEnumerable<FilterUtility.FilterParams> FilterParam { get; set; }
public IEnumerable<string> GroupingColumns { get; set; } = null;
int pageNumber = 1;
public int PageNumber { get { return pageNumber; } set { if (value > 1) pageNumber = value; } }
int pageSize = 25;
public int PageSize { get { return pageSize; } set { if (value > 1) pageSize = value; } }
}

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.

static readonly MemoryCache objCache = new MemoryCache(new MemoryCacheOptions());
public async Task<PaginatedList<SampleViewModel>> PagingMethod(PaginatedInputModel pagingParams)
{
List<SampleViewModel> sampleList = null;
#region [Caching]
if (objCache.Get("SampleCacheId") != null)
{
sampleList = (List<SampleViewModel>)objCache.Get("SampleCacheId");
}
else
{
sampleList = _context.SampleViewModel.FromSql("usp_AllJobs").ToList();
objCache.Set("SampleCacheId", sampleList, new MemoryCacheEntryOptions().SetAbsoluteExpiration(TimeSpan.FromMinutes(10)));
}
#endregion
#region [Filter]
if (pagingParams != null && pagingParams.FilterParam.Any())
{
sampleList = FilterUtility.Filter<SampleViewModel>.FilteredData(pagingParams.FilterParam, sampleList).ToList() ?? sampleList;
}
#endregion
#region [Sorting]
if (pagingParams != null && pagingParams.SortingParams.Count() > 0 && Enum.IsDefined(typeof(SortingUtility.SortOrders), pagingParams.SortingParams.Select(x => x.SortOrder)))
{
sampleList = SortingUtility.Sorting<SampleViewModel>.SortData(sampleList, pagingParams.SortingParams).ToList();
}
#endregion
#region [Grouping]
if (pagingParams != null && pagingParams.GroupingColumns != null && pagingParams.GroupingColumns.Count() > 0)
{
sampleList = SortingUtility.Sorting<SampleViewModel>.GroupingData(sampleList, pagingParams.GroupingColumns).ToList() ?? sampleList;
}
#endregion
#region [Paging]
return await PaginatedList<SampleViewModel>.CreateAsync(sampleList, pagingParams.PageNumber, pagingParams.PageSize);
#endregion
}

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.

5 Comments

Join the conversation! Your thoughts help the community grow.

chidambaram k2y ago
Grate work, but am getting some error, System.InvalidOperationException: Unknown enum type. at System.RuntimeType.IsEnumDefined(Object value) at Vehicle.Infrastructure.Repositories.GroupRepository.ListGroup(PaginatedInputModel pagingParams)
Elena Danisor3y ago
Why in pagination you are not using await ?
Sandeep Kumar Gupta5y ago
Yes sir I have passed in query string of .net core web api using swagger but didnt received the query value in PagingParams object in controller from [FromQuery] attribute please help sir
sedat akyol6y ago
Hi , can you share source code ?
Christian Ponzi6y ago
Great article, i try to pass Sorting and fileter parameters by iactionlink but it dosent work, what can i do? @Html.ActionLink("Denominazione", "Index", new { SortingParam = new SortingUtility.SortingParams{1,"Descrizione" }, FilterParam = ViewBag.FilterParam, GroupingColumns = ViewBag.GroupingColumns, pageNumber = 1 }) i tried in different ways.