Adapting Pagination from SQL Server to Amazon DynamoDB for .NET Applications

When you migrate .NET applications from SQL Server to Amazon DynamoDB, your existing pagination patterns need adaptation. .NET applications commonly use page number navigation with Language Integrated Query (LINQ) Skip() and Take() methods, which rely on SQL Server’s OFFSET/FETCH NEXT clauses and indexed ordering to provide consistent navigation experiences.

DynamoDB’s distributed NoSQL architecture uses a token-based continuation model where each response includes a LastEvaluatedKey token, a bookmark to the next set of results, instead of row offsets. As a result, your existing LINQ queries require adaptation.

This post shows you three adaptation paths for migrating pagination from SQL Server to DynamoDB: adapting to page number simulation, migrating to previous/next navigation, or adopting infinite scrolling. You’ll learn three C# implementation patterns, their performance and cost impact, and evaluation criteria to help you choose the approach that maintains your user experience while optimizing costs and scalability.

Solution Overview

Understanding the architectural difference between SQL Server and DynamoDB pagination is essential before adapting your .NET application. The two models follow different approaches to navigating result sets.

SQL Server: Offset-Based Pagination

SQL Server maintains global row positions using an index-backed storage engine. When you execute an OFFSET/FETCH NEXT query, SQL Server knows exactly how many rows precede the requested page. You can jump to arbitrary page, calculate total counts, and navigate bidirectionally with consistent, predictable performance for shallow pages.

DynamoDB: Token-Based Pagination

DynamoDB distributes data across multiple partitions based on the partition key. It uses a continuation token (LastEvaluatedKey) that represents the last item evaluated during a query. You pass this token as ExclusiveStartKey in your next request to resume pagination from that exact point.

The following table summarizes the key architectural differences between SQL Server and DynamoDB pagination.

SQL Server maintains global row positions, while DynamoDB uses continuation tokens. This architectural difference is why page number navigation is not aligned with DynamoDB’s access model, and why token-based patterns (previous/next, infinite scrolling) are better aligned with DynamoDB’s architecture.

Understanding Pagination Differences

In SQL Server, page number navigation, previous/next navigation, and infinite scrolling all use row positions and indexed ordering to navigate result sets. The following LINQ query shows a typical page number navigation implementation:

// SQL Server page number navigation using LINQ
var products = dbContext.Products
    .Where(p => p.Category == "Electronics")
    .OrderBy(p => p.ProductName)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize)
    .ToList();

// Returns PagedResponse<Product> with:
//   Items         - List<Product> for current page
//   CurrentPage   - int (1-based)
//   TotalPages    - int (calculated from count)
//   TotalCount    - int (total matching rows)

DynamoDB uses a token-based continuation model designed for distributed, scalable data access. When you execute a Query or Scan operation, DynamoDB returns a LastEvaluatedKey token representing the last item evaluated. You pass this token as the ExclusiveStartKey in your next request to continue pagination from that point. DynamoDB uses continuation tokens to efficiently paginate across distributed partitions without the need for global row positions, enabling high-throughput performance at scale.

Conceptual Mapping

The following table provides a one-to-one mapping between SQL Server and DynamoDB pagination counterparts.

The offset-versus-token architectural difference impacts each pagination UI pattern differently. Page number navigation requires adaptation because it relies on offset-based queries that do not map directly to DynamoDB token-based model. Previous/next navigation and infinite scrolling adapt directly because they already use sequential, token-based navigation.

The following sections examine each adaptation path, showing implementation code, performance implications, and evaluation criteria to help you choose the right approach.

Note: If your existing SQL Server application already uses previous/next navigation or infinite scrolling with keyset pagination techniques, your migration requires only backend changes to the data access layer, with no UI modifications.

Page Number Navigation

Page number navigation displays numbered page links (1, 2, 3…) that let you jump directly to a specific page. When adapting this pattern to DynamoDB, you simulate page number navigation by retrieving items sequentially until reaching the desired page.

Displaying total page counts (TotalPages and TotalCount) requires scanning the entire table, which consumes additional read capacity for large datasets. If your UI requires page numbers, consider removing total counts from the UI, caching counts periodically and accepting slightly stale values, or limiting page number navigation to small datasets where scan costs are acceptable.

The following code shows the DynamoDB implementation:

using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using System.Text.Json;

public class ProductRepository
{
    private readonly IAmazonDynamoDB _dynamoDbClient;
    private const string TableName = "Products";

    public ProductRepository(IAmazonDynamoDB dynamoDbClient)
        => _dynamoDbClient = dynamoDbClient;

    public async Task<PagedResponse<Product>> GetProductsWithPageNumber(
        string category, int pageNumber, int pageSize)
    {
        var itemsToSkip = (pageNumber - 1) * pageSize;
        var itemsRetrieved = 0;
        Dictionary<string, AttributeValue> lastKey = null;
        var products = new List<Product>();

        while (itemsRetrieved < itemsToSkip + pageSize)
        {
            var request = new QueryRequest
            {
                TableName = TableName,
                KeyConditionExpression = "Category = :category",
                ExpressionAttributeValues = new Dictionary<string, AttributeValue>
                {
                    { ":category", new AttributeValue { S = category } }
                },
                Limit = itemsToSkip + pageSize,
                ExclusiveStartKey = lastKey,
                ScanIndexForward = true
            };

            var response = await _dynamoDbClient.QueryAsync(request);

            // Only collect items after skipping preceding pages
            foreach (var item in response.Items)
            {
                itemsRetrieved++;
                if (itemsRetrieved > itemsToSkip && products.Count < pageSize)
                    products.Add(MapToProduct(item));
            }

            lastKey = response.LastEvaluatedKey;
            if (lastKey == null || lastKey.Count == 0) break;
        }

        return new PagedResponse<Product>
        {
            Items = products,
            CurrentPage = pageNumber,
            TotalPages = null,   // Requires full scan; omit for cost efficiency
            TotalCount = null    // Requires full scan; omit for cost efficiency
        };
    }

    // Cursor helpers for safe token transport
    private string SerializeCursor(Dictionary<string, AttributeValue> key)
    {
        if (key == null || key.Count == 0) return null;
        var simplified = key.ToDictionary(k => k.Key, k => k.Value.S ?? k.Value.N);
        return Convert.ToBase64String(
            System.Text.Encoding.UTF8.GetBytes(JsonSerializer.Serialize(simplified)));
    }

    private Dictionary<string, AttributeValue> DeserializeCursor(string cursor)
    {
        if (string.IsNullOrEmpty(cursor)) return null;
        var json = System.Text.Encoding.UTF8.GetString(Convert.FromBase64String(cursor));
        var simplified = JsonSerializer.Deserialize<Dictionary<string, string>>(json);
        return simplified.ToDictionary(k => k.Key,
            k => new AttributeValue { S = k.Value });
    }

    private Product MapToProduct(Dictionary<string, AttributeValue> item)
        => new Product
        {
            Category    = item["Category"].S,
            ProductName = item["ProductName"].S,
            Price       = decimal.Parse(item.GetValueOrDefault("Price")?.N ?? "0")
        };
}

This implementation reads items sequentially from the beginning for every page request. Jumping to page 10 requires reading through pages 1–9 every time, even if you previously viewed page 9. For forward-only sequential navigation (page 2 → 3 → 4), you could cache and reuse the most recent continuation token to avoid re-reading preceding pages, but this adds complexity and only benefits sequential access patterns, which page number controls don’t guarantee.

Performance and Cost Analysis

When simulating page-number navigation with DynamoDB’s token-based pagination model, performance degrades and costs increase with page depth. For example, navigating to page 10 with 20 items per page requires reading at least 200 items just to display 20. For 1 KB items using eventually consistent reads, this consumes 25 RCUs (200 items × 1 KB = 200 KB ÷ 4 KB per read unit = 50 read units × 0.5 = 25 RCUs). Page number simulation costs scale linearly with page depth because DynamoDB’s token-based model is optimized for sequential access rather than offset-based jumps.

Each deeper page navigation reads all preceding items, causing performance to degrade progressively at higher page numbers. FilterExpression and client-side filtering compound this problem by making it unpredictable how many items must be scanned to reach the target page.

Evaluation Criteria

• Analyze UI requirements: Determine whether page numbers are truly needed or if sequential (next/previous) navigation is acceptable.
• Review usage patterns: Check analytics to understand how deeply users navigate. If usage stays within the first 3–5 pages, page-number simulation may be viable.
• Calculate cost impact: Estimate RCU consumption based on typical page depths, traffic volume, and filter cardinality.
  Evaluate total count requirements: Determine if the UI requires displaying total page counts. Real-time count scans are only practical for small datasets.
• Evaluate migration effort: Consider the UI component changes and backend refactoring required to adopt alternative pagination patterns.

Previous/Next Navigation

This pattern maps directly to DynamoDB’s token-based continuation model. However, maintaining consistent page sizes introduces performance and cost considerations when you use FilterExpression or client-side filters.

The following code shows the DynamoDB implementation:

public async Task<SequentialPagedResponse<Product>> GetProductsSequential(
    string category, string cursorToken, int pageSize, bool navigateBackward = false)
{
    var request = new QueryRequest
    {
        TableName = TableName,
        KeyConditionExpression = "Category = :category",
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            { ":category", new AttributeValue { S = category } }
        },
        Limit = pageSize,
        ExclusiveStartKey = DeserializeCursor(cursorToken),  // null for first page
        ScanIndexForward = !navigateBackward  // false = backward navigation
    };

    var response = await _dynamoDbClient.QueryAsync(request);
    var products = response.Items.Select(MapToProduct).ToList();

    return new SequentialPagedResponse<Product>
    {
        Items = products,
        NextCursor = SerializeCursor(response.LastEvaluatedKey),
        HasMore = response.LastEvaluatedKey != null
                  && response.LastEvaluatedKey.Count > 0
    };
}

// Response model
public class SequentialPagedResponse<T>
{
    public List<T> Items { get; set; }
    public string NextCursor { get; set; }  // Replaces CurrentPage
    public bool HasMore { get; set; }        // Replaces TotalPages calculation
    // TotalPages and TotalCount removed - replaced by HasMore for cost-efficient navigation
}

Key Model Changes when migrating from page number navigation

1. NextCursor replaces CurrentPage. It contains serialized LastEvaluatedKey token instead of a page number.
2. HasMore replaces page count calculations. It indicates whether additional results exist.
3. Remove TotalPages and TotalCount properties. DynamoDB does not maintain global counts without a full table scan.

Each query returns a LastEvaluatedKey token representing the last item evaluated. You serialize this token and pass it as ExclusiveStartKey in the next request to continue from that point. The ScanIndexForward parameter controls navigation direction and setting it to false supports backward navigation.

Performance and Cost Benefits

Each page request reads only the items displayed, regardless of how many pages you have navigated. Without filters, moving from page 1 to page 2 consumes the same RCUs as moving from page 2 to page 3, and costs remain consistent per page regardless of depth. Unlike page number navigation where costs increase linearly with page depth, previous/next navigation maintains consistent costs per page when filters are not applied.

However, FilterExpression and client-side filters affect this cost predictability. Maintaining consistent page sizes when filters are applied requires reading additional data beyond the target page, impacting both performance and cost. See the handling FilterExpression in Pagination section for detailed strategies.

Evaluation Criteria

1. Review UI requirements: “Next/Previous” navigation is acceptable.
2. Calculate cost efficiency: Compare consistent RCU consumption per page against page number navigation’s increasing costs with page depth.
3. Evaluate page size requirements: Determine if consistent page sizes are essential for your UI or if variable page sizes are acceptable.
4. Assess filter impact: Evaluate how FilterExpression and client-side filters affect page size consistency and the additional API calls required.

Infinite Scrolling

Previous/next navigation provides cost-efficient sequential browsing, but maintaining consistent page sizes with FilterExpression requires reading additional data. Infinite scrolling addresses this technical overhead while also eliminating the need for explicit navigation controls. You can simply continue scrolling to load more content.

With variable batch sizes, you display whatever items each query returns without needing additional calls to reach a fixed page size. This reduces API calls, simplifies continuation token management, and eliminates over-read waste (items retrieved but not displayed because you reached your target page size).

The following code shows the DynamoDB implementation:

public async Task<InfiniteScrollResponse<Product>> GetProductsInfiniteScroll(
    string category, string cursorToken, int batchSize)
{
    var request = new QueryRequest
    {
        TableName = TableName,
        KeyConditionExpression = "Category = :category",
        ExpressionAttributeValues = new Dictionary<string, AttributeValue>
        {
            { ":category", new AttributeValue { S = category } }
        },
        Limit = batchSize,
        ExclusiveStartKey = DeserializeCursor(cursorToken)  // null for first load
    };

    var response = await _dynamoDbClient.QueryAsync(request);
    var products = response.Items.Select(MapToProduct).ToList();

    return new InfiniteScrollResponse<Product>
    {
        Items = products,
        NextCursor = SerializeCursor(response.LastEvaluatedKey),
        HasMore = response.LastEvaluatedKey != null
                  && response.LastEvaluatedKey.Count > 0
    };
}

// Response model
public class InfiniteScrollResponse<T>
{
    public List<T> Items { get; set; }
    public string NextCursor { get; set; }  // Replaces NextOffset (numeric)
    public bool HasMore { get; set; }
}

Key Model Changes

NextCursor replaces NextOffset. It contains serialized LastEvaluatedKey token instead of a numeric offset.

Performance and Cost Benefits

• Consistent RCU consumption per batch regardless of scroll depth.
• Fewer API calls. A single API call per batch instead of multiple calls to collect enough filtered items.
• No overread waste. Display whatever items are returned without discarding excess items.
• Simpler continuation token management (one token per load).

Evaluation Criteria

• Review UI requirements: Assess whether you need pagination controls or if automatic loading aligns with your application’s browsing patterns.
• Evaluate page size requirements: Determine if variable batch sizes are acceptable or if consistent page sizes are essential for your UI.
• Calculate cost efficiency: Compare consistent RCU consumption per batch against offset-based approaches where costs increase with scroll depth.
• Assess filter impact: Evaluate how FilterExpression and client-side filters affect batch size consistency.

Page number navigation, previous/next navigation, and infinite scrolling provide different trade-offs between user experience. Regardless of which pattern you choose, FilterExpression and client-side filters introduce additional considerations that affect page size consistency, API call frequency, and cost efficiency. The following section examines how to handle these challenges effectively across all three patterns.

Handling FilterExpression in Pagination

DynamoDB Limit parameter controls how many items are evaluated before applying filter expressions, not how many items are returned. The evaluate-then-filter behavior affects each pagination UI pattern differently, creating distinct implementation challenges and cost implications.

Impact by Pattern

Page number navigation compounds the FilterExpression challenge. Simulating page 10 requires reading items sequentially from the beginning. If your FilterExpression filters 50% of items, the code needs additional iterations to collect enough items for the target page, scanning more items than (pageNumber × pageSize) and consuming additional RCUs.

Previous/next navigation handles FilterExpression more efficiently than page number navigation but introduces overhead when maintaining consistent page sizes. The following code shows how to maintain consistent page sizes when FilterExpression reduces the number of returned items:

public async Task<SequentialPagedResponse<Product>> GetProductsWithFixedPageSize(
    string category, string cursorToken, int targetPageSize, decimal maxPrice)
{
    var products = new List<Product>();
    var lastKey = DeserializeCursor(cursorToken);
    var estimatedLimit = targetPageSize * 2;  // Assumes ~50% filter selectivity

    while (products.Count < targetPageSize)
    {
        var request = new QueryRequest
        {
            TableName = TableName,
            KeyConditionExpression = "Category = :category",
            FilterExpression = "Price < :maxPrice",
            ExpressionAttributeValues = new Dictionary<string, AttributeValue>
            {
                { ":category", new AttributeValue { S = category } },
                { ":maxPrice", new AttributeValue { N = maxPrice.ToString() } }
            },
            Limit = estimatedLimit,
            ExclusiveStartKey = lastKey
        };

        var response = await _dynamoDbClient.QueryAsync(request);
        products.AddRange(response.Items.Select(MapToProduct));

        lastKey = response.LastEvaluatedKey;
        if (lastKey == null || lastKey.Count == 0) break;

        // Adjust limit estimate based on observed selectivity
        if (response.ScannedCount > 0)
            estimatedLimit = (int)Math.Ceiling(
                (targetPageSize - products.Count) / 
                ((double)response.Count / response.ScannedCount));
    }

    // Trim to target page size if over-read
    var pageItems = products.Take(targetPageSize).ToList();

    return new SequentialPagedResponse<Product>
    {
        Items = pageItems,
        NextCursor = SerializeCursor(lastKey),
        HasMore = lastKey != null && lastKey.Count > 0
    };
}

Infinite scrolling handles FilterExpression with the fewer API calls by accepting variable batch sizes. You display whatever items each query returns without needing additional calls to reach a fixed page size. This reduces API calls and simplifies continuation token management (one token per load).

Conclusion

Migrating pagination from SQL Server to DynamoDB requires adapting to a token-based continuation model where LastEvaluatedKey replaces row offsets. This gives you three adaptation paths: page number simulation (viable for small datasets with shallow navigation), previous/next navigation (consistent per-page costs ideal for sequential browsing), and infinite scrolling (fewest API calls and simplest filter handling for content discovery applications).

When choosing your approach, evaluate your UI requirements, navigation depth, filter selectivity, and cost constraints. Replace page numbers with serialized continuation tokens, remove total count properties, and consider how FilterExpression impacts each pattern differently. Migration is an opportunity to align your pagination design with DynamoDB’s distributed architecture and your application’s actual access patterns.

References

DynamoDB Query API – AWS Documentation
DynamoDB Pagination – Working with Query Results
DynamoDB LastEvaluatedKey and ExclusiveStartKey
DynamoDB FilterExpression
DynamoDB Read Capacity Units (RCU)
AWS SDK for .NET – DynamoDB
LINQ Skip/Take in .NET
Amazon CloudWatch Metrics for DynamoDB