Skip to content

Latest commit

 

History

History
104 lines (70 loc) · 4.98 KB

tutorial-query-table.md

File metadata and controls

104 lines (70 loc) · 4.98 KB
title description author ms.author ms.service ms.subservice ms.topic ms.date ms.reviewer ms.custom
How to query table data in Azure Cosmos DB?
Learn how to query data stored in the Azure Cosmos DB Table API account by using OData filters and LINQ queries
sakash279
akshanka
cosmos-db
cosmosdb-table
tutorial
06/05/2020
sngun
devx-track-csharp

Tutorial: Query Azure Cosmos DB by using the Table API

[!INCLUDEappliesto-table-api]

The Azure Cosmos DB Table API supports OData and LINQ queries against key/value (table) data.

This article covers the following tasks:

[!div class="checklist"]

  • Querying data with the Table API

The queries in this article use the following sample People table:

PartitionKey RowKey Email PhoneNumber
Harp Walter [email protected] 425-555-0101
Smith Ben [email protected] 425-555-0102
Smith Jeff [email protected] 425-555-0104

See Querying Tables and Entities for details on how to query by using the Table API.

For more information on the premium capabilities that Azure Cosmos DB offers, see Azure Cosmos DB Table API and Develop with the Table API in .NET.

Prerequisites

For these queries to work, you must have an Azure Cosmos DB account and have entity data in the container. Don't have any of those? Complete the five-minute quickstart or the developer tutorial to create an account and populate your database.

Query on PartitionKey and RowKey

Because the PartitionKey and RowKey properties form an entity's primary key, you can use the following special syntax to identify the entity:

Query

https://<mytableendpoint>/People(PartitionKey='Harp',RowKey='Walter')

Results

PartitionKey RowKey Email PhoneNumber
Harp Walter [email protected] 425-555-0104

Alternatively, you can specify these properties as part of the $filter option, as shown in the following section. Note that the key property names and constant values are case-sensitive. Both the PartitionKey and RowKey properties are of type String.

Query by using an OData filter

When you're constructing a filter string, keep these rules in mind:

  • Use the logical operators defined by the OData Protocol Specification to compare a property to a value. Note that you can't compare a property to a dynamic value. One side of the expression must be a constant.
  • The property name, operator, and constant value must be separated by URL-encoded spaces. A space is URL-encoded as %20.
  • All parts of the filter string are case-sensitive.
  • The constant value must be of the same data type as the property in order for the filter to return valid results. For more information about supported property types, see Understanding the Table Service Data Model.

Here's an example query that shows how to filter by the PartitionKey and Email properties by using an OData $filter.

Query

https://<mytableapi-endpoint>/People()?$filter=PartitionKey%20eq%20'Smith'%20and%20Email%20eq%20'[email protected]'

For more information on how to construct filter expressions for various data types, see Querying Tables and Entities.

Results

PartitionKey RowKey Email PhoneNumber
Smith Ben [email protected] 425-555-0102

The queries on datetime properties don't return any data when executed in Azure Cosmos DB's Table API. While the Azure Table storage stores date values with time granularity of ticks, the Table API in Azure Cosmos DB uses the _ts property. The _ts property is at a second level of granularity, which isn't an OData filter. So, the queries on timestamp properties are blocked by Azure Cosmos DB. As a workaround, you can define a custom datetime or long data type property and set the date value from the client.

Query by using LINQ

You can also query by using LINQ, which translates to the corresponding OData query expressions. Here's an example of how to build queries by using the .NET SDK:

IQueryable<CustomerEntity> linqQuery = table.CreateQuery<CustomerEntity>()
            .Where(x => x.PartitionKey == "4")
            .Select(x => new CustomerEntity() { PartitionKey = x.PartitionKey, RowKey = x.RowKey, Email = x.Email });

Next steps

In this tutorial, you've done the following:

[!div class="checklist"]

  • Learned how to query by using the Table API

You can now proceed to the next tutorial to learn how to distribute your data globally.

[!div class="nextstepaction"] Distribute your data globally