Introduction

When developing APIs, retrieving data efficiently is crucial for performance and scalability. Two common approaches are:

1. Simple GET Method: Fetches all records at once.
2. Paginated GET Method: Retrieves data in smaller chunks, improving performance and usability.

This blog will explore the differences, advantages, disadvantages, and implementation of pagination in APIs.

What is Pagination in APIs?

Pagination is a technique that divides large datasets into smaller sections (pages) to optimize data retrieval and reduce server load. Instead of fetching all records at once, the API returns a limited number of records per request.

Key Components of Pagination

• Page Number: Specifies which page of data to retrieve.
• Page Size: Defines how many records each page contains.
• Total Count: The total number of records available.

Implementing Efficient Pagination in ASP.NET Core APIs

In modern web applications, handling large datasets efficiently is crucial for maintaining performance and providing a smooth user experience. This blog post explores two common approaches to retrieving data in ASP.NET Core APIs: pagination and the simple GetAll method. I’ll explain their implementation, compare their performance characteristics, and provide recommendations for different scenarios.

The Problem with GetAll

Let’s start by examining a typical GetAll implementation that retrieves all records at once:

public dynamic GetAllItems()
{
    List<ItemModel> items = new List<ItemModel>();
    try
    {
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            DynamicParameters parameters = new DynamicParameters();
            items = con.Query<ItemModel>("GetAllItems", parameters, 
                    commandType: CommandType.StoredProcedure).ToList();
        }
    }
    catch (Exception ex)
    {
        // Log exception
        ExceptionLog obj = new ExceptionLog(ConnectionString);
        obj.Save_ExceptionLog(ex); 
    }
    return items;
}

While this approach is straightforward and works well for small datasets, it comes with several significant drawbacks:

1. Memory Consumption: Loading all records into memory can consume substantial resources, especially as your dataset grows.
2. Network Bandwidth: Transferring large amounts of data over the network increases response time and bandwidth usage.
3. User Experience: Users rarely need to see all records at once, making it inefficient to send data they may never view.
4. Database Load: Retrieving all records places unnecessary load on your database server.
5. Response Time: Larger result sets take longer to process, increasing API response times.

The Pagination Solution

Pagination addresses these issues by splitting data into manageable chunks or “pages”. Here’s how to implement a robust pagination system:

Step 1: Create a PagedResult Model

First, define a generic container for your paginated results:

public class PagedResult<T>
{
    public List<T> Results { get; set; }
    public int PageNumber { get; set; }
    public int PageSize { get; set; }
    public int TotalCount { get; set; }
}

This model includes not just the results themselves, but metadata about the pagination state that clients can use to build UI components like page selectors.

Step 2: Implement the Repository Method

Next, create a repository method that retrieves only the records for the requested page:

public async Task<PagedResult<ItemModel>> GetAllItemsWithPagination(int pageNumber, int pageSize)
{
    List<ItemModel> items = new List<ItemModel>();
    int totalCount = 0;

    try
    {
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            await con.OpenAsync();

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@PageNumber", pageNumber);
            parameters.Add("@PageSize", pageSize);

            using (var result = await con.QueryMultipleAsync("GetItemsWithPagination", 
                     parameters, commandType: CommandType.StoredProcedure))
            {
                items = result.Read<ItemModel>().ToList();
                totalCount = result.ReadSingle<int>();
            }

            return new PagedResult<ItemModel>
            {
                Results = items,
                PageNumber = pageNumber,
                PageSize = pageSize,
                TotalCount = totalCount
            };
        }
    }
    catch (Exception ex)
    {
        // Log exception
        ExceptionLog obj = new ExceptionLog(ConnectionString);
        obj.Save_ExceptionLog(ex);
        throw;
    }
}

Step 3: Create the API Controller

Finally, implement a controller method that accepts pagination parameters:

[HttpGet]
public async Task<IActionResult> Get(int pageNumber = 1, int pageSize = 10, string search = "")
{
    try
    {
        if (string.IsNullOrWhiteSpace(search))
        {
            var result = await _itemRepository.GetAllItemsWithPagination(pageNumber, pageSize);
            return Ok(result);
        }
        else
        {
            var result = await _itemRepository.SearchItems(search);
            return Ok(result);
        }
    }
    catch (Exception ex)
    {
        return StatusCode(500, new { message = "An error occurred", details = ex.Message });
    }
}

Step 4: Implement the Database Stored Procedure

Your stored procedure should handle both fetching the paginated data and the total count:

CREATE PROCEDURE [dbo].[GetItemsWithPagination]
    @PageNumber INT,
    @PageSize INT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Calculate how many rows to skip
    DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;
    
    -- Get the specific page of data
    SELECT * FROM Items
    ORDER BY Id
    OFFSET @Offset ROWS
    FETCH NEXT @PageSize ROWS ONLY;
    
    -- Get the total count of records
    SELECT COUNT(*) FROM Items;
END

Adding Search Functionality

Pagination becomes even more powerful when combined with search:

public async Task<PagedResult<ItemModel>> SearchItems(string search)
{
    List<ItemModel> items = new List<ItemModel>();
    int totalCount = 0;

    try
    {
        using (SqlConnection con = new SqlConnection(ConnectionString))
        {
            await con.OpenAsync();
            
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("@Search", search);

            using (var result = await con.QueryMultipleAsync("SearchItems", 
                     parameters, commandType: CommandType.StoredProcedure))
            {
                items = result.Read<ItemModel>().ToList();
                totalCount = items.Count;
            }

            return new PagedResult<ItemModel>
            {
                Results = items,
                PageNumber = 1,
                PageSize = items.Count,
                TotalCount = totalCount
            };
        }
    }
    catch (Exception ex)
    {
        // Log and handle exception
        throw;
    }
}

Performance Comparison

Let’s compare these approaches across key metrics:

Metric	GetAll Method	Pagination Method
Memory Usage	High (all records)	Low (only requested page)
Database Load	High	Low
Network Transfer	Large payload	Small payload
Response Time	Increases with data size	Consistent
Scalability	Poor	Excellent
Client-side Performance	Can be slow with large datasets	Consistent

When to Use Each Approach

Use GetAll When:

• Your dataset is guaranteed to remain small (fewer than 100 records)
• You need to perform complex operations on the entire dataset at once
• You’re building an offline-first application that needs to cache all data
• You’re creating an export or report function

Use Pagination When:

• Your data could grow to hundreds or thousands of records
• You’re displaying data in a tabular UI with page navigation
• You need consistent performance regardless of dataset size
• You want to minimize database and server load
• You’re building a public-facing API

Implementation Best Practices

1. Set Reasonable Defaults: Use sensible default values for page size (10-25 items) and page number (1).
2. Validate Input Parameters: Ensure page sizes aren’t too large to prevent abuse.
3. Include Metadata: Return total count and page information to help clients build navigation.
4. Use Asynchronous Methods: Keep your API responsive by using async/await patterns.
5. Include Sorting Options: Allow clients to specify sort fields and directions.
6. Implement Filtering: Combine pagination with filtering for even more efficient data retrieval.
7. Index Database Tables: Ensure your database tables are properly indexed for the fields used in pagination queries.

Front-end Integration

Here’s how you might consume a paginated API in a modern JavaScript framework:

async function fetchItems(pageNumber = 1, pageSize = 10, search = '') {
  const response = await fetch(
    `/api/items?pageNumber=${pageNumber}&pageSize=${pageSize}&search=${search}`
  );
  const data = await response.json();
  
  // Update UI with results
  renderItems(data.results);
  
  // Update pagination controls
  updatePagination({
    currentPage: data.pageNumber,
    pageSize: data.pageSize,
    totalCount: data.totalCount,
    totalPages: Math.ceil(data.totalCount / data.pageSize)
  });
}

Conclusion

While the GetAll approach is simpler to implement, pagination offers significant advantages in terms of performance, scalability, and user experience. As your application grows, implementing proper pagination becomes not just a nice-to-have feature but a necessity for maintaining performance.

By following the implementation steps outlined in this blog post, you can create a robust pagination system that will serve your application well as it scales. Remember that the small additional effort required to implement pagination pays dividends in improved application performance and user satisfaction.

For complex applications, consider combining pagination with filtering, searching, and sorting capabilities to create a truly flexible and performant data access layer.