This is the seventh part of Building ASP.Net Web API RESTful Service Series. The topics we’ll cover are:
- Building the Database Model using Entity Framework Code First – Part 1.
- Applying the Repository Pattern for the Data Access Layer – Part 2.
- Getting started with ASP.Net Web API – Part 3.
- Implement Model Factory, Dependency Injection and Configuring Formatters – Part 4.
- Implement HTTP actions POST, PUT, and DELETE In Web API – Part 5.
- Implement Resources Association – Part 6.
- Implement Resources Pagination – Part 7 (This Post).
- Securing Web API – Part 8.
- Preparing Web API for Versioning – Part 9.
- Different techniques to Implement Versioning – Part 10.
- Caching resources using CacheCow and ETag – Part 11.
Update (2014-March-5) Two new posts which cover ASP.Net Web API 2 new features:
Implement Resources Pagination
In this post we’ll discuss the different ways to implement results pagination, we’ll implement manual pagination then format the response in two different ways (having pagination meta-data in an envelope, and in pagination header).
It is well known that overwhelming your server with a query which returns hundreds of thousand of records is a bad thing, when we are designing an API, we should consider returning the results of our GET methods in paginated way, i.e. providing 10 results on each request, and giving the API consumer the ability to navigate through results, specify the size of the page, and which page he wants.
Manual Pagination and Envelopes
We’ll modify the “CoursesController” to use pagination instead of returning the whole courses at once.
Let’s see the code below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
public Object Get(int page = 0, int pageSize = 10) { IQueryable<Course> query; query = TheRepository.GetAllCourses().OrderBy(c => c.CourseSubject.Id); var totalCount = query.Count(); var totalPages = (int)Math.Ceiling((double)totalCount / pageSize); var urlHelper = new UrlHelper(Request); var prevLink = page > 0 ? urlHelper.Link("Courses", new { page = page - 1 }) : ""; var nextLink = page < totalPages - 1 ? urlHelper.Link("Courses", new { page = page + 1 }) : ""; var results = query .Skip(pageSize * page) .Take(pageSize) .ToList() .Select(s => TheModelFactory.Create(s)); return new { TotalCount = totalCount, TotalPages = totalPages, PrevPageLink = prevLink, NextPageLink = nextLink, Results = results }; } |
What we’ve done here is simple, we’ve introduced the below to “CoursesController”
- Added two new optional parameters to the GET method with default values, those optional parameters are translated to query string values, i.e. if we want to request the second page of courses our GET request will be on the form: http://localhost:{your_port}/api/courses/?page=1 Notice we didn’t specify the pageSize parameter and it took the default values 10. Sample of response will be on the form below:
1 2 3 4 5 6 7 8 9 |
{ "totalCount": 33, "totalPages": 4, "prevPageLink": "http://localhost:8323/api/courses?page=0&pageSize=10", "nextPageLink": "http://localhost:8323/api/courses?page=2&pageSize=10", "results": [ /* Array containts the results*/ ] } |
- The method “GetAllCourses” in our Repository returns “IQueryable” response, which is perfect because till this moment the query is represented in memory and didn’t execute against SQL server, so paging and order by for query are executing correctly.
- We’re using envelope to wrap our response, this envelope contains pagination meta-data inside the JSON response such as: totalCount, totalPages, prevPageLink, nextPageLink. It is important to return the total records count and total pages so API consumer will be able to bind results and apply pagination on grid easily.
Returning the pagination meta-data in the response body is a common technique, there is nothing wrong about it as everything is visible for the developer, the draw back of this approach is that API consumer will dig into the response to extract data he was originally asking for and maybe ignoring all the pagination meta data we returned if he do not need to use it. So the other cleaner way to return pagination meta-data is to include them in response header, so we’ll keep the response body for the results only and we’ll add new header called “X-Pagination” which contains all pagination meta-data.
Manual Pagination and Pagination Headers
We’ll modify “StudentsController” to use headers to return pagination meta-data, in this approach API consumer can use this header if he is interested in the pagination meta-data, other wise he can just ignore this header and read the results of the query directly from response body.
Applying this is fairly simple, the pagination technique we used in “CoursesControrler” will be used the same here, except for returning the pagination meta-data in new header. Take a look on the code below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
public IEnumerable<StudentBaseModel> Get(int page = 0, int pageSize = 10) { IQueryable<Student> query; query = TheRepository.GetAllStudentsWithEnrollments().OrderBy(c => c.LastName); var totalCount = query.Count(); var totalPages = (int)Math.Ceiling((double)totalCount / pageSize); var urlHelper = new UrlHelper(Request); var prevLink = page > 0 ? urlHelper.Link("Students", new { page = page - 1, pageSize = pageSize }) : ""; var nextLink = page < totalPages - 1 ? urlHelper.Link("Students", new { page = page + 1, pageSize = pageSize }) : ""; var paginationHeader = new { TotalCount = totalCount, TotalPages = totalPages, PrevPageLink = prevLink, NextPageLink = nextLink }; System.Web.HttpContext.Current.Response.Headers.Add("X-Pagination", Newtonsoft.Json.JsonConvert.SerializeObject(paginationHeader)); var results = query .Skip(pageSize * page) .Take(pageSize) .ToList() .Select(s => TheModelFactory.CreateSummary(s)); return results; } |
Notice how we added a new header to the response collection headers which contains a serialized JSON object containing all pagination meta-data.
In the next post we’ll talk briefly about web API security and how we can implement Basic authentication.
Reblogged this on Sutoprise Avenue, A SutoCom Source.
Instead of creating your own pagination code you might want to consider using the existing WebAPI OData package instead.
Hello Martin,
You are absolutely right, but I wanted to dominstrste pagination manually, I might blog about oData support for web API.
Thakns I learn a lot from you
thanks Taiseer for this … I was facing a terrible code and found the solution here
Your welcome 😉 glad it solved your issue!
Taiseer you have really been of immense help to me by this wonderful tutorial. Thanks so much!
Thanks Chris, it is always pleasure to hear nice comments from blog readers 🙂
Hello Teiseer, How would you proceed in order to move pagination more to a database level ? I am new in Web API, but as I rememeber from classic ASP.NET, it was always recommended to send a paginated query (e.g. using rownum for Oracle, using keword “limit” for other databases) – it supposed to be faster and less resources consuming.
What is the best way of pagination in web Api when taking performance into consideration ?
Hi Michael, interface allows you to prepare in memory query and this query do not get executed against database until you call .ToList() or .ToArray(), etc… You can run SQL profiler and watch how the query is executed or you can use .ToString() and watch the TSQL that will be generated against the database.
The current pagination is done on the database level not on the returned data. In EF The IQueryable
Hope this answers your question.
A quick question..
If I need to add additional criterias (like search by name, keywords, etc…) and also add the Sort parameters (key and direction).. What would be the best approach to handle theses parameters from the Restful service?
Thanks.
Hi Stan,
As long you are doing search (Getting data from the server) then you need to pass those fields as part of the query string. There is lot of examples on SO which shows you how to do sorting as wel..
Hi Taiseer,
Thanks.. I’ve seen some examples. and yes, they pass the fields as part of the query string.
My additional question, read as a best practice, they say it’s to give the ability to choose returned fields.
How this can be done knowing you define each Entity ?
Well what are you looking for can be implemented using OData, check my other tutorial which shows how you can select specific columns.
Awesome. Exactly what I needed.
Happy to help, just simple note here, do not prefix your custom header with (X) it is not good practice anymore, you can prefix them with your company name.
Taiseer, would the GET method you described above and the pagination headers work with jquery DataTables? It seems they have a very specific return format. If I understand correctly WebApi returns generic objects and the format is left to content negotiation?
Hi, I do not know how Jquery DataTable pagination works, but this way is standard way to do pagination, so I guess it should work.
You need create a wrapper mapper in JS to transfer your model back to whatever JS(JQuery) required format.
hmm, I prefer build a generic type class like:
public class PagedList : List, IPagedList,,
This implementation couple the code too much, for each entity you have to specific the paging logic again and again.
the interface will looks like the following:
public interface IPagedList : IList
{
int PageIndex { get; }
int PageSize { get; }
int TotalCount { get; }
int TotalPages { get; }
bool HasPreviousPage { get; }
bool HasNextPage { get; }
//I personal don’t like the previous/next page implementation, that’s the UI logic, it better handle in the front end(either //javascript or .Net view, the front end need to specify each index page’s logic anyway and build the front end paging by using PageIndex, PageSize, TotalCount) that’s just my personal preference.
}
Thanks for your message, the next/previous link is part of the HATEOAS specifications, but your suggestion is valid too and there is many ways to implement pagination, not only the ones I’ve mentioned.
when I changed the Get method in the CoursesController from public IEnumerable Get() to
public Object Get(int page = 0, int pageSize = 10). I got the error below. How do I get around the error?
An error has occurred.The ‘ObjectContent`1’ type failed to serialize the response body for content type ‘application/xml; charset=utf-8’.System.InvalidOperationException
Thanks Taiseer for your great efforts
Please , what is the benefit of domain models ? because every time deal with DAL direct
IQueryable query;
query = TheRepository.GetAllStudentsWithEnrollments().OrderBy(c => c.LastName);
thanks
Hi Haitham,
It is good practice to distinguish your domain business models from your lower database ORM classes, I do not want to expose the EF classes as a response to API consumers, I want to have my own POCO classes (Models, DTOs) so I can shape the response as I want. You can use AutoMapper for this task too, but I prefer to do it manually as it gives great flexibility.
Hope this answers your question.
Thanks ! But where do I put the decoration [Required] if I need a field required
Check how I used this attribute here
Ok, but I would like to use it in this project. Tested it in the entity model class. Perhaps this project is not design to permit [Required] attribute on model class?
Thank you a lot Taiseer for your great effot , im new in the web api , so i need an advice from an expert.
my problematic is that i want to develope an api that can retrieve data from a distant oracle database ( in json format )
how can i proceed ? for a 1st step.
i have already an mvc webservice developed in c# that manage my back-office site and the database , what i want to do : it’s an api wich will play the role of a bridge beetween a distant oracle database and my webservice.
thanks.
Hello Armind,
You need to follow everything here except when you want to use your Oracle ORM or data provider to access your oracle tables and do your queries, everything else will work the same, you can get your data from oracle source, put them in a model (POCO class) and send them as a response and Web API formatter will serialise those to JSON response.
hi, actually i have a problem, i cant to get the url path, i’m using .net core, the error is specify in UrlHelper
Hi Jordy, well that’s tutorial is for ASP.NET 4, I do not think it will help in case of asp.net core
hi,
please help me
how can i get prePageLink in Odata web api?
Uri prePageLink = GetPrePageLink(Request.RequestUri,Request.GetQueryNameValuePairs(),5); ????
ODataQuerySettings settingsQueryOptions = new ODataQuerySettings() { PageSize = 5 };
var CityListQuery = DIManager.Get().GetCityList();
IQueryable appliedOdataToCityListQuery = (IQueryable)queryOptionsCity.ApplyTo(CityListQuery, settingsQueryOptions);
return new ResultDto(
appliedOdataToCityListQuery.ToList(),
Request.GetNextPageLink(),
prePageLink
);