Using Cosmos DB Table API to query for a datetime range

Question

I am using Cosmos DB Table API to manage my data(Using SQL API is not an option). I have used "Creation DateTime Ticks" as the "PartitionKey". The idea is to retrieve data in every half an hour. To get the new data in half an hour range, I wrote a method, which is like this - (Update - Based on Gaurav's suggestion, I have updated the code).

public async Task<List<TEntity>> GetEntityWithDateTimePartitionKeyAsync<TEntity>(long startDateTimeTicks , long endDateTimeTicks, string tableName) where TEntity : TableEntity, new()
        {
            var results = new List<TEntity>();
            if (endDateTimeTicks > startDateTimeTicks)
            {
                var table = await this.GetTableAsync(tableName, true).ConfigureAwait(false);               
                 var filterA = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, startDateTimeTicks.ToString());
                 var filterB = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, endDateTimeTicks.ToString());
                  var combinedFilter = TableQuery.CombineFilters(filterA, "AND", filterB);
                   var query = new TableQuery<TEntity>().Where(combinedFilter);         
              

                try
                {
                    results = table.ExecuteQuery<TEntity>(query).ToList();                   
                }
                catch(Exception ex)
                {
                }
            }
            return results;
        }

// Sample Data - 
public class TestItem: TableEntity
{
}

//Create the instances and then save them to Cosmos Db.
var testItem1 = new TestItem { PartitionKey ="637671350058032346",RowKey= "Mumbai", ETag="*" };
var testItem2 = new TestItem {PartitionKey = "637671350058033346", RowKey="Delhi" , ETag="*"};
var testItem3 = new TestItem { PartitionKey ="637671350058034346", RowKey="Chennai" , ETag="*"};
var testItem4 = new TestItem { PartitionKey ="637671350058035346", RowKey="Hyderabad" , ETag="*"}

//Calling the method -
var entityList = await GetEntityWithDateTimePartitionKeyAsync<TestItem>(637671350058030000 , 637671350058036000, "TestTable");

` I was getting an exception - "Method 'Visit' in type 'QueryTokenVisitor' from assembly 'Microsoft.Azure.Cosmos.Table, Version=1.0.8.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' does not have an implementation.". I tried to use LINQ query too. But I could not make it work. The other thing which I tried , was , TableQuery.GenerateFilterCondition(). That works for a specific "PartitionKey" and "RowKey" but not for the range of the "PartitionKey". How can I use Cosmos DB Table API to get result for the given DateTime range? I am new to Azure Table API.

Answer 1

There are a few issues with your code:

1. PartitionKey is of type string whereas you're creating a filter condition based on long. Your filter condition will result in an ODATA query like PartitionKey ge {startDateTimeTicks}L and PartitionKey lt {endDateTimeTicks}L. You would need to use code like the following:

var filterA = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, startDateTimeTicks.ToString());
var filterB = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, endDateTimeTicks.ToString());

2. You're missing the logical connector (AND in your case) connecting these two filters when you are creating the query. Because of this your query looks like PartitionKey lt {endDateTimeTicks}L (basically it is just picking up filterB). You will need to combine these two filter conditions and use that in your query:

var combinedFilter = TableQuery.CombineFilters(filterA, "AND", filterB);
var query = new TableQuery<DynamicTableEntity>().Where(combinedFilter);

When you do that, your query would look like:

(PartitionKey ge '{startDateTimeTicks}') AND (PartitionKey lt '{endDateTimeTicks}')

This query should give you data back.

UPDATE

I am not able to reproduce the issue. Here's the code I used and it gave me correct results.

using System;
using System.Threading.Tasks;
using Microsoft.Azure.Cosmos.Table;
using Newtonsoft.Json;

namespace CosmosDbTableSamples
{
    class Program
    {
        static async Task Main(string[] args)
        {
            string accountName = "accountname";
            string accountKey =
                "accountkey";
            string tableUrl = "https://accountname.table.cosmos.azure.com:443/";
            string tableName = "Test";
            StorageCredentials credentials1 = new StorageCredentials();
            
            StorageCredentials credentials = new StorageCredentials(accountName, accountKey);
            CloudTableClient tableClient = new CloudTableClient(new Uri(tableUrl), credentials);
            CloudTable table = tableClient.GetTableReference(tableName);
            long startDateTimeTicks = 637671350058030000, endDateTimeTicks = 637671350058036000;
            var filterA = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, startDateTimeTicks.ToString());
            var filterB = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, endDateTimeTicks.ToString());
            var combinedFilter = TableQuery.CombineFilters(filterA, "and", filterB);
            var query = new TableQuery<TestItem>().Where(combinedFilter);
            var result = await table.ExecuteQuerySegmentedAsync<TestItem>(query, null);
            foreach (var row in result)
            {
                Console.WriteLine($"{row.PartitionKey}|{row.RowKey}");
            }
        }
    }
    
    public class TestItem: TableEntity
    {
    }
}

I used Microsoft.Azure.Cosmos.Table (1.0.8) SDK.