Post Cover

Implementing Pagination in ASP.NET Core Web API and Entity Framework Core

ASP.NET Core Posted Jun 30, 2021

Pagination is one of the application performance optimization techniques employed while designing systems where the client-server interaction involves huge load of datasets. It helps in creating data interactions in the form of partial data sets or pages, so that the client is not overworked while processing the huge set of data thrown at it and the server is not overworked while processing the huge dataset to be passed down to the client.

The interaction happens over a common understanding over the number of records the client expects from the server and the server relays how many pages the client can expect from the server for loading the complete dataset. The page size is kept at optimal so that the server can process quickly and the client can receive it without stressing the channel.

"Pagination is more of a design choice than a functionality"

In this article, let's look at how we can implement an API that employs pagination in a simple way using ASP.NET Core and Entity Framework Core for the database interaction.

To demonstrate how we can build a simple pagination over our WebAPI, let's begin by designing one. In this example, we're given a Blog API which has a single GET call that returns all the Posts available in the database. Now since this is a Blog, we can expect that the data can grow in size over the time leading to possible performance issues. To avoid this, we're going to design our GET API so that it sends out paginated data.

The API receives two inputs from the client - the pageNumber and the pageSize. The pageSize represents the number of records the client expects per page and the pageNumber is the current page going by the size of the records each page consists of.

We'll build this in a layered architecture, so our business logic lies in the "Core" layer and the APIs reside in the "API" layer. The below commands let us create two projects for API and Core layers and then add them both to a Solution file.

dotnet new webapi --name MyBlog.Api

dotnet new classlib --name MyBlog.Core

dotnet new sln --name MyBlog

dotnet sln MyBlog add ./MyBlog.Api/MyBlog.Api.csproj

dotnet sln MyBlog add ./MyBlog.Core/MyBlog.Core.csproj

Once we're done with the project setup, let's install the necessary libraries for the database interaction. In this example, we're going to use SQLite as our database so we'd install packages accordingly. We'd use Entity Framework Core as the mapping layer between SQLite db and our project.

> sqlite3
## create new db file in the API project directory
sqlite> .open app.db

Post installing libraries, the API csproj file looks like below:

#Api.csproj#
<Project Sdk="Microsoft.NET.Sdk.Web">
  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.7">
      <PrivateAssets>all</PrivateAssets>
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
    </PackageReference>
    <PackageReference Include="Swashbuckle.AspNetCore" Version="5.6.3" />
  </ItemGroup>
  <ItemGroup>
    <ProjectReference Include="..\MyBlog.Core\MyBlog.Core.csproj" />
  </ItemGroup>
</Project>
#Core.csproj#
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>net5.0</TargetFramework>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="5.0.7" />
  </ItemGroup>
</Project>

We'd be working with Posts table from the database, so we'd have our entity class Post as below:

namespace MyBlog.Core.Entities
{
    public class Post
    {
        [Key]
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public string Permalink { get; set; }
        public DateTime CreatedOn { get; set; }
    }
}

The database context class that maps this entity to the database, looks as below:

namespace MyBlog.Core.Entities
{
    public class MyBlogContext : DbContext
    {
        public MyBlogContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<Post> Posts { get; set; }
    }
}

Finally, the context is registered in the Startup class as below:

services.AddDbContext<MyBlogContext>(options =>
{
    options.UseSqlite("Data Source=app.db");
});

Designing the Solution - API Layer:

As mentioned before, the client would pass the pageNumber and the pageSize to the API and the API would act accordingly. For this we design the API method as below:

namespace MyBlog.Api.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class PostsController : ControllerBase
    {
        [HttpGet]
        public PaginatedPost Get([FromQuery] QueryParams qp)
        {
            // fetch posts and return
        }
    }
}

The QueryParams type is binded from the query parameters by decorating with a FromQuery decorator. The class looks like below:

namespace MyBlog.Core 
{
  public class QueryParams
  {
      private const int _maxPageSize = 50;

      private int _page = 10;

      public int Page { get; set; } = 1;

      public int PostsPerPage
      {
        get
        {
            return _page;
        }
        set
        {
            if (value > _maxPageSize) _page = _maxPageSize;
            else _page = value;
        }
      }
  }
}

This class manages the validation logic of the pageSize being passed over to the API. The validation here ensures that the client doesn't send too big a number on the pageSize for the API to handle.

The controller on receiving this data passes it over to the Repository layer which contains the business logic for querying the database and paginating the result.

Business Layer:

The PostsRepository class implements the IPostRepository interface and has a single method GetPosts() which receives the pageNumber and the pageSize and returns a PaginatedPost object, that contains the data as well as the meta information for the client.

namespace MyBlog.Core.Repositories
{
    public interface IPostsRepository
    {
        PaginatedPost GetPosts(int page = 1, int postsPerPage = 10);
    }

    public class PostsRepository : IPostsRepository
    {
        private readonly MyBlogContext _context;

        public PostsRepository(MyBlogContext context)
        {
            _context = context;
        }

        public PaginatedPost GetPosts(int page = 1, int postsPerPage = 10)
        {
            var results = _context.Posts.OrderBy(x => x.Id);
            return PaginatedPost.ToPaginatedPost(results, page, postsPerPage);
        }
    }
}

In the above class implementation, the Repository calls on the database for the posts list and then passes it to the static method ToPaginatedPost() inside the PaginatedPost class where the paging happens.

The PaginatedPost class looks like below:

namespace MyBlog.Core
{
    public class PaginatedPost
    {
        public PaginatedPost(IEnumerable<Post> items, int count, int pageNumber, int postsPerPage)
        {
            Metadata = new Metadata
            {
                CurrentPage = pageNumber,
                PostsPerPage = postsPerPage,
                TotalPages = (int)Math.Ceiling(count / (double)postsPerPage),
                TotalPosts = count
            };
            this.Posts = items;
        }

        public Metadata Metadata { get; set; }

        public IEnumerable<Post> Posts { get; set; }

        public static PaginatedPost ToPaginatedPost(IQueryable<Post> posts, int pageNumber, int postsPerPage)
        {
            var count = posts.Count();
            var chunk = posts.Skip((pageNumber - 1) * postsPerPage).Take(postsPerPage);
            return new PaginatedPost(chunk, count, pageNumber, postsPerPage);
        }
    }

    public class Metadata
    {
        public int TotalPages { get; set; }
        public int CurrentPage { get; set; }
        public int PostsPerPage { get; set; }
        public int TotalPosts { get; set; }
    }
}

The ToPaginatedPost() method receives the Posts collection and then chunks it using the Skip().Take() combination. These are built-in extension methods provided by LINQ. Once the chunk is made, it is passed to the constructor of the PaginatedPost instance where the meta information is constructed.

public PaginatedPost(IEnumerable<Post> items, int count, int pageNumber, int postsPerPage)
{
    Metadata = new Metadata
    {
        CurrentPage = pageNumber,
        PostsPerPage = postsPerPage,
        TotalPages = (int)Math.Ceiling(count / (double)postsPerPage),
        TotalPosts = count
    };

    this.Posts = items;
}

The TotalPages is calculated based on the postsPerPage the client expects and the total records that are available in the database for the given entity. For example, if the database contains 1000 posts and the client requests 24 posts per page, the total pages formed are 1000/24 which closes to 42 (we're using ceil) with 41 pages having 24 records each and the last page having 16 records.

The Metadata and Posts properties are available in the PaginatedPost class and these are filled and the instance is sent out to the caller.

The PostsController invokes this via a DataService class which contains the PostsRepository instance.

namespace MyBlog.Core.Services
{
    public interface IDataService
    {
        IPostsRepository Posts { get; }
    }

    public class DataService : IDataService
    {
        private readonly MyBlogContext _context;

        public DataService(MyBlogContext context)
        {
            _context = context;
        }

        public IPostsRepository Posts => new PostsRepository(_context);
    }
}

*The DataService implements the IDataService and is registered as a Scoped Service inside the dotnet core DI.

The complete controller class looks like below after all the pieces are joined.

namespace MyBlog.Api.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class PostsController : ControllerBase
    {
        private readonly IDataService _db;

        public PostsController(IDataService db)
        {
            _db = db;
        }

        [HttpGet]
        public PaginatedPost Get([FromQuery] QueryParams qp)
        {
            return _db.Posts.GetPosts(qp.Page, qp.PostsPerPage);
        }
    }
}

When we run the project with API as the startup project, we see that in the Swagger we have two parameters the endpoint expects "page" and "postsPerPage". When we pass in the parameters, the API fetches the records according to the requested pageSize and returns the chunk.

Performance Analysis:

When we look at the query, we're passing in the posts collection along with an OrderBy clause (context.Posts.OrderBy(x => x.Id)) but doesn't that mean we're calling up the entire dataset from the table? It doesn't, because we're passing an IQueryable to the PaginatedPost where we're adding more filters to the dataset (Skip() and Take()) the underlying query which is executed over the database looks like below:

Executed DbCommand (1ms) [Parameters=[@__p_1='?', @__p_0='?'], CommandType='Text', CommandTimeout='30']
SELECT "p"."Id", "p"."Content", "p"."CreatedOn", "p"."Permalink", "p"."Title"
FROM "Posts" AS "p"
ORDER BY "p"."Id"
LIMIT @__p_1 OFFSET @__p_0

This is lot better and faster than calling out all the records and filtering on them. This approach we're following is called "Lazy-Evaluation" which happens due to the presence of the IQueryable.

A sample execution of the API as below would result in:

curl -X GET "https://localhost:5001/api/Posts?Page=1&PostsPerPage=5" -H  "accept: text/plain"

Response:
{
  "metadata": {
    "totalPages": 200,
    "currentPage": 1,
    "postsPerPage": 5,
    "totalPosts": 1000
  },
  "posts": [
    {
      "id": 1001,
      "title": "This is your sample Post on Paginating DotnetCoreAPI 1001",
      "content": "This is some random content you might always want to ignore. Look at the other stuffs first. I'm just a substitute here",
      "permalink": "This-is-your-sample-Post-on-Paginating-DotnetCoreAPI-1001",
      "createdOn": "2021-06-30T11:34:17.6321408"
    },
    {
      "id": 1002,
      "title": "This is your sample Post on Paginating DotnetCoreAPI 1002",
      "content": "This is some random content you might always want to ignore. Look at the other stuffs first. I'm just a substitute here",
      "permalink": "This-is-your-sample-Post-on-Paginating-DotnetCoreAPI-1002",
      "createdOn": "2021-06-30T11:34:17.8733694"
    },
    {
      "id": 1003,
      "title": "This is your sample Post on Paginating DotnetCoreAPI 1003",
      "content": "This is some random content you might always want to ignore. Look at the other stuffs first. I'm just a substitute here",
      "permalink": "This-is-your-sample-Post-on-Paginating-DotnetCoreAPI-1003",
      "createdOn": "2021-06-30T11:34:17.8766236"
    },
    {
      "id": 1004,
      "title": "This is your sample Post on Paginating DotnetCoreAPI 1004",
      "content": "This is some random content you might always want to ignore. Look at the other stuffs first. I'm just a substitute here",
      "permalink": "This-is-your-sample-Post-on-Paginating-DotnetCoreAPI-1004",
      "createdOn": "2021-06-30T11:34:17.8781989"
    },
    {
      "id": 1005,
      "title": "This is your sample Post on Paginating DotnetCoreAPI 1005",
      "content": "This is some random content you might always want to ignore. Look at the other stuffs first. I'm just a substitute here",
      "permalink": "This-is-your-sample-Post-on-Paginating-DotnetCoreAPI-1005",
      "createdOn": "2021-06-30T11:34:17.880343"
    }
  ]
}

Final Thoughts:

Pagination can be a huge performance boost when implemented properly. It helps keep the back-end lighter and also results in a more intuitive and great user experience. ASP.NET Core together with EF Core can make pagination easy with the LINQ extension methods at the disposal. Like mentioned before, Pagination is more of a design choice than a functional implementation. So it varies from application to application.

The complete project is available at https://github.com/referbruv/dotnetcore-pagination-example

Author-Image

Ram

I'm a full-stack developer and a software enthusiast who likes to play around with cloud and tech stack out of curiosity.

You can now show your support. 😊

We use cookies to provide you with a great user experience, analyze traffic and serve targeted promotions.   Learn More   Accept