Implementing Pagination with Dynamic Filtering and Sorting using Entity Framework

Implementing pagination with dynamic filtering and sorting using entity framework often results in long functions with complex checks to check and apply filters and sorting. In this article, we are going to implement these dynamic filtering and sorting along with pagination and see how we can achieve the same in less code and with more information. We are going to use a free, open-source nuget package Entity Framework Paginate to achieve our goal.

What is Entity Framework Paginate?

EF Paginate is a plug in for .NET applications that simplifies the dynamic sorting and filtering by storing your filter and order by expressions along with the execution condition. Based on these details, it determines which filtering and sorting to apply to the base query and returns a Page<T> object with all the information you need for paginated data.

There are 3 main classes you need to know:

1. Page<T> class
2. Filters<T> class
3. Sorts<T> class

Page Class

EF Paginate provides a Page class which holds all the required information of your paginated data. Let’s say we want to paginate the data for an Employee entity. What EF Paginate will provide us is an object of class...

Page<Employee>

...which will contain the following properties:

• CurrentPage: An integer variable that will store the current page number for which all the data is being returned.
• PageCount: An integer variable which stores the total number of pages available for the query.
• PageSize: Stores the integer value of the number of records that will be displayed in a page.
• RecordCount: Total number of records that are available for the query.
• Results: In this case, IEnumerable<Employee> will be returned which will hold the actual result set which we need to display.

Filters Class

The filters class exposes an Add method which accepts two parameters, condition and an expression. Condition is a boolean value which decides if the given expression needs to be executed or not. To implement a dynamic filter, we need to initialize a filters object and then add multiple conditions and their corresponding expression.

var filters = new Filters<Employee>();
filters.Add(!string.IsNullOrEmpty(searchText), x => x.LoginID.Contains(searchText));
filters.Add(!string.IsNullOrEmpty(jobTitle), x => x.JobTitle.Equals(jobTitle));

Sorts Class

Similar to the filters class, the sorts class also exposes an Add method with an additional parameter byDescending. The byDescending parameter is false by default and true needs to be sent only in case you want to order the records in descending order.

var sorts = new Sorts<Employee>();
sorts.Add(sortBy == 1, x => x.BusinessEntityID);
sorts.Add(sortBy == 2, x => x.LoginID, true); //When this sort is applied,
                                              //data will be sorted by LoginID in descending order.
sorts.Add(sortBy == 3, x => x.JobTitle);

Now that we know about all the classes provided by EF Paginate, we need to know how to connect all the dots to get the desired result. To get the final paginated, filtered and sorted data, we have an extension method Paginate which works on IQueryable type. So, all you need to do is call this paginate extension method on your base query and pass the information like page number, page size and optionally the sorts and filters object.

context.Employees.Paginate(currentPage, pageSize, sorts, filters);

Let's take a look at a working function which implements all the features mentioned above. In our GetFilteredEmployees function, we pass the page size, current page, search text, sort by and job title as parameters. These can come from any component which is trying to consume this. In our case, it comes from front end where we have these options. The page size and current page determines which page needs to be returned and with how many records in it. Login ids need to be filter if any search text was passed, sort by is an integer value which decides which sorting is to be implemented and job title filters the employee table by the title provided.

public Page<Employee> GetFilteredEmployees
(int pageSize, int currentPage, string searchText, int sortBy, string jobTitle)
{
      Page<Employee> employees;
      var filters = new Filters<Employee>();
      var sorts = new Sorts<Employee>();

      filters.Add(!string.IsNullOrEmpty(searchText), x => x.LoginID.Contains(searchText));
      filters.Add(!string.IsNullOrEmpty(jobTitle), x => x.JobTitle.Equals(jobTitle));

      sorts.Add(sortBy == 1, x => x.BusinessEntityID);
      sorts.Add(sortBy == 2, x => x.LoginID);
      sorts.Add(sortBy == 3, x => x.JobTitle);

      using (var context = new AdventureWorksEntities())
      {
         employees = context.Employees.Paginate(currentPage, pageSize, sorts, filters);
      }

      return employees;
}

This function will return a Page<Employee> object with all the results and other metadata for the page.

Check the project source code with working example on GitHub.

The post Implementing pagination with dynamic filtering and sorting using Entity Framework appeared first on The Inquisitive Monk.

CodeProject