Execute a query with the Azure Cosmos DB for NoSQL SDK

The latest version of the .NET SDK for Azure Cosmos DB for NoSQL makes it easier than ever to query a container and asynchronously iterate over result sets using the latest best practices and language features from C#.

This library has special functionality to make it easier to query Azure Cosmos DB using [https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.cosmos.feediterator?view=azure-dotnet].

In this lab, you’ll use an asynchronous stream to iterate over a large result set returned from Azure Cosmos DB for NoSQL. You will use the .NET SDK to query and iterate over results.

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 forNoSQL 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.

Iterate over the results of a SQL query using the SDK

You will now use an asynchronous stream to create a simple-to-understand foreach loop over paginated results from Azure Cosmos DB. Behind the scenes, the SDK will manage the feed iterator and making sure subsequent requests are invoked correctly.

In Visual Studio Code, in the Explorer pane, browse to the 09-execute-query-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==”;.
Let’s append some additional code to the end of the the script.cs file, create a new variable named sql of type string with a value of SELECT * FROM products p:
```
 string sql = "SELECT * 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 while loop by invoking the generic GetItemQueryIterator method of the CosmosContainer class passing in the query variable as a parameter, and then iterating over the results:
```
 using FeedIterator<Product> feed = container.GetItemQueryIterator<Product>(
     queryDefinition: query
 );

 while (feed.HasMoreResults)
 {
 }
```

Within the while loop, read the next result asynchronously, use the built-in Console.WriteLine static method to format and print the id, name, and price properties of the product variable:

 FeedResponse<Product> response = await feed.ReadNextAsync();
 foreach (Product product in response)
 {
     Console.WriteLine($"[{product.id}]\t{product.name,35}\t{product.price,15:C}");
 }

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 (endpoint, key);

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

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

 string sql = "SELECT * FROM products p";
 QueryDefinition query = new (sql);

 using FeedIterator<Product> feed = container.GetItemQueryIterator<Product>(
     queryDefinition: query
 );

 while (feed.HasMoreResults)
 {
     FeedResponse<Product> response = await feed.ReadNextAsync();
     foreach (Product product in response)
     {
         Console.WriteLine($"[{product.id}]\t{product.name,35}\t{product.price,15:C}");
     }
 }

Save the script.cs file.
In Visual Studio Code, open the context menu for the 09-execute-query-sdk folder and then select Open in Integrated Terminal to open a new terminal instance.
Add the [Microsoft.Azure.Cosmos][nuget.org/packages/microsoft.azure.cosmos/3.22.1] package from NuGet using the following command:
```
 dotnet add package Microsoft.Azure.Cosmos --version 3.22.1
```
Build and run the project using the dotnet run command:
```
 dotnet run
```
The script will now output every product in the container
Close the integrated terminal.
Close Visual Studio Code.