Implementing pagination with dynamic filtering and sorting using Entity Framework

5
6021
views

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 plugin 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 initialise 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);

Lets 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 needs 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 result and other metadata for the page.

Read this article to implement sorting multiple columns.

EFP-Example

github-logo Check the project source code with working example on GitHub.