DocumentDB - Limiting Records

Microsoft has recently added a number of improvements on how you can query Azure DocumentDB, such as the TOP keyword to SQL grammar, which made queries run faster and consume fewer resources, increased the pmits for query operators, and added support for additional LINQ operators in the .NET SDK.

Let’s take a look at a simple example in which we will retrieve only the first two records. If you have a number of records and you want to retrieve only some of them, then you can use the Top keyword. In this example, we have a lot of records of earthquakes.

Now we want to show the first two records only

Step 1 − Go to the query explorer and run this query.

SELECT * FROM c 
WHERE c.magnitude > 2.5 

You will see that it has retrieved four records because we have not specified TOP keyword yet.

Step 2 − Now use the TOP keyword with same query. Here we have specified the TOP keyword and ‘2’ means that we want two records only.

SELECT TOP 2 * FROM c 
WHERE c.magnitude > 2.5

Step 3 − Now run this query and you will see that only two records are retrieved.

Similarly, you can use TOP keyword in code using .Net SDK. Following is the implementation.

private async static Task QueryDocumentsWithPaging(DocumentCpent cpent) {
   Console.WriteLine(); 
   Console.WriteLine("**** Query Documents (paged results) ****"); 
   Console.WriteLine();  
   Console.WriteLine("Quering for all documents"); 
	
   var sql = "SELECT TOP 3 * FROM c";  
   var query = cpent 
      .CreateDocumentQuery(collection.SelfLink, sql) 
      .AsDocumentQuery(); 
		
   while (query.HasMoreResults) {
      var documents = await query.ExecuteNextAsync(); 
		
      foreach (var document in documents) { 
         Console.WriteLine(" PubpcId: {0}; Magnitude: {1};", document.pubpcid,
            document.magnitude); 
      } 
   } 
	
   Console.WriteLine(); 
}

Following is the CreateDocumentCpent task in which are instantiated the DocumentCpent and earthquake database.

private static async Task CreateDocumentCpent() {
   // Create a new instance of the DocumentCpent 
   using (var cpent = new DocumentCpent(new Uri(EndpointUrl), AuthorizationKey)) {
      database = cpent.CreateDatabaseQuery("SELECT * FROM c WHERE c.id =
          earthquake ").AsEnumerable().First(); 
			
      collection = cpent.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id =  earthquakedata ").AsEnumerable().First(); 
			
      await QueryDocumentsWithPaging(cpent); 
   } 
}

When the above code is compiled and executed, you will see that only three records are retrieved.

**** Query Documents (paged results) **** 
 
Quering for all documents 
PubpcId: 2015p947400; Magnitude: 2.515176918; 
PubpcId: 2015p947373; Magnitude: 1.506774108; 
PubpcId: 2015p947329; Magnitude: 1.593394461;