AWS DocumentDB with C# MongoDB driver, unsupported efficient pagination

Question

I am developing some queries and indexing against a MongoDB 4.0 using the C# MongoDB.Driver 2.12.4

All is great with MongoDB. Unfortunately, I must use AWS DocumentDB as the production database, which claims to be compatible with MongoDB, but I've been discovering several issues:

• Indexes are limited https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html#mongo-apis-index
• It does not support collation indexes or queries (already removed, managed to do case insensitive search by other means)
• It does not support facet (and I use it for efficient pagination) "message": "Command aggregate failed: Aggregation stage not supported: '$facet'.",

The question is: What's the best way to paginate results with AWS DocumentDB? Is there any option to do so with a single query? or I must hit the database twice?

This was my original implementation using Facet and aggregating facet results.

public async Task<PagedResult<TDocument>> GetAll<TDocument>(
    Expression<Func<TDocument, bool>> filter,
    Sort<TDocument> sort,
    Pagination pagination,
    CancellationToken cancellationToken = new())
    where TDocument : class, IDocument
{
    filter ??= doc => true;
    sort ??= Sort<TDocument>.Default;
    if (pagination == null) throw new ArgumentNullException(nameof(pagination));

    var collection = _collectionRetrieverService.GetCollection<TDocument>();

    var sortDefinition = GetSortDefinition(sort);
    
    var pageNumber = pagination.PageNumber;
    var pageSize = pagination.PageSize;

    var countPipelineDefinition =
        PipelineDefinition<TDocument, AggregateCountResult>
            .Create(new[]
            {
                PipelineStageDefinitionBuilder.Count<TDocument>()
            });
    
    var dataPipelineDefinition =
        PipelineDefinition<TDocument, TDocument>
            .Create(new[]
            {
                PipelineStageDefinitionBuilder.Sort(sortDefinition),
                PipelineStageDefinitionBuilder.Skip<TDocument>((pageNumber -1) * pageSize),
                PipelineStageDefinitionBuilder.Limit<TDocument>(pageSize)
            });

    const string countFacetName = "count";
    const string dataFacetName = "data";
    
    var countFacet = AggregateFacet.Create(countFacetName, countPipelineDefinition);
    var dataFacet = AggregateFacet.Create(dataFacetName, dataPipelineDefinition);

    var aggregateOptions =
        new AggregateOptions
        {
            // Collation is not currently supported in AWS DocumentDB. See https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html#mongo-apis-index
            //Collation = CollationOptions.CaseInsensitiveCollation
        };
    
    var aggregation =
        await collection
            .Aggregate(aggregateOptions)
            .Match(filter)
            .Facet(countFacet, dataFacet)
            .ToListAsync(cancellationToken);

    var count =
        aggregation
            .First()
            .Facets
            .First(x => x.Name == countFacetName)
            .Output<AggregateCountResult>()
            ?.FirstOrDefault()
            ?.Count;

    if (count is null)
    {
        var emptyResult =
            new PagedResult<TDocument>()
            {
                Items = Enumerable.Empty<TDocument>(),
                PageNumber = pageNumber,
                PageSize = pageSize,
                TotalPages = pageNumber,
                TotalCount = 0
            };
        return emptyResult;
    }
    
    var totalPages = (int)Math.Ceiling((double)count/ pageSize);

    var data =
        aggregation
            .First()
            .Facets
            .First(x => x.Name == dataFacetName)
            .Output<TDocument>();

    var result =
        new PagedResult<TDocument>
        {
            Items = data,
            PageNumber = pageNumber,
            PageSize = pageSize,
            TotalPages = totalPages,
            TotalCount = count.Value
        };

    return result;
}

---

UPDATE 2021-07-06

This is my workaround, which queries twice the database because I need the total count of items matching the filter criteria.

public async Task<PagedResult<TDocument>> GetAll<TDocument>(
    Expression<Func<TDocument, bool>> filter,
    Sort<TDocument> sort,
    Pagination pagination,
    CancellationToken cancellationToken = new())
    where TDocument : class, IDocument
{
    filter ??= doc => true;
    sort ??= Sort<TDocument>.Default;
    if (pagination == null) throw new ArgumentNullException(nameof(pagination));

    var collection = _collectionRetrieverService.GetCollection<TDocument>();

    var sortDefinition = GetSortDefinition(sort);
    
    var pageNumber = pagination.PageNumber;
    var pageSize = pagination.PageSize;
    
    var skipCount = pageSize * (pageNumber - 1);
    
    var count = await collection.CountDocumentsAsync(filter, cancellationToken: cancellationToken);
    
    if (count == 0)
    {
        var emptyResult =
            new PagedResult<TDocument>()
            {
                Items = Enumerable.Empty<TDocument>(),
                PageNumber = pageNumber,
                PageSize = pageSize,
                TotalPages = pageNumber,
                TotalCount = count
            };
        return emptyResult;
    }
    
    var totalPages = (int)Math.Ceiling((double)count/ pageSize);
    
    var findOptions =
        new FindOptions<TDocument,TDocument>
        {
            AllowPartialResults = false,
            Sort = sortDefinition,
            Limit = pageSize,
            Skip = skipCount
            // Collation is not currently supported in AWS DocumentDB. See https://docs.aws.amazon.com/documentdb/latest/developerguide/mongo-apis.html#mongo-apis-index
            //Collation = CollationOptions.CaseInsensitiveCollation
        };
    var queryResult = await collection.FindAsync(filter, findOptions, cancellationToken);
    var results = await queryResult.ToListAsync(cancellationToken: cancellationToken);
    
    var result =
        new PagedResult<TDocument>
        {
            Items = results,
            PageNumber = pageNumber,
            PageSize = pageSize,
            TotalPages = totalPages,
            TotalCount = count
        };

    return result;
}