Massoud Mazar

Sharing The Knowledge


Azure DocumentDB (Cosmos DB): Cross partition query does not return data if TOP is not specified

When querying Azure DocumentDB (recently renamed to Cosmos DB, to make you really believe it can handle anything you throw at it), best practice is to have a Partition Id. In case of high volume scenarios, it's mandatory to partition your data.

You may select a good partition key, but there are always scenarios where you need to query your data without knowing the Partition Id. These cross partition queries, although slower, are possible by specifying EnableCrossPartitionQuery = true in your FeedOptions, when you are creating your query:

protected IQueryable<TData> CreateQuery(int pageSize, string continuationToken = null)
	var uri = UriFactory.CreateDocumentCollectionUri("database", "children");
	IQueryable<Child> query = _client.CreateDocumentQuery<Child>(uri, 
		new FeedOptions
			EnableCrossPartitionQuery = true,
			MaxItemCount = pageSize,
			RequestContinuation = continuationToken
	query = query.Where(p => p.FirstName == "john" && p.ParentId = "Smith Family");
	return query;

Options I'm using in above code are to allow my doing a paged query. Then I can create a query and get my results:

var query = CreateQuery(pageSize);
var exec = query.AsDocumentQuery();
var skipped = 0;

while (exec.HasMoreResults && results.Count < pageSize)
	var response = await exec.ExecuteNextAsync<Child>();
	if (!response.Any())

	if (skip - skipped >= response.Count)
		skipped += response.Count;
		results.AddRange(response.Skip(skip - skipped).Take(pageSize - results.Count));
		skipped += skip - skipped;

In above code, I was trying to simulate Linq's Skip+Take functionality. I used the PageSize to simulate "Take" and by skipping pages, achieve "Skip", but that is not why I decided to write this post. Problem started when my query did not include filter for "Parent Id". Something like this:

query = query.Where(p => p.FirstName == "john");

And my query was not getting any results. Funny thing is, if I copy the SQL from my above query and run it in DocumentDB Query Explorer in Azure portal, it return the data.

After a few hours of investigation, I noticed when I used to have the Linq's "Take" extension in my query, I was getting data, so I added it back with a large value (in this case 1000) and started to get the data again:

query = query.Where(p => p.FirstName == "john").Take(1000);

 Unfortunately I don't have any explanation for this behavior, but I thought this may help other confused developer like myself.

Add comment