NetCore 5 Pagination 100,000+ Rows

Introduction

Have you ever come across a web application where it is slow to display a data table? The crawling performance is typical in any application that has a lot of data. The amount of data plays a big role in how fast a table will load. The root cause of a slow site is “over-fetching” data.

The cure for “over-fetching” is server-side pagination — a programming technique for which only a subset of data, such as 10 rows, will be provided by the backend for display in the frontend.

Pagination, also known as paging, is the process of dividing a document into discrete pages, either electronic pages or printed pages. — Wikipedia

I recently published Angular 11 Pagination of 100,000+ Rows here in medium.com in which I mentioned the Visual Studio Template for rapid prototype REST API at the end. In this story, I will go over the OnionAPI template to scaffold a NetCore 5 solution with advanced features such as crud, sort, filter, and page, and then add custom code to support the Angular Datatables frontend.

Tutorial Content

The tutorial has three parts:

Part 1: Scaffold a REST API project using Visual Studio Template OnionAPI. Create a Clean Architecture solution running on NetCore 5 and seed the database with 100,000 rows of mock data using Bogus library.

Part 2: Design the REST API interfaces to work with Angular-Datatables. Review the input/out parameters and design strong-type C# interfaces.

Part 3: Build a custom REST endpoint for use by Angular Datatables. Add/update controller, CQRS, etc.

At the end of the tutorial part, I include a complementary screencast so you can see how it was done on my desktop. Grab a drink and relax while watching the video!

Opensource Project Highlights

VSIXTemplateOnionAPI is a Visual Studio template for scaffolding a clean architecture ASP.NET REST API solution that consists of five projects.

Domain — Entities and the common models
Application — Interfaces, CQRS Features, Exceptions, Behaviors
Infrastructure.Persistence —Application-specific database access
Infrastructure.Shared — Common services such as Mail Service, Date Time Service, Mock, and so on
WebApi — Controllers for REST API resources and endpoints

The underline tech stack provides loosely-coupled and inverted-dependency architecture with good design patterns and practices. At the time of this writing, the OnionAPI template is at version 1.3 and can generate the REST API projects running on NetCore 5.

I created the OnionAPI template to refrain myself from DRY (Don’t repeat yourself). When starting a new project, I used to clone a working ASP.NET WebAPI project and changed the namespace, connection string, startup and deleted unused files. With the OnionAPI template, I specify a project name and it creates a clean project ready for Sprint 1. — Fuji Nguyen, Creator of OnionAPI Template

Prerequisites

The following tools/skills are recommended.

Visual Studio 2019 Community — free editor for ASP.NET Core WebAPI C#.
Familiarity with C# model classes and CQRS pattern.

Let’s get started!

Part 1: Scaffold a REST API project using Visual Studio Template OnionAPI.

The objective of Part 1 is to create a Clean Architecture solution and then seed the database with 100,000 rows of mock data.

By default, the OnionAPI template creates two REST resources:

Employees — This is a mock REST API resource where the data is dynamically generated by the Bogus library at run time. To learn more about Bogus, check the link in the References section later on.
Positions — This REST API resource gets its data from the actual database table Positions. This table is automatically seeded with 1,000 rows of mock data when the project first runs.

Task 1.1: Download OnionAPI Template

You can download the OnionAPI template from the Visual Studio Marketplace. See Figure 1 for more information.

Figure 1 — OnionAPI template in Visual Studio Marketplace

After download, click on the VSIXTemplateOnionAPI.vsix to install the extension. If you are new to Visual Studio Extension, visit Manage extensions for Visual Studio for installation instructions.

Task 1.2: Generate a New REST API Project and seed database with 100,000 rows of position

The easiest way to create a new project is to start from a project template for a particular type of application or website. A project template consists of a basic set of pre-generated code files, config files, assets, and settings.

When you first open Visual Studio, the start window appears, and from there, you can choose to create a new project. See Figure 2 for more information.

Figure 2 — Option to create a new project in Visual Studio

If the Visual Studio development environment is already open, you can create a new project by choosing File > New > Project on the menu bar or by clicking the New Project button on the toolbar. See Figure 3 for more information.

Figure 3 — Shortcut to create a new project via the File menu

Step 1: Select a Template Type

On the Create a new project page, a list of your recently selected templates appears on the left. The templates are sorted by most recently used.

If you’re not selecting from the recently used templates, you can filter all available project templates by entering text into the search box to further filter the templates; for example, enter in “OnionAPI”. See Figure 4 for more information.

Figure 4 — Filter project template in Visual Studio

Step 2: Create a Solution

On the Create a new project screen, search for and select the OnionAPI template and click on the Next button.

Figure 5 — Select OnionAPI template to scaffold a new project

Enter the project name as AngularNgxDataTableBackend and location C:\apps\devkit\ApiResources then click on the Create button. See Figure 6 for visual aids. For folder naming convention, I recommend following the CAT Pattern.

Figure 6 — Entering parameters to create a new project

Step 3: Set position seed value 100,000

Open ApplicationDbContext.cs from Infrastructure.Persistence > Contexts folder and change the SeedPositions from 1,000 to 100,000. See Figure 7 for visual aids.

Figure 7 — Line of code in ApplicationDbContext.cs to change the seed value

Step 4: Run project

Hit F5 to run. Depending on the speed of your computer, it may take a minute or two to seed 100,000 position records. Then the swagger will display in the browser as shown in Figure 8.

Figure 8 — Swagger display after seeding the data

Open and run the Positions > Get endpoint without entering any parameter. You should see the record total of 100,000 as shown in Figure 9.

Figure 9 — Confirmation of the 100,000 position rows

Screencast of Part 1

The complementary Youtube video is a record of the steps in this part of the tutorial. If you find the video helpful, please like and subscribe.

Part 2: Design the REST API interfaces to work with Angular-Datatables

Architecture-wise, the ASP.NET Core REST API application consists of a group of Middleware that’s assembled into an app pipeline to handle requests and responses. In Part 2, we will design the REST API request and response parameters and build strong-type C# interfaces to support Angular Datatables.

As shown in Figure 10, when using server-side processing, DataTables will make an Ajax request to the server for each draw of information on the page (i.e. when paging, ordering, searching, etc.). DataTables will send a number of variables to the server to instruct it to perform the required processing and then return the data in JSON format to DataTables for display.

Sent Parameters — When making a request to the server using server-side processing, DataTables will send the input parameters such as columns, draw, length, order, and search, to let the server know what data to query.

Returned Data — DataTables expect a return of data in JSON format, including fields such as draw, recordTotal, recordFiltered, data, and error.

Figure 10 — Interfaces between Clients and Api Resource to support server-side pagination

For more information about Clients-ApiResources-TokenService architecture for building scalable and maintainable modern applications, see CAT architecture pattern for modern app SPA/Mobile.

Task 2.1: Create C# model classes for Sent Parameters

The Angular Datatables project has an online demo of the server-side pagination. Using Chrome > F12 > Network > Header, we can inspect the Request Payload JSON as shown in Figure 11.

Figure 11 — Angular Datatables > Request Payload

The JSON’s structure is mapped to C# model classes as followed

JSON columns structure is mapped to C# Column model class as shown in Figure 12
JSON order structure is mapped to C# Order model class as shown in Figure 13
JSON search structure is mapped to C# Search model class as shown in Figure 14

Code walk-thru in Figure 12

line 5–9: properties mapping to Angular Datatables > Column

Code walk-thru in Figure 13

line 5–6: properties mapping to Angular Datatables > Order

Code walk-thru in Figure 14

line 5–6: properties mapping to Angular Datatables > Search

Following the Clean Architecture concept, the C# model classes for parameters are organized in the Application > Parameters folder. See Figure 15 for the file location in the project.

Figure 15 — Source code location for parameter model classes

Tip: To convert JSON into gorgeous, typesafe C# code without manually typing the model classes, try quicktype.io. For this story, I obtained the JSON Sent Parameters by clicking view source (see Figure 12) and then cut & paste JSON into the online converter to get the source code for C# model classes. See Figure 16 for visual aids.

Figure 16 — Online JSON to C# conversion helper

Task 2.2: Create C# model classes for the Returned Data

The Angular Datatables project has an online demo of the server-side pagination. Using Chrome > F12 > Network > Preview, we can inspect the Returned Payload JSON as shown in Figure 17.

Figure 17 — Angular Datatables Returned Payload JSON

The JSON Returned Payload is mapped to C# model class PagedDataTableResponse as shown in Figure 18.

Code walk-thru in Figure 18

line 7–9: properties to support pagination as required by Angular Datatables
line 13–16: set the values required by Angular Datatables

Following the Clean Architecture, the PagedDataTableResponse C# model class is organized in the Application > Wrappers folder. See Figure 19 for the file location in the project.

Figure 19 — Location of wrapper models in the Application project

Screencast of Part 2

The complementary Youtube video is a record of the steps in this part of the tutorial. As a bonus, I highlighted the importance of interface design and tips to build strong-type C# model classes. If you find the video informative, please leave a like and subscribe.

Part 3: Build a custom REST endpoint for use by Angular Datatables.

Command and Query Responsibility Segregation (CQRS) is a pattern used to separate the logic between commands and queries. In Part 3, we will add a custom query handler to support Angular Datatables server-side paging.

Task 3.1: Update PositionsController.cs in WebAPI project

In the WebAPI project, update the PositionsController.cs inside folder Controllers to add a REST endpoint, called “Paged”, to support Angular Datatables query (see Figure 20).

Code walk-thru in Figure 20

line 73-76: add the Paged endpoint

Task 3.2: Add handler PagedPositionsQuery.cs in the Application project

In the Application project, add a query handler PagedPositionsQuery.cs inside folder Features\Positions\Queries\GetPositions to process Angular Datatables request and response (see Figure 21).

Code walk-thru in Figure 21

line 17–22: strong type input parameters mapping to Angular Datatables Sent Parameters
line 43: map Sent Parameters > Draw to OnionAPI > PageNumber
line 45 : map Sent Parameters > Length to OnionAPI >PageSize
line 49–62 : map Sent Parameters > Order to OnionAPI > OrderBy
line 65–70: map Sent Parameters > Search term to OnionAPI > Position searchable fields

Task 3.3: Test-Drive new Paged endpoint

In Visual Studio, hit F5 to run the project. In the Swagger, navigate to the /api/v1/Positions/Paged endpoint and cut and paste the sample JSON in Figure 22 into the Request body and click on Execute. You should see the Response body with JSON data. See Figure 23 for visual aids.

Figure 23 — Swagger UI to run Paged endpoint

Screencast of Part 3

The complementary Youtube video is a record of the steps in this part of the tutorial. As a bonus, I explained the use of the MediatR library to achieve “thin” controller and CQRS. If you find the video helpful, please like and subscribe.

Github Repos

Feel free to download the source codes on Github.

Angular Front-End https://github.com/workcontrolgit/AngularNgxDataTable
ASP.NET REST API Backend (this story) https://github.com/workcontrolgit/AngularNgxDataTableBackend

References

Tutorial Demo

Developers love to see working source code. We all like to experiment with how something works, and then decide whether we want to spend our valuable time learning it. Check out the live demo.

Summary

The OnionAPI provides boilerplate code to support advanced REST API features such as CRUD, filtering, sorting, shaping, and paging. These features are written as the base classes so you can inherit and extend them to meet your project-specific requirements such as Angular Datatables server-side paging.

Let’s peel the onion!