ASP.Net Core Server-Side Filtering & Pagination with EF Core
In this article, we will go through the sample code to understand the implementation of server-side pagination along with data filter.
Let’s walkthrough step by step.
1) Define the Request Model
Define a model which includes the input properties, for example in order to perform pagination client must pass the Page No, Page Size, Filter criteria’s if any, right !
Refer the diagram for sample interface for the Request model.
2) Define the Response Model
Now lets define a model which includes the response properties, lets say we have queried the database and have to compose the final response, what are the data we send back to the client.
Refer the diagram of the sample interface for the response model. If you notice, Here we have inherited request model interface, so the response can include whatever the input values that we received from the client. Its up to you to decide whether you need those properties, otherwise the props from Response interface is sufficient.
3) Implement the Helper Function
As a third step, we will implement the common helper function which will assist us preparing response object, We will discuss about this implementation in detail at the end of the article.
4) Query implementation
As per your project structure this query implementation may go inside the Controller/Service class. We can compose our query using LINQ query/methods both are lazy and should return as IQueryable<Type>.
a. Assign first get the table as queryable in to a variable
b. Loop through all the filter/search criteria’s that was passed through the request and update the query with the where class by including each search term
LINQ to Sql queries are lazy by default, so the above query will be in memory and not executed until we access the records. In other words, until we iterate through the records (or) bind them in the view the actual sql query will not get executed. Hence as of step 2 we just created the desired query, it is in memory and ready for execution.
c. Now the execute the query by calling our helper function
At this point our helper function will execute two different queries in the database. First query is to fetch the record counts for the given query and the next query would be the data fetch for the requested page.
As you see below, the Execute function inside PagingHelper, runs two queries in to database, one is to fetch the available record count for the applied search criteria and another one is to fetch the records for requested page.
NOTE: In the helper function I have referred the query parameter as IQueryable<dynamic> it is better to use Generic instead of dynamic, Same applies for ArrayList items which could be IEnemurable<T>. This doesn’t matter for the scope of this article, as long as you are able to understand the implementation of filtering & pagination in EF Core.
