Azure Cosmos DB: Develop with the Table API in .NET

Azure Cosmos DB is Microsoft’s globally distributed multi-model database service. You can quickly create and query document, key/value, and graph databases, all of which benefit from the global distribution and horizontal scale capabilities at the core of Azure Cosmos DB.

This tutorial covers the following tasks:

  • Create an Azure Cosmos DB account
  • Enable functionality in the app.config file
  • Create a table using the Table API
  • Add an entity to a table
  • Insert a batch of entities
  • Retrieve a single entity
  • Query entities using automatic secondary indexes
  • Replace an entity
  • Delete an entity
  • Delete a table

Tables in Azure Cosmos DB

Azure Cosmos DB provides the Table API for applications that need a key-value store with a schema-less design. Both Azure Cosmos DB Table API and Azure Table storage now support the same SDKs and REST APIs. You can use Azure Cosmos DB to create tables with high throughput requirements.

This tutorial is for developers who are familiar with the Azure Table storage SDK, and would like to use the premium features available with Azure Cosmos DB. It is based on Get Started with Azure Table storage using .NET and shows how to take advantage of additional capabilities like secondary indexes, provisioned throughput, and multi-homing. This tutorial describes how to use the Azure portal to create an Azure Cosmos DB account, and then build and deploy a Table API application. We also walk through .NET examples for creating and deleting a table, and inserting, updating, deleting, and querying table data.

If you currently use Azure Table storage, you gain the following benefits with Azure Cosmos DB Table API:

This tutorial covers Azure Cosmos DB Table API using the .NET SDK. You can download the Azure Storage Preview SDK from NuGet.

To learn more about complex Azure Table storage tasks, see:

About this tutorial

This tutorial is for developers who are familiar with the Azure Table storage SDK, and would like to use the premium features available using Azure Cosmos DB. It is based on Get Started with Azure Table storage using .NET and shows how to take advantage of additional capabilities like secondary indexes, provisioned throughput, and multi-homing. We cover how to use the Azure portal to create an Azure Cosmos DB account, and then build and deploy a Table application. We also walk through .NET examples for creating and deleting a table, and inserting, updating, deleting, and querying table data.

If you don't already have Visual Studio 2017 installed, you can download and use the free Visual Studio 2017 Community Edition. Make sure that you enable Azure development during the Visual Studio setup.

If you don't have an Azure subscription, create a free account before you begin.

Create a database account

Let's start by creating an Azure Cosmos DB account in the Azure portal.

  1. In a new browser window, sign in to the Azure portal.
  2. In the left menu, click New, click Databases, and then under Azure Cosmos DB, click Create.

    Screen shot of the Azure portal, highlighting More Services, and Azure Cosmos DB

  3. In the New account page, enter the settings for the new Azure Cosmos DB account.

    Setting Suggested value Description
    ID Enter a unique name Enter a unique name to identify this Azure Cosmos DB account. Because documents.azure.com is appended to the ID that you provide to create your URI, use a unique but identifiable ID.

    The ID can contain only lowercase letters, numbers, and the hyphen (-) character, and it must contain 3 to 50 characters.
    API Azure Table The API determines the type of account to create. Azure Cosmos DB provides five APIs to suits the needs of your application: SQL (document database), Gremlin (graph database), MongoDB (document database), Azure Table, and Cassandra, each which currently require a separate account.

    Select Azure Table because in this quickstart you are creating a table that works with the Table API.

    Learn more about the Table API
    Subscription Enter the same unique name as provided above in ID Select Azure subscription that you want to use for this Azure Cosmos DB account.
    Resource Group The same value as ID Enter a new resource-group name for your account. For simplicity, you can use the same name as your ID.
    Location Select the region closest to your users Select geographic location in which to host your Azure Cosmos DB account. Use the location that's closest to your users to give them the fastest access to the data.
    Enable geo-redundancy Leave blank This creates a replicated version of your database in a second (paired) region. Leave this blank.
    Pin to dashboard Select Select this box so that your new database account is added to your portal dashboard for easy access.

    Then click Create.

    Screen shot of the New Azure Cosmos DB blade

  4. The account creation takes a few minutes. During account creation the portal displays the Deploying Azure Cosmos DB tile.

    The Azure portal Notifications pane

    Once the account is created, the Congratulations! Your Azure Cosmos DB account was created page is displayed.

Clone the sample application

Now let's clone a Table app from github, set the connection string, and run it. You'll see how easy it is to work with data programmatically.

  1. Open a git terminal window, such as git bash, and use the cd command to change to a folder to install the sample app.

    cd "C:\git-samples"
    
  2. Run the following command to clone the sample repository. This command creates a copy of the sample app on your computer.

    git clone https://github.com/Azure-Samples/azure-cosmos-db-table-dotnet-getting-started.git
    
  3. Then open the solution file in Visual Studio.

Update your connection string

Now go back to the Azure portal to get your connection string information and copy it into the app. This enables your app to communicate with your hosted database.

  1. In the Azure portal, click Connection String.

    Use the copy buttons on the right side of the screen to copy the CONNECTION STRING.

    View and copy the CONNECTION STRING in the Connection String pane

  2. In Visual Studio, open the app.config file.

  3. Paste the CONNECTION STRING value into the app.config file as the value of the CosmosDBStorageConnectionString.

    <add key="CosmosDBStorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=MYSTORAGEACCOUNT;AccountKey=AUTHKEY;TableEndpoint=https://account-name.table.cosmosdb.net" />

    Note

    To use this app with Azure Table storage, you need to change the connection string in app.config file. Use the account name as Table-account name and key as Azure Storage Primary key.
    <add key="StandardStorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=account-name;AccountKey=account-key;EndpointSuffix=core.windows.net" />

  4. Save the app.config file.

You've now updated your app with all the info it needs to communicate with Azure Cosmos DB.

Azure Cosmos DB capabilities

Azure Cosmos DB supports a number of capabilities that are not available in the Azure Table storage API.

Certain functionality is accessed via new overloads to CreateCloudTableClient that enable one to specify connection policy and consistency level.

Table Connection Settings Description
Connection Mode Azure Cosmos DB supports two connectivity modes. In Gateway mode, requests are always made to the Azure Cosmos DB gateway, which forwards it to the corresponding data partitions. In Direct connectivity mode, the client fetches the mapping of tables to partitions, and requests are made directly against data partitions. We recommend Direct, the default.
Connection Protocol Azure Cosmos DB supports two connection protocols - Https and Tcp. Tcp is the default, and recommended because it is more lightweight.
Preferred Locations Comma-separated list of preferred (multi-homing) locations for reads. Each Azure Cosmos DB account can be associated with 1-30+ regions. Each client instance can specify a subset of these regions in the preferred order for low latency reads. The regions must be named using their display names, for example, West US. Also see Multi-homing APIs.
Consistency Level You can trade off between latency, consistency, and availability by choosing between five well-defined consistency levels: Strong, Session, Bounded-Staleness, ConsistentPrefix, and Eventual. Default is Session. The choice of consistency level makes a significant performance difference in multi-region setups. See Consistency levels for details.

Other functionality can be enabled via the following appSettings configuration values.

Key Description
TableThroughput Reserved throughput for the table expressed in request units (RU) per second. Single tables can support 100s-millions of RU/s. See Request units. Default is 400
TableIndexingPolicy JSON string conforming to the indexing policy specification. See Indexing Policy to see how you can change indexing policy to include/exclude specific columns.
TableQueryMaxItemCount Configure the maximum number of items returned per table query in a single round trip. Default is -1, which lets Azure Cosmos DB dynamically determine the value at runtime.
TableQueryEnableScan If the query cannot use the index for any filter, then run it anyway via a scan. Default is false.
TableQueryMaxDegreeOfParallelism The degree of parallelism for execution of a cross-partition query. 0 is serial with no pre-fetching, 1 is serial with pre-fetching, and higher values increase the rate of parallelism. Default is -1, which lets Azure Cosmos DB dynamically determine the value at runtime.

To change the default value, open the app.config file from Solution Explorer in Visual Studio. Add the contents of the <appSettings> element shown below. Replace account-name with the name of your storage account, and account-key with your account access key.

<configuration>
    ...
    <appSettings>
      <!-- Client options -->
      <add key="CosmosDBStorageConnectionString" 
        value="DefaultEndpointsProtocol=https;AccountName=MYSTORAGEACCOUNT;AccountKey=AUTHKEY;TableEndpoint=https://account-name.table.cosmosdb.azure.com" />
      <add key="StorageConnectionString" value="DefaultEndpointsProtocol=https;AccountName=account-name;AccountKey=account-key; TableEndpoint=https://account-name.documents.azure.com" />

      <!--Table creation options -->
      <add key="TableThroughput" value="700"/>
      <add key="TableIndexingPolicy" value="{""indexingMode"": ""Consistent""}"/>

      <!-- Table query options -->
      <add key="TableQueryMaxItemCount" value="-1"/>
      <add key="TableQueryEnableScan" value="false"/>
      <add key="TableQueryMaxDegreeOfParallelism" value="-1"/>
      <add key="TableQueryContinuationTokenLimitInKb" value="16"/>

    </appSettings>
</configuration>

Let's make a quick review of what's happening in the app. Open the Program.cs file and you will find that these lines of code create the Table resources.

Create the table client

You initialize a CloudTableClient to connect to the table account.

CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

This client is initialized using the TableConnectionMode, TableConnectionProtocol, TableConsistencyLevel, and TablePreferredLocations configuration values if specified in the app settings.

Create a table

Then, you create a table using CloudTable. Tables in Azure Cosmos DB can scale independently in terms of storage and throughput, and partitioning is handled automatically by the service. Azure Cosmos DB supports both fixed size and unlimited tables. See Partitioning in Azure Cosmos DB for details.

CloudTable table = tableClient.GetTableReference("people");

table.CreateIfNotExists();

There is an important difference in how tables are created. Azure Cosmos DB reserves throughput, unlike Azure storage's consumption-based model for transactions. Your throughput is dedicated/reserved, so you never get throttled if your request rate is at or below your provisioned throughput.

You can configure the default throughput by configuring the setting for TableThroughput in terms of RU (request units) per second.

A read of a 1-KB entity is normalized as 1 RU, and other operations are normalized to a fixed RU value based on their CPU, memory, and IOPS consumption. Learn more about Request units in Azure Cosmos DB and specifically for key value stores.

Next, we walk through the simple read and write (CRUD) operations using the Azure Table storage SDK. This tutorial demonstrates predictable low single-digit millisecond latencies and fast queries provided by Azure Cosmos DB.

Add an entity to a table

Entities in Azure Table storage extend from the TableEntity class and must have PartitionKey and RowKey properties. Here's a sample definition for a customer entity.

public class CustomerEntity : TableEntity
{
    public CustomerEntity(string lastName, string firstName)
    {
        this.PartitionKey = lastName;
        this.RowKey = firstName;
    }

    public CustomerEntity() { }

    public string Email { get; set; }

    public string PhoneNumber { get; set; }
}

The following snippet shows how to insert an entity with the Azure storage SDK. Azure Cosmos DB is designed for guaranteed low latency at any scale, across the world.

Writes complete <15 ms at p99 and ~6 ms at p50 for applications running in the same region as the Azure Cosmos DB account. And this duration accounts for the fact that writes are acknowledged back to the client only after they are synchronously replicated, durably committed, and all content is indexed.

// Create a new customer entity.
CustomerEntity customer1 = new CustomerEntity("Harp", "Walter");
customer1.Email = "Walter@contoso.com";
customer1.PhoneNumber = "425-555-0101";

// Create the TableOperation object that inserts the customer entity.
TableOperation insertOperation = TableOperation.Insert(customer1);

// Execute the insert operation.
table.Execute(insertOperation);

Insert a batch of entities

Azure Table storage supports a batch operation API, that lets you combine updates, deletes, and inserts in the same batch operation.

// Create the batch operation.
TableBatchOperation batchOperation = new TableBatchOperation();

// Create a customer entity and add it to the table.
CustomerEntity customer1 = new CustomerEntity("Smith", "Jeff");
customer1.Email = "Jeff@contoso.com";
customer1.PhoneNumber = "425-555-0104";

// Create another customer entity and add it to the table.
CustomerEntity customer2 = new CustomerEntity("Smith", "Ben");
customer2.Email = "Ben@contoso.com";
customer2.PhoneNumber = "425-555-0102";

// Add both customer entities to the batch insert operation.
batchOperation.Insert(customer1);
batchOperation.Insert(customer2);

// Execute the batch operation.
table.ExecuteBatch(batchOperation);

Retrieve a single entity

Retrieves (GETs) in Azure Cosmos DB complete <10 ms at p99 and ~1 ms at p50 in the same Azure region. You can add as many regions to your account for low latency reads, and deploy applications to read from their local region ("multi-homed") by setting TablePreferredLocations.

You can retrieve a single entity using the following snippet:

// Create a retrieve operation that takes a customer entity.
TableOperation retrieveOperation = TableOperation.Retrieve<CustomerEntity>("Smith", "Ben");

// Execute the retrieve operation.
TableResult retrievedResult = table.Execute(retrieveOperation);

Tip

Learn about multi-homing APIs at Developing with multiple regions

Query entities using automatic secondary indexes

Tables can be queried using the TableQuery class. Azure Cosmos DB has a write-optimized database engine that automatically indexes all columns within your table. Indexing in Azure Cosmos DB is agnostic to schema. Therefore, even if your schema is different between rows, or if the schema evolves over time, it is automatically indexed. Since Azure Cosmos DB supports automatic secondary indexes, queries against any property can use the index and be served efficiently.

CloudTable table = tableClient.GetTableReference("people");

// Filter against a property that's not partition key or row key
TableQuery<CustomerEntity> emailQuery = new TableQuery<CustomerEntity>().Where(
    TableQuery.GenerateFilterCondition("Email", QueryComparisons.Equal, "Ben@contoso.com"));

foreach (CustomerEntity entity in table.ExecuteQuery(emailQuery))
{
    Console.WriteLine("{0}, {1}\t{2}\t{3}", entity.PartitionKey, entity.RowKey,
        entity.Email, entity.PhoneNumber);
}

Azure Cosmos DB supports the same query functionality as Azure Table storage for the Table API. Azure Cosmos DB also supports sorting, aggregates, geospatial query, hierarchy, and a wide range of built-in functions. The additional functionality will be provided in the Table API in a future service update. See Azure Cosmos DB query for an overview of these capabilities.

Replace an entity

To update an entity, retrieve it from the Table service, modify the entity object, and then save the changes back to the Table service. The following code changes an existing customer's phone number.

TableOperation updateOperation = TableOperation.Replace(updateEntity);
table.Execute(updateOperation);

Similarly, you can perform InsertOrMerge or Merge operations.

Delete an entity

You can easily delete an entity after you have retrieved it by using the same pattern shown for updating an entity. The following code retrieves and deletes a customer entity.

TableOperation deleteOperation = TableOperation.Delete(deleteEntity);
table.Execute(deleteOperation);

Delete a table

Finally, the following code example deletes a table from a storage account. You can delete and recreate a table immediately with Azure Cosmos DB.

CloudTable table = tableClient.GetTableReference("people");
table.DeleteIfExists();

Clean up resources

If you're not going to continue to use this app, use the following steps to delete all resources created by this tutorial in the Azure portal.

  1. From the left-hand menu in the Azure portal, click Resource groups and then click the name of the resource you created.
  2. On your resource group page, click Delete, type the name of the resource to delete in the text box, and then click Delete.

Next steps

In this tutorial, we covered how to get started using Azure Cosmos DB with the Table API, and you've done the following:

  • Created an Azure Cosmos DB account
  • Enabled functionality in the app.config file
  • Created a table
  • Added an entity to a table
  • Inserted a batch of entities
  • Retrieved a single entity
  • Queried entities using automatic secondary indexes
  • Replaced an entity
  • Deleted an entity
  • Deleted a table

You can now proceed to the next tutorial and learn more about querying table data.