Paginate cross-product query results with the Azure Cosmos DB for NoSQL SDK

Azure Cosmos DB queries will typically have multiple pages of results. Pagination is done automatically server-side when Azure Cosmos DB cannot return all query results in one single execution. In many applications, you will want to write code using the SDK to process your query results in batches in a performant manner.

In this lab, you’ll create a feed iterator that can be used in a loop to iterate over your entire result set.

Prepare your development environment

If you have not already cloned the lab code repository for DP-420 to the environment where you’re working on this lab, follow these steps to do so. Otherwise, open the previously cloned folder in Visual Studio Code.

Start Visual Studio Code.

📝 If you are not already familiar with the Visual Studio Code interface, review the Get Started guide for Visual Studio Code
Open the command palette and run Git: Clone to clone the https://github.com/microsoftlearning/dp-420-cosmos-db-dev GitHub repository in a local folder of your choice.

💡 You can use the CTRL+SHIFT+P keyboard shortcut to open the command palette.
Once the repository has been cloned, open the local folder you selected in Visual Studio Code.

Create an Azure Cosmos DB for NoSQL account

Azure Cosmos DB is a cloud-based NoSQL database service that supports multiple APIs. When provisioning an Azure Cosmos DB account for the first time, you will select which of the APIs you want the account to support (for example, Mongo API or NoSQL API). Once the Azure Cosmos DB for NoSQL account is done provisioning, you can retrieve the endpoint and key and use them to connect to the Azure Cosmos DB for NoSQL account using the Azure SDK for .NET or any other SDK of your choice.

In a new web browser window or tab, navigate to the Azure portal (portal.azure.com).
Sign into the portal using the Microsoft credentials associated with your subscription.

Select + Create a resource, search for Cosmos DB, and then create a new Azure Cosmos DB for NoSQL account resource with the following settings, leaving all remaining settings to their default values:

Setting	Value
Subscription	Your existing Azure subscription
Resource group	Select an existing or create a new resource group
Account Name	Enter a globally unique name
Location	Choose any available region
Capacity mode	Provisioned throughput
Apply Free Tier Discount	Do Not Apply

📝 Your lab environments may have restrictions preventing you from creating a new resource group. If that is the case, use the existing pre-created resource group.

Wait for the deployment task to complete before continuing with this task.
Go to the newly created Azure Cosmos DB account resource and navigate to the Keys pane.
This pane contains the connection details and credentials necessary to connect to the account from the SDK. Specifically:
1. Notice the URI field. You will use this endpoint value later in this exercise.
2. Notice the PRIMARY KEY field. You will use this key value later in this exercise.
Return to Visual Studio Code.

Seed the Azure Cosmos DB for NoSQL account with data

The cosmicworks command-line tool deploys sample data to any Azure Cosmos DB for NoSQL account. The tool is open-source and available through NuGet. You will install this tool to the Azure Cloud Shell and then use it to seed your database.

In Visual Studio Code, open the Terminal menu and then select New Terminal to open a new terminal instance.
Install the cosmicworks command-line tool for global use on your machine.
```
 dotnet tool install cosmicworks --global --version 1.*
```
💡 This command may take a couple of minutes to complete. This command will output the warning message (*Tool ‘cosmicworks’ is already installed’) if you have already installed the latest version of this tool in the past.

Run cosmicworks to seed your Azure Cosmos DB account with the following command-line options:

Option	Value
–endpoint	The endpoint value you copied earlier in this lab
–key	The key value you coped earlier in this lab
–datasets	product

 cosmicworks --endpoint <cosmos-endpoint> --key <cosmos-key> --datasets product

📝 For example, if your endpoint is: https://dp420.documents.azure.com:443/ and your key is: fDR2ci9QgkdkvERTQ==, then the command would be: cosmicworks --endpoint https://dp420.documents.azure.com:443/ --key fDR2ci9QgkdkvERTQ== --datasets product

Wait for the cosmicworks command to finish populating the account with a database, container, and items.
Close the integrated terminal.

Paginate through small result sets of a SQL query using the SDK

When processing query results, you must make sure your code progresses through all pages of results and checks to see if any more pages are remaining before making subsequent requests.

In Visual Studio Code, in the Explorer pane, browse to the 10-paginate-results-sdk folder.
Open the product.cs code file.
Observe the Product class and its corresponding properties. Specifically, this lab will use the id, name, and price properties.
Back in the Explorer pane of Visual Studio Code, open the script.cs code file.
Update the existing variable named endpoint with its value set to the endpoint of the Azure Cosmos DB account you created earlier.
```
 string endpoint = "<cosmos-endpoint>";
```
📝 For example, if your endpoint is: https://dp420.documents.azure.com:443/, then the C# statement would be: string endpoint = “https://dp420.documents.azure.com:443/”;.
Update the existing variable named key with its value set to the key of the Azure Cosmos DB account you created earlier.
```
 string key = "<cosmos-key>";
```
📝 For example, if your key is: fDR2ci9QgkdkvERTQ==, then the C# statement would be: string key = “fDR2ci9QgkdkvERTQ==”;.
Create a new variable named sql of type string with a value of SELECT p.id, p.name, p.price FROM products p:
```
 string sql = "SELECT p.id, p.name, p.price FROM products p ";
```
Create a new variable of type QueryDefinition passing in the sql variable as a parameter to the constructor:
```
 QueryDefinition query = new (sql);
```
Create a new variable of type QueryRequestOptions named options using the default empty constructor:
```
 QueryRequestOptions options = new ();
```
Set the MaxItemCount property of the options variable to a value of 50:
```
 options.MaxItemCount = 50;
```
Create a new variable named iterator of type FeedIterator<> by invoking the generic GetItemQueryIterator method of the Container class passing in the query and options variables as parameters:
```
 FeedIterator<Product> iterator = container.GetItemQueryIterator<Product>(query, requestOptions: options);
```
Create a while loop that checks the HasMoreResults property of the iterator variable:
```
 while (iterator.HasMoreResults)
 {
        
 }
```
Within the while loop, asynchronously invoke the ReadNextAsync method of the iterator variable storing the result in a variable named products of generic type FeedResponse using the Product class:
```
 FeedResponse<Product> products = await iterator.ReadNextAsync();
```
Still within the while loop, create a new foreach loop by iterating over the products variable using the variable product to represent an instance of type Product:
```
 foreach (Product product in products)
 {

 }
```
Within the foreach loop, use the built-in Console.WriteLine static method to format and print the id, name, and price properties of the product variable:
```
 Console.WriteLine($"[{product.id}]\t[{product.name,40}]\t[{product.price,10}]");
```
Back within the while loop, use the built-in Console.WriteLine static method to print the message Press any key to get more results:
```
 Console.WriteLine("Press any key to get more results");
```
Still within the while loop, use the built-in Console.ReadKey static method to listen for the next keypress input:
```
 Console.ReadKey();
```

Once you are done, your code file should now include:

 using System;
 using Microsoft.Azure.Cosmos;

 string endpoint = "<cosmos-endpoint>";

 string key = "<cosmos-key>";

 CosmosClient client = new CosmosClient(endpoint, key);

 Database database = await client.CreateDatabaseIfNotExistsAsync("cosmicworks");

 Container container = await database.CreateContainerIfNotExistsAsync("products", "/categoryId");

 string sql = "SELECT p.id, p.name, p.price FROM products p ";
 QueryDefinition query = new (sql);

 QueryRequestOptions options = new ();
 options.MaxItemCount = 50;

 FeedIterator<Product> iterator = container.GetItemQueryIterator<Product>(query, requestOptions: options);

 while (iterator.HasMoreResults)
 {
     FeedResponse<Product> products = await iterator.ReadNextAsync();
     foreach (Product product in products)
     {
         Console.WriteLine($"[{product.id}]\t[{product.name,40}]\t[{product.price,10}]");
     }

     Console.WriteLine("Press any key for next page of results");
     Console.ReadKey();        
 }

Save the script.cs file.
In Visual Studio Code, open the context menu for the 10-paginate-results-sdk folder and then select Open in Integrated Terminal to open a new terminal instance.
Build and run the project using the dotnet run command:
```
 dotnet run
```
The script will now output the first set of 50 items that match the query. Press any key to get the next set of 50 items until the query has iterated over all matching items.

💡 The query will match hundreds of items in the products container.
Close the integrated terminal.
Close Visual Studio Code.