How to allow Odata options to be applied to cosmos db collection and return filtered records

Question

I have a requirement to implement paging on api. Am doing below to enable OData querying on api, but it filters after fetching all records from cosmos db, but I would like to fetch filtered records from cosmos db. How to convert Odata query options to cosmos db query options?

 controller.cs

            [Route("apps")]
            [HttpGet]
            [EnableQuery]
            public async Task<IActionResult> GetAllApps(CancellationToken cancellationToken)
            {
                var user = this.GetUser();
                var results = await this.appRepository.GetAppsForUserAsync(user, cancellationToken).ConfigureAwait(false);
                return this.Ok(this.mapper.Map<AppHeader[]>(results));
            }

AppRepository.cs
 public async Task<IEnumerable<App>> GetAppsForUserAsync(User user, CancellationToken cancellationToken)
        {            
            try
            {
                FeedOptions queryOptions = new FeedOptions
                {
                    MaxItemCount = -1,
                    PartitionKey = new PartitionKey(user)
                };

                var query = this.factory.GetClient()
                    .CreateDocumentQuery<App>(
                        UriFactory.CreateDocumentCollectionUri(DatabaseName, CollectionName),
                        queryOptions)
                    .Where(resource => resource.UserList.Any(u => u.userId == user.Id))
                    .AsDocumentQuery();

                List<App> results = new List<App>();
                while (query.HasMoreResults)
                {
                    cancellationToken.ThrowIfCancellationRequested();

                    var response = await query.ExecuteNextAsync<App>(cancellationToken).ConfigureAwait(false);

                    var app = this.mapper.Map<App[]>(response);
                    results.AddRange(app);
                }

                return results;
            }
            catch (DocumentClientException ex)
            {
                this.logger.LogError(ex, ex.Message);
                throw;
            }
        }

Client.js

http://localhost:8303/api/appdefinitions/my?$skip=6&$top=4`

Answer 1

In the repository, try using GetItemLinqQueryable and query on that. Note I am using newer V3 SDK Microsoft.Azure.Cosmos here, but equivalent should exist in older SDK too.

// Take `skip` and `top` params as input to the repository method.

List<App> results = new List<App>();
using (CosmosClient client = new CosmosClient(endpoint, authKey))
{
  Database cosmosDatabase = await client.CreateDatabaseIfNotExistsAsync(DatabaseName);
  Container container = await Program.GetOrCreateContainerAsync(cosmosDatabase, CollectionName);
  // LINQ query generation
  using (FeedIterator setIterator = container.GetItemLinqQueryable<App>()
                     .Where(a => a.userId == user.Id)
                     .Skip(skip)
                     .Take(top)
                     .ToFeedIterator())
  {                   
      //Asynchronous query execution
      while (setIterator.HasMoreResults)
      {
          var items = await feedIterator.ReadNextAsync().ConfigureAwait(false);
          results.AddRange(items);
      }
  }
}

NOTE: Ideally in real application you should reuse CosmosClient instance instead of creating every time for performance improvement.