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:
Page<T>
class Filters<T>
class 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);
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