'How to get total count from a filtered FeedIterator in Cosmos DB

I have the following code to filter some data in a Cosmos DB:

Container container = await service.GetContainer(containerName, partitionKeyA);
using (FeedIterator<T> resultSet = container.GetItemQueryIterator<T>(
    queryDefinition: GetComplexSQLQueryDefinition(),
    paginationInfo.Token,
    requestOptions: new QueryRequestOptions()
    {
      PartitionKey = new PartitionKey(partitionKey),
      MaxItemCount = 10
    }
    ))
{ 
  FeedResponse<T> response = await resultSet.ReadNextAsync();
  //get total count
  int totalCount = -1;
}

The query can yield many records, hence I need the pagination.

Unfortunately I need the total count of the items - and it can vary according to the filtering.

According to this answer, I have only 2 options:

  1. I create a second SQL query that uses an SQL select command to count the records - then query the database again to select the actual records:

    var query = new QueryDefinition("SELECT value count(1) FROM c WHERE c.tenantId = @type");
    query.WithParameter("@type", '5d484526d76e9653e6226aa2');
    var container = client.GetContainer("DatabaseName", "CollectionName");
    var iterator = container.GetItemQueryIterator<int>(query);
    var count = 0;
    while (iterator.HasMoreResults)
    {
        var currentResultSet = await iterator.ReadNextAsync();
        foreach (var res in currentResultSet)
        {
            count += res;
        }
    }
    Console.WriterLine($"The first count is: {count}");
    
  2. I translate my complex SQL query to LINQ and use its Count method:

    //should be formatted as code

    var count = container.GetItemLinqQueryable(true) .Count(item => item.tenantId.Equals('5d484526d76e9653e6226aa2'));

Both seems quite cumbersome for such a simple task so I wonder if there is any better or more effective approach.

What else could I try?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source