Quickstart: Build a Table API app with .NET SDK and Azure Cosmos DB

APPLIES TO: Table API

This quickstart shows how to access the Azure Cosmos DB Table API from a .NET application. The Cosmos DB Table API is a schemaless data store allowing applications to store structured NoSQL data in the cloud. Because data is stored in a schemaless design, new properties (columns) are automatically added to the table when an object with a new attribute is added to the table.

.NET applications can access the Cosmos DB Table API using the Azure.Data.Tables NuGet package. The Azure.Data.Tables package is a .NET Standard 2.0 library that works with both .NET Framework (4.7.2 and later) and .NET Core (2.0 and later) applications.

Prerequisites

The sample application is written in .NET Core 3.1, though the principles apply to both .NET Framework and .NET Core applications. You can use either Visual Studio, Visual Studio for Mac, or Visual Studio Code as an IDE.

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

Sample application

The sample application for this tutorial may be cloned or downloaded from the repository https://github.com/Azure-Samples/msdocs-azure-data-tables-sdk-dotnet. Both a starter and completed app are included in the sample repository.

git clone https://github.com/Azure-Samples/msdocs-azure-data-tables-sdk-dotnet

The sample application uses weather data as an example to demonstrate the capabilities of the Table API. Objects representing weather observations are stored and retrieved using the Table API, including storing objects with additional properties to demonstrate the schemaless capabilities of the Table API.

1 - Create an Azure Cosmos DB account

You first need to create a Cosmos DB Tables API account that will contain the table(s) used in your application. This can be done using the Azure portal, Azure CLI, or Azure PowerShell.

• Azure portal
• Azure CLI
• Azure PowerShell

Log in to the Azure portal and follow these steps to create an Cosmos DB account.

Instructions	Screenshot
In the Azure portal: In the search bar at the top of the Azure portal, enter "cosmos db". On the menu that appears below the search bar, under Services, select the item labeled Azure Cosmos DB.
On the Azure Cosmos DB page select +Create.
On the Select API option page choose the Azure Table option.
On the Create Azure Cosmos DB Account - Azure Table page, fill out the form as follows. Create a new resource group for the storage account named rg-msdocs-tables-sdk-demo by selecting the Create new link under Resource group. Give your storage account a name of cosmos-msdocs-tables-sdk-demo-XYZ where XYZ are any three random characters to create a unique account name. Azure Cosmos DB account names must be between 3 and 44 characters in length and may contain only lowercase letters, numbers or the hyphen (-) character. Select the region for your storage account. Select Standard performance. Select Provisioned throughput for this example under Capacity mode. Select Apply under Apply Free Tier Discount for this example. Select the Review + create button at the bottom of the screen and then select "Create" on the summary screen to create your Azure Cosmos DB account. This process may take several minutes.

2 - Create a table

Next, you need to create a table within your Cosmos DB account for your application to use. Unlike a traditional database, you only need to specify the name of the table, not the properties (columns) in the table. As data is loaded into your table, the properties (columns) will be automatically created as needed.

• Azure portal
• Azure CLI
• Azure PowerShell

In the Azure portal, complete the following steps to create a table inside your Cosmos DB account.

Instructions	Screenshot
In the Azure portal, navigate to the overview page for the Azure Cosmos DB account. You can navigate to the overview page for your Cosmos DB account by typing the name (cosmos-msdocs-tables-sdk-demo-XYZ) of your Cosmos DB account in the top search bar and looking under the resources heading.Select the name of your Azure Cosmos DB account to go to the overview page.
On the overview page, select +Add Table. The New Table dialog will slide out from the right side of the page.
In the New Table dialog, fill out the form as follows. Enter the name WeatherData for the Table ID. This is the name of the table. Select Manual under Table throughput (autoscale) for this example. Use the default value of 400 under your estimated RU/s. Select the OK button to create the table.

3 - Get Cosmos DB connection string

To access your table(s) in Cosmos DB, your app will need the table connection string for the CosmosDB Storage account. The connection string can be retrieved using the Azure portal, Azure CLI or Azure PowerShell.

• Azure portal
• Azure CLI
• Azure PowerShell

Instructions	Screenshot
On the left hand side of the Azure Cosmos DB account page, locate the menu item named Connection String under the Settings header and select it. You will be taken to a page where you can retrieve the connection string for the storage account.
Copy the PRIMARY CONNECTION STRING value to use in your application.

The connection string for your Cosmos DB account is considered an app secret and must be protected like any other app secret or password. This example uses the Secret Manager tool to store the connection string during development and make it available to the application. The Secret Manager tool can be accessed from either Visual Studio or the .NET CLI.

{
  "ConnectionStrings": {
    "CosmosTableApi": "<cosmos db table connection string>"
  }  
}

dotnet user-secrets init

dotnet user-secrets set "ConnectionStrings:CosmosTableApi" "<cosmos db table connection string>"

4 - Install Azure.Data.Tables NuGet package

To access the Cosmos DB Table API from a .NET application, install the Azure.Data.Tables NuGet package.

Install-Package Azure.Data.Tables

dotnet add package Azure.Data.Tables

5 - Configure the Table client in Startup.cs

The Azure SDK communicates with Azure using client objects to execute different operations against Azure. The TableClient object is the object used to communicate with the Cosmos DB Table API.

An application will typically create a single TableClient object per table to be used throughout the application. It's recommended to use dependency injection (DI) and register the TableClient object as a singleton to accomplish this. For more information about using DI with the Azure SDK, see Dependency injection with the Azure SDK for .NET.

In the Startup.cs file of the application, edit the ConfigureServices() method to match the following code snippet:

public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages()
        .AddMvcOptions(options =>
        {
            options.Filters.Add(new ValidationFilter());
        });
    
    var connectionString = Configuration.GetConnectionString("CosmosTableApi");
    services.AddSingleton<TableClient>(new TableClient(connectionString, "WeatherData"));
    
    services.AddSingleton<TablesService>();
}

You will also need to add the following using statement at the top of the Startup.cs file.

using Azure.Data.Tables;

6 - Implement Cosmos DB table operations

All Cosmos DB table operations for the sample app are implemented in the TableService class located in the Services directory. You will need to import the Azure and Azure.Data.Tables namespaces at the top of this file to work with objects in the Azure.Data.Tables SDK package.

using Azure;
using Azure.Data.Tables;

At the start of the TableService class, add a member variable for the TableClient object and a constructor to allow the TableClient object to be injected into the class.

private TableClient _tableClient;

public TablesService(TableClient tableClient)
{
    _tableClient = tableClient;
}

Get rows from a table

The TableClient class contains a method named Query which allows you to select rows from the table. In this example, since no parameters are being passed to the method, all rows will be selected from the table.

The method also takes a generic parameter of type ITableEntity that specifies the model class data will be returned as. In this case, the built-in class TableEntity is used, meaning the Query method will return a Pageable<TableEntity> collection as its results.

public IEnumerable<WeatherDataModel> GetAllRows()
{
    Pageable<TableEntity> entities = _tableClient.Query<TableEntity>();

    return entities.Select(e => MapTableEntityToWeatherDataModel(e));
}

The TableEntity class defined in the Azure.Data.Tables package has properties for the partition key and row key values in the table. Together, these two values for a unique key for the row in the table. In this example application, the name of the weather station (city) is stored in the partition key and the date/time of the observation is stored in the row key. All other properties (temperature, humidity, wind speed) are stored in a dictionary in the TableEntity object.

It is common practice to map a TableEntity object to an object of your own definition. The sample application defines a class WeatherDataModel in the Models directory for this purpose. This class has properties for the station name and observation date that the partition key and row key will map to, providing more meaningful property names for these values. It then uses a dictionary to store all the other properties on the object. This is a common pattern when working with Table storage since a row can have any number of arbitrary properties and we want our model objects to be able to capture all of them. This class also contains methods to list the properties on the class.

public class WeatherDataModel 
{
    // Captures all of the weather data properties -- temp, humidity, wind speed, etc
    private Dictionary<string, object> _properties = new Dictionary<string, object>();

    public string StationName { get; set; }

    public string ObservationDate { get; set; }

    public DateTimeOffset? Timestamp { get; set; }

    public string Etag { get; set; }

    public object this[string name] 
    { 
        get => ( ContainsProperty(name)) ? _properties[name] : null; 
        set => _properties[name] = value; 
    }
    
    public ICollection<string> PropertyNames => _properties.Keys;

    public int PropertyCount => _properties.Count;

    public bool ContainsProperty(string name) => _properties.ContainsKey(name);       
}

The MapTableEntityToWeatherDataModel method is used to map a TableEntity object to a WeatherDataModel object. The TableEntity object contains a Keys property to get all of the property names contained in the table for the object (effectively the column names for this row in the table). The MapTableEntityToWeatherDataModel method directly maps the PartitionKey, RowKey, Timestamp, and Etag properties and then uses the Keys property to iterate over the other properties in the TableEntity object and map those to the WeatherDataModel object, minus the properties that have already been directly mapped.

Edit the code in the MapTableEntityToWeatherDataModel method to match the following code block.

public WeatherDataModel MapTableEntityToWeatherDataModel(TableEntity entity)
{
    WeatherDataModel observation = new WeatherDataModel();
    observation.StationName = entity.PartitionKey;
    observation.ObservationDate = entity.RowKey;
    observation.Timestamp = entity.Timestamp;
    observation.Etag = entity.ETag.ToString();

    var measurements = entity.Keys.Where(key => !EXCLUDE_TABLE_ENTITY_KEYS.Contains(key));
    foreach (var key in measurements)
    {
        observation[key] = entity[key];
    }
    return observation;            
}

Filter rows returned from a table

To filter the rows returned from a table, you can pass an OData style filter string to the Query method. For example, if you wanted to get all of the weather readings for Chicago between midnight July 1, 2021 and midnight July 2, 2021 (inclusive) you would pass in the following filter string.

PartitionKey eq 'Chicago' and RowKey ge '2021-07-01 12:00 AM' and RowKey le '2021-07-02 12:00 AM'

You can view all OData filter operators on the OData website in the section Filter System Query Option.

In the example application, the FilterResultsInputModel object is designed to capture any filter criteria provided by the user.

public class FilterResultsInputModel : IValidatableObject
{
    public string PartitionKey { get; set; }
    public string RowKeyDateStart { get; set; }
    public string RowKeyTimeStart { get; set; }
    public string RowKeyDateEnd { get; set; }
    public string RowKeyTimeEnd { get; set; }
    [Range(-100, +200)]
    public double? MinTemperature { get; set; }
    [Range(-100,200)]
    public double? MaxTemperature { get; set; }
    [Range(0, 300)]
    public double? MinPrecipitation { get; set; }
    [Range(0,300)]
    public double? MaxPrecipitation { get; set; }
}

When this object is passed to the GetFilteredRows method in the TableService class, it creates a filter string for each non-null property value. It then creates a combined filter string by joining all of the values together with an "and" clause. This combined filter string is passed to the Query method on the TableClient object and only rows matching the filter string will be returned. You can use a similar method in your code to construct suitable filter strings as required by your application.

public IEnumerable<WeatherDataModel> GetFilteredRows(FilterResultsInputModel inputModel)
{
    List<string> filters = new List<string>();

    if (!String.IsNullOrEmpty(inputModel.PartitionKey))
        filters.Add($"PartitionKey eq '{inputModel.PartitionKey}'");
    if (!String.IsNullOrEmpty(inputModel.RowKeyDateStart) && !String.IsNullOrEmpty(inputModel.RowKeyTimeStart))
        filters.Add($"RowKey ge '{inputModel.RowKeyDateStart} {inputModel.RowKeyTimeStart}'");
    if (!String.IsNullOrEmpty(inputModel.RowKeyDateEnd) && !String.IsNullOrEmpty(inputModel.RowKeyTimeEnd))
        filters.Add($"RowKey le '{inputModel.RowKeyDateEnd} {inputModel.RowKeyTimeEnd}'");
    if (inputModel.MinTemperature.HasValue)
        filters.Add($"Temperature ge {inputModel.MinTemperature.Value}");
    if (inputModel.MaxTemperature.HasValue)
        filters.Add($"Temperature le {inputModel.MaxTemperature.Value}");
    if (inputModel.MinPrecipitation.HasValue)
        filters.Add($"Precipitation ge {inputModel.MinTemperature.Value}");
    if (inputModel.MaxPrecipitation.HasValue)
        filters.Add($"Precipitation le {inputModel.MaxTemperature.Value}");

    string filter = String.Join(" and ", filters);
    Pageable<TableEntity> entities = _tableClient.Query<TableEntity>(filter);

    return entities.Select(e => MapTableEntityToWeatherDataModel(e));
}

Insert data using a TableEntity object

The simplest way to add data to a table is by using a TableEntity object. In this example, data is mapped from an input model object to a TableEntity object. The properties on the input object representing the weather station name and observation date/time are mapped to the PartitionKey and RowKey properties respectively which together form a unique key for the row in the table. Then the additional properties on the input model object are mapped to dictionary properties on the TableEntity object. Finally, the AddEntity method on the TableClient object is used to insert data into the table.

Modify the InsertTableEntity class in the example application to contain the following code.

public void InsertTableEntity(WeatherInputModel model)
{
    TableEntity entity = new TableEntity();
    entity.PartitionKey = model.StationName;
    entity.RowKey = $"{model.ObservationDate} {model.ObservationTime}";

    // The other values are added like a items to a dictionary
    entity["Temperature"] = model.Temperature;
    entity["Humidity"] = model.Humidity;
    entity["Barometer"] = model.Barometer;
    entity["WindDirection"] = model.WindDirection;
    entity["WindSpeed"] = model.WindSpeed;
    entity["Precipitation"] = model.Precipitation;

    _tableClient.AddEntity(entity);
}

Upsert data using a TableEntity object

If you try to insert a row into a table with a partition key/row key combination that already exists in that table, you will receive an error. For this reason, it is often preferable to use the UpsertEntity instead of the AddEntity method when adding rows to a table. If the given partition key/row key combination already exists in the table, the UpsertEntity method will update the existing row. Otherwise, the row will be added to the table.

public void UpsertTableEntity(WeatherInputModel model)
{
    TableEntity entity = new TableEntity();
    entity.PartitionKey = model.StationName;
    entity.RowKey = $"{model.ObservationDate} {model.ObservationTime}";

    // The other values are added like a items to a dictionary
    entity["Temperature"] = model.Temperature;
    entity["Humidity"] = model.Humidity;
    entity["Barometer"] = model.Barometer;
    entity["WindDirection"] = model.WindDirection;
    entity["WindSpeed"] = model.WindSpeed;
    entity["Precipitation"] = model.Precipitation;

    _tableClient.UpsertEntity(entity);
}

Insert or upsert data with variable properties

One of the advantages of using the Cosmos DB Table API is that if an object being loaded to a table contains any new properties then those properties are automatically added to the table and the values stored in Cosmos DB. There is no need to run DDL statements like ALTER TABLE to add columns as in a traditional database.

This model gives your application flexibility when dealing with data sources that may add or modify what data needs to be captured over time or when different inputs provide different data to your application. In the sample application, we can simulate a weather station that sends not just the base weather data but also some additional values. When an object with these new properties is stored in the table for the first time, the corresponding properties (columns) will be automatically added to the table.

In the sample application, the ExpandableWeatherObject class is built around an internal dictionary to support any set of properties on the object. This class represents a typical pattern for when an object needs to contain an arbitrary set of properties.

public class ExpandableWeatherObject
{
    public Dictionary<string, object> _properties = new Dictionary<string, object>();

    public string StationName { get; set; }

    public string ObservationDate { get; set; }

    public object this[string name]
    {
        get => (ContainsProperty(name)) ? _properties[name] : null;
        set => _properties[name] = value;
    }

    public ICollection<string> PropertyNames => _properties.Keys;

    public int PropertyCount => _properties.Count;

    public bool ContainsProperty(string name) => _properties.ContainsKey(name);
}

To insert or upsert such an object using the Table API, map the properties of the expandable object into a TableEntity object and use the AddEntity or UpsertEntity methods on the TableClient object as appropriate.

public void InsertExpandableData(ExpandableWeatherObject weatherObject)
{
    TableEntity entity = new TableEntity();
    entity.PartitionKey = weatherObject.StationName;
    entity.RowKey = weatherObject.ObservationDate;

    foreach (string propertyName in weatherObject.PropertyNames)
    {
        var value = weatherObject[propertyName];
        entity[propertyName] = value;
    }
    _tableClient.AddEntity(entity);
}

        
public void UpsertExpandableData(ExpandableWeatherObject weatherObject)
{
    TableEntity entity = new TableEntity();
    entity.PartitionKey = weatherObject.StationName;
    entity.RowKey = weatherObject.ObservationDate;

    foreach (string propertyName in weatherObject.PropertyNames)
    {
        var value = weatherObject[propertyName];
        entity[propertyName] = value;
    }
    _tableClient.UpsertEntity(entity);
}

Update an entity

Entities can be updated by calling the UpdateEntity method on the TableClient object. Because an entity (row) stored using the Table API could contain any arbitrary set of properties, it is often useful to create an update object based around a Dictionary object similar to the ExpandableWeatherObject discussed earlier. In this case, the only difference is the addition of an Etag property which is used for concurrency control during updates.

public class UpdateWeatherObject
{
    public Dictionary<string, object> _properties = new Dictionary<string, object>();

    public string StationName { get; set; }
    public string ObservationDate { get; set; }
    public string Etag { get; set; }

    public object this[string name]
    {
        get => (ContainsProperty(name)) ? _properties[name] : null;
        set => _properties[name] = value;
    }

    public ICollection<string> PropertyNames => _properties.Keys;

    public int PropertyCount => _properties.Count;

    public bool ContainsProperty(string name) => _properties.ContainsKey(name);
}

In the sample app, this object is passed to the UpdateEntity method in the TableService class. This method first loads the existing entity from the Table API using the GetEntity method on the TableClient. It then updates that entity object and uses the UpdateEntity method save the updates to the database. Note how the UpdateEntity method takes the current Etag of the object to insure the object has not changed since it was initially loaded. If you want to update the entity regardless, you may pass a value of Etag.Any to the UpdateEntity method.

public void UpdateEntity(UpdateWeatherObject weatherObject)
{
    string partitionKey = weatherObject.StationName;
    string rowKey = weatherObject.ObservationDate;

    // Use the partition key and row key to get the entity
    TableEntity entity = _tableClient.GetEntity<TableEntity>(partitionKey, rowKey).Value;

    foreach (string propertyName in weatherObject.PropertyNames)
    {
        var value = weatherObject[propertyName];
        entity[propertyName] = value;
    }

    _tableClient.UpdateEntity(entity, new ETag(weatherObject.Etag));
}

Remove an entity

To remove an entity from a table, call the DeleteEntity method on the TableClient object with the partition key and row key of the object.

public void RemoveEntity(string partitionKey, string rowKey)
{
    _tableClient.DeleteEntity(partitionKey, rowKey);           
}

7 - Run the code

Run the sample application to interact with the Cosmos DB Table API. The first time you run the application, there will be no data because the table is empty. Use any of the buttons at the top of application to add data to the table.

Selecting the Insert using Table Entity button opens a dialog allowing you to insert or upsert a new row using a TableEntity object.

Selecting the Insert using Expandable Data button brings up a dialog that enables you to insert an object with custom properties, demonstrating how the Cosmos DB Table API automatically adds properties (columns) to the table when needed. Use the Add Custom Field button to add one or more new properties and demonstrate this capability.

Use the Insert Sample Data button to load some sample data into your Cosmos DB Table.

Select the Filter Results item in the top menu to be taken to the Filter Results page. On this page, fill out the filter criteria to demonstrate how a filter clause can be built and passed to the Cosmos DB Table API.

Clean up resources

When you are finished with the sample application, you should remove all Azure resources related to this article from your Azure account. You can do this by deleting the resource group.

• Azure portal
• Azure CLI
• Azure PowerShell

A resource group can be deleted using the Azure portal by doing the following.

Instructions	Screenshot
To go to the resource group, in the search bar, type the name of the resource group. Then on the Resource Groups tab, select the name of the resource group.
Select Delete resource group from the toolbar at the top of the resource group page.
A dialog will pop out from the right of the screen asking you to confirm deletion of the resource group. Type the full name of the resource group in the text box to confirm deletion as instructed. Select the Delete button at the bottom of the page.

Next steps

In this quickstart, you've learned how to create an Azure Cosmos DB account, create a table using the Data Explorer, and run an app. Now you can query your data using the Table API.