CloudStorageAccount, making TableQuery request

Question

I am trying to make a query to my CloudTable. What I did before it was getting all elements of certain table but that takes a lot of time.

public static List<CollectItem> GetCollectItems(string connectionString, string tableName, DateTime dateTime)
    {
        CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);

        // Create the table client.
        CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

        // Create the CloudTable object that represents the "people" table.
        CloudTable table = tableClient.GetTableReference(tableName);

        TableContinuationToken token = null;
        List<CollectItem> entities = new List<CollectItem>();

        do
        {
            var queryResult = table.ExecuteQuerySegmented(new TableQuery<CollectItem>(), token);
            entities.AddRange(queryResult.Results);
            token = queryResult.ContinuationToken;
        } while (token != null);

        entities = entities.Where(x => x.Timestamp.DateTime.Year == dateTime.Year &&
                                       x.Timestamp.DateTime.Month == dateTime.Month &&
                                       x.Timestamp.DateTime.Day == dateTime.Day).ToList();

        return entities;
    }

Then i tried creating some query conditions

do
        {
            TableQuery<CollectItem> itemStockQuery = new TableQuery<CollectItem>().Where(
                                                                        TableQuery.GenerateFilterConditionForInt("Timestamp", QueryComparisons.Equal, dateTime)
                                                                        );

            var queryResult = table.ExecuteQuerySegmented(itemStockQuery, token);
            entities.AddRange(queryResult.Results);
            token = queryResult.ContinuationToken;
        } while (token != null);

But now what it does is compere Year, Month, Day, Hour, Minute .... And i need to compere only Year, Month and Day. And then i tried creating this but it said its a bad request.

TableQuery<CollectItem> itemStockQuery = new TableQuery<CollectItem>().Where(
                                                                        TableQuery.GenerateFilterConditionForInt("Timestamp.DateTime.Day", QueryComparisons.Equal, dateTime.Day)
                                                                        );

My question is how can i create query request on Timestamp but only compere to Year, Month and Day property?

Answer 1

You have to create a column and make an index on it. Your currend datetime-column is not indexed and shouldn't be and that's the reason your query takes long time.

You can create a date-column without time-informationen and make an index on that column or you can specify your own format eg. 20200928 as int column for your date.