Mastering API Pagination Methodology with the Microsoft Stack

Pagination

Image source: online

Introduction

As a software developer since 2009, I’ve witnessed the transformation of APIs from simple data endpoints to the backbone of modern applications. My expertise in the Microsoft stack ASP.NET MVC, ASP.NET Core, IIS, and SQL Server—has allowed me to build robust, scalable, and secure APIs for diverse industries.

Pagination, a cornerstone of API design, is critical for managing large datasets, ensuring performance, and delivering a seamless user experience. This blog is a culmination of my experience, offering a definitive guide to API pagination methodologies with practical insights, real-world examples, and actionable code. By sharing this knowledge, I aim to establish myself as a thought leader in API development while empowering the developer community. Whether you’re building a simple to-do list API or a global e-commerce platform, this guide will equip you with the tools to master pagination with flexibility, scalability, and cost efficiency.

📘 Table of Contents

1. Introduction to APIs and Pagination

1.1 What Are APIs and Why Do They Matter
1.2 What Is API Pagination and Its Importance
1.3 Theoretical Foundations of Pagination
1.4 Business Case for Effective Pagination

2. Pagination Methodologies

2.1 Offset-Based Pagination
2.2 Page-Based Pagination
2.3 Cursor-Based Pagination
2.4 Token-Based Pagination
2.5 Time-Based Pagination
2.6 Seek/Index-Based Pagination
2.7 Hybrid Pagination
2.8 Header-Based Pagination
2.9 Hypermedia (HATEOAS) Pagination

3. Implementing Pagination in ASP.NET Core

3.1 Setting Up the Development Environment
3.2 Basic Pagination Implementation
3.3 Advanced Pagination with Filters and Sorting
3.4 Optimizing Pagination with SQL Server

4. Best Practices for Pagination

4.1 Flexibility: Supporting Multiple Pagination Styles
4.2 Scalability: Handling Large Datasets
4.3 Security: Protecting Pagination Endpoints
4.4 User Experience: Designing Intuitive APIs
4.5 Performance: Optimizing Queries and Responses
4.6 Reducing Development Time and Cost
4.7 Minimizing Dependencies

5. API Architecture and Design Patterns

5.1 RESTful API Design Principles
5.2 GraphQL as an Alternative
5.3 Microservices vs. Monolithic APIs
5.4 Domain-Driven Design (DDD) in APIs
5.5 CQRS and Event Sourcing

6. API Deployment

6.1 Deploying Paginated APIs on IIS
6.2 Containerization with Docker
6.3 Cloud Deployment with Azure API Management
6.4 CI/CD Pipelines for APIs

7. API Security

7.1 Authentication: JWT, OAuth 2.0, and OpenID Connect
7.2 Authorization and Role-Based Access Control
7.3 Protecting Against Common Threats
7.4 Rate Limiting and Throttling

8. Performance Optimization

8.1 Caching Strategies
8.2 Asynchronous Programming
8.3 Database Optimization with SQL Server
8.4 Load Balancing and Scaling

9. API Integration and User Experience

9.1 Designing Intuitive APIs
9.2 API Documentation with Swagger/OpenAPI
9.3 Client-Side Integration

10. API Lifecycle Management

10.1 Versioning Strategies
10.2 Deprecation and Sunsetting
10.3 Monitoring and Logging

11. Real-Life Use Cases and Business Cases

11.1 E-Commerce: Product Listing API
11.2 Social Media: News Feed Pagination
11.3 Healthcare: Patient Records API
11.4 Financial: Transaction Processing API

12. Pros and Cons of Pagination Methods

12.1 Offset-Based Pagination
12.2 Page-Based Pagination
12.3 Cursor-Based Pagination
12.4 Token-Based Pagination
12.5 Time-Based Pagination
12.6 Seek/Index-Based Pagination
12.7 Hybrid Pagination
12.8 Header-Based Pagination
12.9 Hypermedia (HATEOAS) Pagination

13. Alternatives to Pagination

13.1 GraphQL with Relay-Style Pagination
13.2 Streaming APIs
13.3 Data Aggregation and Summarization

14. Basic to Advanced Scenarios

14.1 Basic: Simple Paginated To-Do List API
14.2 Intermediate: Multi-Tenant Paginated API
14.3 Advanced: Real-Time Paginated API with SignalR

15. Alternatives to Microsoft Stack

15.1 Node.js with Express
15.2 Python with Django/Flask
15.3 Java with Spring Boot

16. Conclusion

The Future of API Pagination

1. Introduction to APIs and Pagination

1.1 What Are APIs and Why Do They MatterAn? An API (Application Programming Interface) is a set of rules enabling communication between software applications. APIs power modern ecosystems, from mobile apps to cloud services, by facilitating data exchange and functionality integration. Real-Life Example: Amazon’s API allows third-party sellers to list products, manage inventory, and process orders, driving billions in revenue through seamless integrations.1.2 What Is API Pagination and Its ImportancePagination divides large datasets into smaller, manageable chunks (pages) delivered to clients. It’s essential for APIs handling large volumes of data, ensuring performance, scalability, and user satisfaction. Key Terms.

Resource: The data entity (e.g., /products).
Request: Includes pagination parameters (e.g., page, cursor).
Response: Returns a page of data with metadata (e.g., totalPages).
Payload: The data in the response body, typically JSON.

Real-Life Example: Twitter’s API paginates tweets to deliver 20 per request, reducing server load and improving user experience.1.3 Theoretical Foundations of Pagination. Pagination involves.

Consistency: Ensuring stable results despite data changes.
Efficiency: Minimizing database and network overhead.
Flexibility: Supporting sorting, filtering, and varying page sizes.
Scalability: Handling millions of records efficiently.

Challenges

Performance degradation with large datasets.
Data consistency during concurrent updates.
User experience for navigation (e.g., infinite scrolling vs. page controls).

1.4 Business Case for Effective Pagination.

Enhances User Retention: Fast, responsive APIs improve engagement.
Reduces Costs: Optimized data delivery lowers cloud expenses.
Enables Scalability: Supports growth in data and user volume.

A retail platform uses pagination to display 50 products per page, reducing server costs by 30% and improving page load times, which boosts conversions. 2. Pagination Methodologies 2.1 Offset-Based Pagination Description: Uses offset and limit to skip records and fetch a fixed number. Although simple, this approach is inefficient for large datasets due to database scanning. Use Case: A blog platform displaying 10 posts per page.Code Example (ASP.NET Core).

[HttpGet]
public async Task<ActionResult<PagedResult<Post>>> GetPosts(int offset = 0, int limit = 10)
{
    var totalItems = await _context.Posts.CountAsync();
    var items = await _context.Posts
        .OrderBy(p => p.Id)
        .Skip(offset)
        .Take(limit)
        .ToListAsync();

    return new PagedResult<Post>
    {
        Items = items,
        Offset = offset,
        Limit = limit,
        TotalItems = totalItems,
        TotalPages = (int)Math.Ceiling(totalItems / (double)limit)
    };
}

public class PagedResult<T>
{
    public IEnumerable<T> Items { get; set; }
    public int Offset { get; set; }
    public int Limit { get; set; }
    public int TotalItems { get; set; }
    public int TotalPages { get; set; }
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime PublishedAt { get; set; }
}

SQL Query

SELECT *
FROM Posts
ORDER BY Id
OFFSET @offset ROWS
FETCH NEXT @limit ROWS ONLY;

Real-Life Example: A startup’s blog platform uses offset-based pagination for 1,000 posts, but performance degrades as the dataset grows to 100,000.Pros:

Simple to implement.
Intuitive for small datasets.
Easy to integrate with UI pagination controls.

Cons

Poor performance for large offsets (e.g., page 1000).
Inconsistent results if the data changes.
High database load for deep pagination.

Business Case

A small e-commerce site uses offset-based pagination for product listings, enabling rapid development but requiring optimization as traffic scales. Basic Scenario: Paginate tasks with offset=0&limit=10. Advanced Scenario: Paginate historical data with caching for large offsets.Alternatives: Cursor-based or Seek/Index-based pagination for scalability.2.2 Page-Based Pagination Description: Uses page and pageSize parameters, translating to an offset internally (e.g., offset = (page - 1) * pageSize)—User-friendly for web UIs.Use Case: An e-commerce API displaying 20 products per page.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Product>>> GetProducts(int page = 1, int pageSize = 20)
{
    if (page < 1 || pageSize < 1)
        return BadRequest("Invalid page or pageSize");

    var totalItems = await _context.Products.CountAsync();

    var items = await _context.Products
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Product>
    {
        Items = items,
        Page = page,
        PageSize = pageSize,
        TotalItems = totalItems,
        TotalPages = (int)Math.Ceiling(totalItems / (double)pageSize)
    };
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Response

{
  "items": [
    {
      "id": 21,
      "name": "Laptop",
      "price": 999.99
    },
    ...
  ],
  "page": 2,
  "pageSize": 20,
  "totalItems": 1000,
  "totalPages": 50
}

Real-Life Example: Amazon’s product search API uses page-based pagination, allowing users to navigate via page numbers.Pros:

Intuitive for users.
Easy UI integration.
Simple implementation.

Cons

Same performance issues as offset-based.
Not suitable for infinite scrolling.
Inconsistent with dynamic data.

Business Case

A job board uses page-based pagination for listings, enhancing user experience with familiar navigation. Basic Scenario: Paginate posts with page=1&pageSize=10. Advanced Scenario: Paginate analytics data with dynamic sorting.Alternatives: Cursor-based for infinite scrolling, Hypermedia for discoverability.2.3 Cursor-Based Pagination Description: Uses a stable column (e.g., ID) to fetch records after a cursor, ideal for large datasets and infinite scrolling. Use Case: A social media API paginating a news feed.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Post>>> GetPosts(string cursor = null, int limit = 10)
{
    var query = _context.Posts.AsQueryable();

    if (!string.IsNullOrEmpty(cursor) && int.TryParse(cursor, out var cursorId))
    {
        query = query.Where(p => p.Id > cursorId);
    }
    var items = await query
        .OrderBy(p => p.Id)
        .Take(limit)
        .ToListAsync();

    var nextCursor = items.Any() ? items.Last().Id.ToString() : null;
    return new PagedResult<Post>
    {
        Items = items,
        Cursor = nextCursor,
        Limit = limit
    };
}

Response

{
  "items": [
    { "id": 101, "title": "Post 101", "publishedAt": "2025-07-24" },
    ...
  ],
  "cursor": "110",
  "limit": 10
}

Real-Life Example: Twitter’s API uses cursor-based pagination for timelines, ensuring stable results as new tweets are added.Pros.

High performance for large datasets.
Consistent results.
Ideal for infinite scrolling.

Cons

Less intuitive than page-based.
No random page access.
Requires unique, ordered columns.

Business Case

A social media platform uses cursor-based pagination to reduce server costs and improve feed performance. Basic Scenario: Paginate tasks with cursor=100&limit=10. Advanced Scenario: Paginate chat history with timestamp cursors.Alternatives: Token-based for security, Seek/Index-based for complex sorting.2.4 Token-Based Pagination Description: Uses opaque tokens to reference the next page, hiding implementation details for security and flexibility. Use Case: A financial API paginating transaction history.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Transaction>>> GetTransactions(string token = null, int limit = 10)
{
    int cursorId = 0;
    if (!string.IsNullOrEmpty(token))
    {
        var decoded = Convert.FromBase64String(token);
        cursorId = BitConverter.ToInt32(decoded, 0);
    }

    var items = await _context.Transactions
        .Where(t => t.Id > cursorId)
        .OrderBy(t => t.Id)
        .Take(limit)
        .ToListAsync();

    var nextToken = items.Any() 
        ? Convert.ToBase64String(BitConverter.GetBytes(items.Last().Id)) 
        : null;

    return new PagedResult<Transaction>
    {
        Items = items,
        Token = nextToken,
        Limit = limit
    };
}

public class Transaction
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public DateTime Date { get; set; }
}

Response

{
  "items": [
    { "id": 101, "amount": 50.00, "date": "2025-07-24" }
    // ...
  ],
  "token": "AAAABQ==",
  "limit": 10
}

Real-Life Example: PayPal’s API uses token-based pagination for secure transaction history retrieval.Pros.

Secure, hides database details.
Flexible for various pagination strategies.
Supports stateless APIs.

Cons

Complex implementation.
Opaque tokens may confuse developers.
Token management overhead.

Business Case

A fintech company uses token-based pagination to meet compliance requirements, enhancing trust.Basic Scenario: Paginate profiles with tokens. Advanced Scenario: Paginate multi-tenant data with complex tokens.Alternatives: Cursor-based for simplicity, Hypermedia for discoverability.2.5 Time-Based Pagination Description: Uses date/time fields to paginate records, ideal for time-series data. Use Case: A logging API paginating event logs.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Log>>> GetLogs(DateTime? after = null, int limit = 10)
{
    var query = _context.Logs.AsQueryable();
    
    if (after.HasValue)
    {
        query = query.Where(l => l.Timestamp > after.Value);
    }

    var items = await query
        .OrderBy(l => l.Timestamp)
        .Take(limit)
        .ToListAsync();

    var nextAfter = items.Any() ? items.Last().Timestamp : (DateTime?)null;

    return new PagedResult<Log>
    {
        Items = items,
        After = nextAfter,
        Limit = limit
    };
}

public class Log
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string Message { get; set; }
}

Response

{
  "items": [
    { "id": 101, "timestamp": "2025-07-24T12:00:00Z", "message": "Error occurred" }
    ...
  ],
  "after": "2025-07-24T12:10:00Z",
  "limit": 10
}

Real-Life Example: Datadog’s API paginates system logs by timestamp, enabling efficient time-based navigation.Pros.

Ideal for time-series data.
Efficient with indexed timestamps.
Intuitive for chronological data.

Cons

Limited to time-based datasets.
Time zone handling complexity.
Duplicate timestamps need tiebreakers.

Business Case

A SaaS company uses time-based pagination for audit logs, reducing compliance costs. Basic Scenario: Paginate articles by publication date. Advanced Scenario: Paginate IoT sensor data with millisecond precision.Alternatives: Seek/Index-based for non-time-based sorting, Cursor-based for simplicity.2.6 Seek/Index-Based Pagination Description: Uses sorted keys (e.g., id > 100) for high-performance pagination of large, sorted datasets. Use Case: A financial API paginating transactions by date and ID.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Transaction>>> GetTransactions(string after = null, int limit = 10)
{
    var query = _context.Transactions.AsQueryable();

    if (!string.IsNullOrEmpty(after))
    {
        var parts = after.Split(',');

        if (DateTime.TryParse(parts[0], out var date) && int.TryParse(parts[1], out var id))
        {
            query = query.Where(t => t.Date > date || (t.Date == date && t.Id > id));
        }
    }

    var items = await query
        .OrderBy(t => t.Date)
        .ThenBy(t => t.Id)
        .Take(limit)
        .ToListAsync();

    var nextAfter = items.Any() ? $"{items.Last().Date:yyyy-MM-dd},{items.Last().Id}" : null;

    return new PagedResult<Transaction>
    {
        Items = items,
        Cursor = nextAfter,
        Limit = limit
    };
}

SQL Index

CREATE INDEX IX_Transactions_Date_Id 
ON Transactions (Date, Id);

Real-Life Example: A stock trading platform paginates trade history by execution time, ensuring performance for millions of records.Pros.

High performance for large datasets.
Flexible sorting.
Stable results.

Cons

Complex implementation.
Requires indexed columns.
Sequential navigation only.

Business Case:

A logistics company uses seek/index-based pagination for delivery records, improving query performance. Basic Scenario: Paginate products by ID. Advanced Scenario: Paginate sales data with multiple sort criteria.Alternatives: Cursor-based for simpler sorting, Time-based for chronological data.2.7 Hybrid Pagination Description: Combines multiple pagination styles (e.g., cursor + page-based) for flexibility. Use Case: A job board API supporting page-based UI navigation and cursor-based mobile scrolling.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Job>>> GetJobs([FromQuery] PaginationParameters parameters)
{
    if (!string.IsNullOrEmpty(parameters.Cursor) && int.TryParse(parameters.Cursor, out var cursorId))
    {
        var items = await _context.Jobs
            .Where(j => j.Id > cursorId)
            .OrderBy(j => j.Id)
            .Take(parameters.Limit)
            .ToListAsync();

        return new PagedResult<Job>
        {
            Items = items,
            Cursor = items.Any() ? items.Last().Id.ToString() : null,
            Limit = parameters.Limit
        };
    }

    var totalItems = await _context.Jobs.CountAsync();
    var itemsOffset = await _context.Jobs
        .OrderBy(j => j.Id)
        .Skip((parameters.Page.GetValueOrDefault(1) - 1) * parameters.Limit)
        .Take(parameters.Limit)
        .ToListAsync();

    return new PagedResult<Job>
    {
        Items = itemsOffset,
        Page = parameters.Page.GetValueOrDefault(1),
        Limit = parameters.Limit,
        TotalItems = totalItems,
        TotalPages = (int)Math.Ceiling(totalItems / (double)parameters.Limit)
    };
}

public class PaginationParameters
{
    public int? Page { get; set; }
    public int Limit { get; set; } = 10;
    public string Cursor { get; set; }
}

public class Job
{
    public int Id { get; set; }
    public string Title { get; set; }
    public string Company { get; set; }
}

Real-Life Example: LinkedIn’s API supports hybrid pagination for web and mobile clients.Pros.

Flexible for diverse clients.
Combining method strengths.
Enhances API adoption.

Cons

Complex implementation.
Higher maintenance.
Potential inconsistency.

Business Case

A SaaS platform uses hybrid pagination to support enterprise and mobile users, increasing retention. Basic Scenario: Support page-based and cursor-based pagination. Advanced Scenario: Combine token-based and time-based pagination.Alternatives: Single-method pagination, Hypermedia pagination.2.8 Header-Based Pagination Description: Sends pagination metadata in HTTP headers (e.g., Link, X-Total-Count), keeping the response body clean. Use Case: A public API for third-party developers.

Code Example

[HttpGet]
public async Task<ActionResult<IEnumerable<Product>>> GetProducts(int page = 1, int pageSize = 10)
{
    var totalItems = await _context.Products.CountAsync();
    var items = await _context.Products
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    Response.Headers.Add("X-Total-Count", totalItems.ToString());
    Response.Headers.Add("X-Total-Pages", ((int)Math.Ceiling(totalItems / (double)pageSize)).ToString());

    if (page < Math.Ceiling(totalItems / (double)pageSize))
    {
        Response.Headers.Add("Link", $"</api/products?page={page + 1}&pageSize={pageSize}>; rel=\"next\"");
    }

    return Ok(items);
}

Response Headers

X-Total-Count: 1000
X-Total-Pages: 100
Link: </api/products?page=2&pageSize=10>; rel="next"

Real-Life Example: GitHub’s API uses header-based pagination for repository commits.Pros.

Clean response body.
Aligns with REST principles.
Flexible metadata delivery.

Cons

Client complexity for header parsing.
Limited header size.
Less intuitive for beginners.

Business Case

A developer platform uses header-based pagination to attract third-party developers. Basic Scenario: Paginate profiles with Link headers. Advanced Scenario: Use custom headers for complex metadata.Alternatives: Hypermedia pagination, body-based pagination.2.9 Hypermedia (HATEOAS) Pagination Description: Embeds pagination links (e.g., self, next, prev) in the response body for self-discoverable APIs.Use Case: A public API for long-term maintainability.

Code Example

[HttpGet]
public async Task<ActionResult<PagedResult<Product>>> GetProducts(int page = 1, int pageSize = 10)
{
    var totalItems = await _context.Products.CountAsync();
    var items = await _context.Products
        .OrderBy(p => p.Id)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    var links = new List<Link>
    {
        new Link { Rel = "self", Href = $"/api/products?page={page}&pageSize={pageSize}", Method = "GET" }
    };

    if (page > 1)
    {
        links.Add(new Link { Rel = "prev", Href = $"/api/products?page={page - 1}&pageSize={pageSize}", Method = "GET" });
    }

    if (page < Math.Ceiling(totalItems / (double)pageSize))
    {
        links.Add(new Link { Rel = "next", Href = $"/api/products?page={page + 1}&pageSize={pageSize}", Method = "GET" });
    }

    return new PagedResult<Product>
    {
        Items = items,
        Page = page,
        PageSize = pageSize,
        TotalItems = totalItems,
        TotalPages = (int)Math.Ceiling(totalItems / (double)pageSize),
        Links = links
    };
}

public class Link
{
    public string Rel { get; set; }
    public string Href { get; set; }
    public string Method { get; set; }
}

Response

{
  "items": [
    { "id": 11, "name": "Tablet", "price": 299.99 }
    ...
  ],
  "page": 2,
  "pageSize": 10,
  "totalItems": 100,
  "totalPages": 10,
  "links": [
    { "rel": "self", "href": "/api/products?page=2&pageSize=10", "method": "GET" },
    { "rel": "prev", "href": "/api/products?page=1&pageSize=10", "method": "GET" },
    { "rel": "next", "href": "/api/products?page=3&pageSize=10", "method": "GET" }
  ]
}

Real-Life Example: Stripe’s API uses HATEOAS for paginated resources like charges.Pros.

Self-discoverable APIs.
Enhances maintainability.
Aligns with REST maturity.

Cons

Increased response size.
Complex client implementation.
Higher development effort.

Business Case

A B2B platform uses HATEOAS to simplify partner integrations. Basic Scenario: Paginate products with links. Advanced Scenario: Include links for multiple actions.Alternatives: Header-based pagination, simple pagination 3. Implementing Pagination in ASP.NET Core 3.:1 Setting Up the Environment

Tools: Visual Studio 2022, .NET 8 SDK, SQL Server Express, Postman.

Project Setup

dotnet new webapi -n PaginationApi
cd PaginationApi
dotnet add package Microsoft.EntityFrameworkCore.SqlServer

DbContext

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) 
        : base(options) 
    { 
    }
    public DbSet<Product> Products { get; set; }
}

Program.cs

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"))
);

builder.Services.AddControllers();
var app = builder.Build();
app.UseHttpsRedirection();
app.MapControllers();
app.Run();

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=ApiDb;Trusted_Connection=True;"
  }
}

3.2 Basic Pagination ImplementationSee Section 2.1 for offset-based pagination.3.3 Advanced Pagination with Filters and Sorting: Code Example.

[HttpGet]
public async Task<ActionResult<PagedResult<Product>>> GetProducts(
    int page = 1, 
    int pageSize = 10, 
    string filter = null, 
    string sort = "id")
{
    var query = _context.Products.AsQueryable();

    if (!string.IsNullOrEmpty(filter))
    {
        query = query.Where(p => p.Name.Contains(filter));
    }

    query = sort.ToLower() == "name" 
        ? query.OrderBy(p => p.Name) 
        : query.OrderBy(p => p.Id);

    var totalItems = await query.CountAsync();

    var items = await query
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Product>
    {
        Items = items,
        Page = page,
        PageSize = pageSize,
        TotalItems = totalItems,
        TotalPages = (int)Math.Ceiling(totalItems / (double)pageSize)
    };
}

3.4 Optimizing Pagination with SQL Server.

Indexes: Create indexes on sorting columns (e.g., Id, Name).
Covering Indexes: Include frequently accessed columns.

CREATE INDEX IX_Products_Name
ON Products(Name)
INCLUDE (Price);

Real-Life Example: A retail API optimizes product pagination with indexes, reducing query time by 50%.4. Best Practices for Pagination 4.1 Flexibility Support multiple pagination styles (e.g., page-based, cursor-based) via query parameters.4.2 Scalability.

Use indexes for sorting and filtering.
Avoid COUNT(*) for large datasets; use approximate counts.

Code Example (Approximate Count)

SELECT SUM(row_count) 
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID('Products') 
  AND index_id IN (0, 1);

4.3 Security

Validate pagination parameters to prevent SQL injection.
Implement rate limiting.

Code Example (Rate Limiting)

builder.Services.AddRateLimiter(options =>
{
    options.AddFixedWindowLimiter("pagination", opt =>
    {
        opt.PermitLimit = 100;
        opt.Window = TimeSpan.FromMinutes(1);
    });
});
app.UseRateLimiter();

4.4 User Experience

Include metadata (e.g., totalPages, nextCursor).
Use HATEOAS for navigation links.

4.5 Performance

Cache responses with Redis.
Use asynchronous queries.

Code Example (Caching)

[HttpGet]
public async Task<ActionResult<PagedResult<Product>>> GetProducts(int page = 1, int pageSize = 10)
{
    var cacheKey = $"products_page_{page}_size_{pageSize}";
    var cached = await _cache.GetStringAsync(cacheKey);
    
    if (cached != null)
    {
        return JsonSerializer.Deserialize<PagedResult<Product>>(cached);
    }

    var result = await GetProductsFromDb(page, pageSize);
    
    await _cache.SetStringAsync(
        cacheKey, 
        JsonSerializer.Serialize(result), 
        new DistributedCacheEntryOptions
        {
            AbsoluteExpirationRelativeToNow = TimeSpan.FromMinutes(5)
        }
    );
    return result;
}

4.6 Reducing Development Time and Cost Use tools like Swashbuckle for automatic documentation.4.7 Minimizing DependenciesRely on Entity Framework Core for pagination logic.Real-Life Example: A startup uses EF Core’s query capabilities to avoid external pagination libraries, reducing costs 5. API Architecture and Design Patterns 5.1 RESTful API Design Principles

Use nouns for resources (e.g., /products).
Leverage HTTP methods (GET, POST, PUT, DELETE).
Include pagination metadata.

5.2 GraphQL as an Alternative. GraphQL supports Relay-style pagination.

Code Example

public class ProductConnection : ObjectType
{
    protected override void Configure(IObjectTypeDescriptor descriptor)
    {
        descriptor.Field("edges")
            .ResolveWith<ProductResolver>(r => r.GetEdges(default))
            .Type<ListType<ProductEdgeType>>();

        descriptor.Field("pageInfo")
            .ResolveWith<ProductResolver>(r => r.GetPageInfo(default))
            .Type<PageInfoType>();
    }
}

5.3 Microservices vs. Monolithic APIs

Monolithic: Simpler for small APIs, harder to scale.
Microservices: Scalable but complex.

5.4 Domain-Driven Design (DDD)Use repositories for pagination logic.

Code Example

public interface IProductRepository
{
    Task<PagedResult<Product>> GetProductsAsync(int page, int pageSize);
}

5.5 CQRS and Event Sourcing: Separate read and write operations for pagination.

Code Example

public class GetProductsQuery
{
    public int Page { get; set; }
    public int PageSize { get; set; }
}

public class GetProductsQueryHandler
{
    public async Task<PagedResult<Product>> Handle(GetProductsQuery query)
    {
        // Pagination logic
    }
}

6. API Deployment 6.1 Deploying on IIS

Publish: dotnet publish -c Release.
Configure IIS for high request volumes.

6.2 Containerization with Docker Dockerfile

FROM mcr.microsoft.com/dotnet/aspnet:8.0
COPY bin/Release/net8.0/publish/ /app
WORKDIR /app
ENTRYPOINT ["dotnet", "PaginationApi.dll"]

6.3 Azure API Management: Enforce pagination policies (e.g., limit pageSize).Policy.

<policies>
  <inbound>
    <set-query-parameter name="pageSize" exists-action="override">
      <value>100</value>
    </set-query-parameter>
  </inbound>
</policies>

6.4 CI/CD Pipelines GitHub Actions.

name: Deploy API
on:
  push:
    branches: [ main ]
jobs:
  build-and-deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - name: Setup .NET
        uses: actions/setup-dotnet@v3
        with:
          dotnet-version: '8.0.x'
      - name: Build
        run: dotnet build
      - name: Publish
        run: dotnet publish -c Release -o ./publish
      - name: Deploy to Azure
        uses: azure/webapps-deploy@v2
        with:
          app-name: pagination-api
          publish-profile: ${{ secrets.AZURE_PUBLISH_PROFILE }}

7. API Security7.1 AuthenticationUse JWT for secure pagination.

Code Example

builder.Services.AddAuthentication(JwtBearerDefaults.AuthenticationScheme)
    .AddJwtBearer(options =>
    {
        options.TokenValidationParameters = new TokenValidationParameters
        {
            ValidateIssuer = true,
            ValidateAudience = true,
            ValidIssuer = "your-issuer",
            ValidAudience = "your-audience",
            IssuerSigningKey = new SymmetricSecurityKey(
                Encoding.UTF8.GetBytes("your-secret-key"))
        };
    });

7.2 Authorization: Restrict pagination endpoints to authorized roles.7.3 Protecting Against Threats,

SQL Injection: Use parameterized queries.
XSS/CSRF: Sanitize inputs, use anti-forgery tokens.

7.4 Rate Limiting See Section 4.3.8. Performance Optimization 8.1 Caching Use Redis for paginated responses.8.2 Asynchronous Programming Use async/await for non-blocking queries.8.3 Database Optimization,

Eager loading to avoid N+1 queries.
Composite indexes for multi-column sorting.

8.4 Load Balancing Use Azure Load Balancer for high traffic 9. API Integration and User Experience 9.1 Designing Intuitive APIs

Consistent naming.
Clear error messages.

9.2 API Documentation Use Swashbuckle for Swagger.

Code Example

builder.Services.AddSwaggerGen(c =>
{
    c.SwaggerDoc("v1", new OpenApiInfo 
    { 
        Title = "Pagination API", 
        Version = "v1" 
    });
});

app.UseSwagger();
app.UseSwaggerUI();

9.3 Client-Side Integration Code Example (HttpClient).

using var client = new HttpClient();
var response = await client.GetAsync("https://api.example.com/products?page=1&pageSize=10");
var products = await response.Content.ReadFromJsonAsync<PagedResult<Product>>();

10. API Lifecycle Management10.1 VersioningUse URI versioning (e.g., /api/v1/products).10.2 Deprecation: Announce deprecation 6–12 months in advance.10.3 Monitoring: Use Application Insights.

Code Example

builder.Services.AddApplicationInsightsTelemetry();

11. Real-Life Use Cases and Business Cases 11.1 E-Commerce Use Case: Paginate product listings. Business Case: Improves conversions with fast load times.11.2 Social Media Use Case: Paginate news feeds with cursor-based pagination. Business Case: Enhances user engagement.11.3 Healthcare Use Case: Paginate patient records with time-based pagination. Business Case: Ensures compliance and performance.11.4 Financial Use Case: Paginate transactions with token-based pagination. Business Case: Reduces compliance risks 12. Pros and Cons of Pagination Methods.

Method	Pros	Cons
Offset-Based	Simple, intuitive	Poor performance for large datasets
Page-Based	User-friendly, UI-friendly	Same issues as offset-based
Cursor-Based	Efficient, consistent	Less intuitive, no random access
Token-Based	Secure, flexible	Complex, opaque tokens
Time-Based	Ideal for time-series	Limited to chronological data
Seek/Index-Based	High performance, flexible sorting	Complex implementation
Hybrid	Flexible for diverse clients	Increased complexity
Header-Based	Clean response body, RESTful	Client complexity for headers
Hypermedia	Self-discoverable, maintainable	Larger responses, complex clients

13. Alternatives to Pagination13.1 GraphQL with Relay-Style PaginationSupports flexible pagination with connections.13.2 Streaming APIs: Stream data for real-time use cases.13.3 Data Aggregation. Summarize data to avoid pagination . 14. Basic to Advanced Scenarios. 14.1 Basic: To-Do List API. See Section 2.1.14.2 Intermediate: Multi-Tenant API.

Code Example

[HttpGet]
[Authorize]
public async Task<ActionResult<PagedResult<Task>>> GetTasks(int page = 1, int pageSize = 10)
{
    var tenantId = User.Claims.First(c => c.Type == "tenant_id").Value;
    var items = await _context.Tasks
        .Where(t => t.TenantId == tenantId)
        .Skip((page - 1) * pageSize)
        .Take(pageSize)
        .ToListAsync();

    return new PagedResult<Task> 
    { 
        Items = items, 
        Page = page, 
        PageSize = pageSize 
    };
}

14.3 Advanced: Real-Time API with SignalR

Code Example

public class PaginationHub : Hub
{
    public async Task SendPageUpdate(PagedResult<Product> page)
    {
        await Clients.All.SendAsync("ReceivePageUpdate", page);
    }
}

15. Alternatives to Microsoft Stack 15.1 Node.js with Express

Pros: Lightweight, fast prototyping.
Cons: Less robust for enterprise.

15.2 Python with Django/Flask

Pros: Rich libraries, rapid development.
Cons: Slower than ASP.NET Core.

15.3 Java with Spring Boot

Pros: Enterprise-grade, strong typing.
Cons: Verbose code.

16. Conclusion

The Future of API Pagination. Pagination is essential for scalable, user-friendly APIs. The Microsoft stack offers robust tools to implement diverse pagination methodologies, from simple offset-based to advanced HATEOAS. By prioritizing flexibility, scalability, and security, developers can build APIs that meet modern demands. As APIs evolve with AI, serverless computing, and real-time data, pagination will remain critical. Continue learning, experimenting, and sharing knowledge to shape the future of API development.

My blog link: https://imomins.blogspot.com/2025/07/mastering-api-pagination-methodology.html