CosmsDB Query on Date Field

Question

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?

How is your index policy? The error you're receiving indicates that the query engine could not find a range index for the path c.OrderDateTime. — Jay Gong
@JayGong Indexing is numbers range and strings hash, but nothing for DateTime. — Shamim Hafiz
So, maybe you need to add strings range index for OrderDataTime column,please have a try? — Jay Gong

Jay Gong Jay Gong · Accepted Answer · 2018-12-03T06:22:27

{\"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.

CosmsDB Query on Date Field

1 Answers