CODEWITHSUNDEEP
.netazure-cosmosdbdate-range
Shamim Hafiz - MSFT
Shamim Hafiz - MSFT

Reputation: 22104

CosmsDB Query on Date Field

When I run the following query on AZure portal, it runs fine but when ran programmatically from a .NET App, it gives an error:

Raw query on Azure CosmosDB portal: SELECT * FROM c where c.OrderDateTime > '2018-11-29T18:33:17.5957307Z' Works fine

string queryString = $"SELECT * FROM c  Where c.OrderDateTime >= '{DateTime.UtcNow.AddDays(-1).ToString("s")}'"; Gives the following error:

Message:

{\"Errors\":[\"An invalid query has been specified with filters against path(s) that are not range-indexed. Consider adding allow scan header in the request.\"]}

I understand Dates in range query needs different handling than strings, but was wondering how it can work on the portal? Am I missing something else in my code?

Upvotes: 1

Views: 113

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

{\"Errors\":[\"An invalid query has been specified with filters against path(s) that are not range-indexed. Consider adding allow scan header in the request.\"]}

The error you're receiving here indicates that the query engine could not find a range index for the path C.OrderDateTime . Please check the Indexing Policy of you collection and make sure that both numbers and strings are indexed as range rather than hash. For more information about indexing policies, please see official document.

Or you could set EnableScanInQuery as true in FeedOptions follow the error details:Consider adding allow scan header in the request. Please refer to here.

var feedOptions = new FeedOptions
{
   EnableScanInQuery = true,
};

As I know, SQL Query on the portal is different from sdk query or rest api query. It will have some implicit optimizations, such as not having to provide a partition key to execute a query on a partition key column.That doesn't mean the partition key doesn't need to be provided in client side query operation.

Upvotes: 1

Related Questions