Documentdb 简明教程

DocumentDB - Query Document

在 DocumentDB 中，我们实际上使用 SQL 查询文档，因此本章全是关于在 DocumentDB 中使用特殊 SQL 语法的查询。虽然如果您正在进行 .NET 开发，也可以使用可生成适当 SQL 的 LINQ 提供程序。

In DocumentDB, we actually use SQL to query for documents, so this chapter is all about querying using the special SQL syntax in DocumentDB. Although if you are doing .NET development, there is also a LINQ provider that can be used and which can generate appropriate SQL from a LINQ query.

Querying Document using Portal

Azure 门户具有查询浏览器，可让您针对 DocumentDB 数据库运行任何 SQL 查询。

The Azure portal has a Query Explorer that lets you run any SQL query against your DocumentDB database.

我们将使用查询浏览器来演示查询语言的许多不同功能和特性，从最简单的查询开始。

We will use the Query Explorer to demonstrate the many different capabilities and features of the query language starting with the simplest possible query.

Step 1 − 在数据库边栏中，单击打开查询浏览器边栏。

Step 1 − In the database blade, click to open the Query Explorer blade.

记住查询运行在集合的范围内，因此 Query Explorer 允许您在此下拉列表中选择集合。

Remember that queries run within the scope of a collection, and so the Query Explorer lets you choose the collection in this dropdown.

Step 2 − 选择之前使用门户创建的 Families 集合。

Step 2 − Select Families collection which is created earlier using the portal.

Query Explorer 使用这个简单的查询 SELECT * FROM c 打开，它只从集合中检索所有文档。

The Query Explorer opens up with this simple query SELECT * FROM c, which simply retrieves all documents from the collection.

Step 3 − 通过点击“运行查询”按钮执行此查询。然后您会看到完整的文档在“结果”面板中被检索出来。

Step 3 − Execute this query by clicking the ‘Run query’ button. Then you will see that the complete document is retrieved in the Results blade.

Querying Document using .Net SDK

以下是使用 .Net SDK 运行一些文档查询的步骤。

Following are the steps to run some document queries using .Net SDK.

在这个例子中，我们要查询我们刚刚添加的新创建的文档。

In this example, we want to query for the newly created documents that we just added.

Step 1 − 调用 CreateDocumentQuery，将集合通过其 SelfLink 和查询文本传递给要针对其运行查询的集合。

Step 1 − Call CreateDocumentQuery, passing in the collection to run the query against by its SelfLink and the query text.

private async static Task QueryDocumentsWithPaging(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** Query Documents (paged results) ****");
   Console.WriteLine();
   Console.WriteLine("Quering for all documents");

   var sql = "SELECT * FROM c";
   var query = client.CreateDocumentQuery(collection.SelfLink, sql).AsDocumentQuery();

   while (query.HasMoreResults) {
      var documents = await query.ExecuteNextAsync();

      foreach (var document in documents) {
         Console.WriteLine(" Id: {0}; Name: {1};", document.id, document.name);
      }
   }

   Console.WriteLine();
}

此查询也返回整个集合中的所有文档，但是我们没像之前那样对 CreateDocumentQuery 调用 .ToList，这将发出尽可能多的请求，以便在一行代码中提取所有结果。

This query is also returning all documents in the entire collection, but we’re not calling .ToList on CreateDocumentQuery as before, which would issue as many requests as necessary to pull down all the results in one line of code.

Step 2 − 相反，调用 AsDocumentQuery，此方法返回一个具有 HasMoreResults 属性的查询对象。

Step 2 − Instead, call AsDocumentQuery and this method returns a query object with a HasMoreResults property.

Step 3 − 如果 HasMoreResults 为真，那么调用 ExecuteNextAsync 获取下一个块，然后转储该块中的所有内容。

Step 3 − If HasMoreResults is true, then call ExecuteNextAsync to get the next chunk and then dump all the contents of that chunk.

Step 4 − 如果你愿意，你可以使用 LINQ 而不用 SQL 进行查询。这里我们在 q 中定义了一个 LINQ 查询，但是它在我们在其上运行 .ToList 之前不会执行。

Step 4 − You can also query using LINQ instead of SQL if you prefer. Here we’ve defined a LINQ query in q, but it won’t execute until we run .ToList on it.

private static void QueryDocumentsWithLinq(DocumentClient client) {
   Console.WriteLine();
   Console.WriteLine("**** Query Documents (LINQ) ****");
   Console.WriteLine();
   Console.WriteLine("Quering for US customers (LINQ)");

   var q =
      from d in client.CreateDocumentQuery<Customer>(collection.DocumentsLink)
      where d.Address.CountryRegionName == " United States"
      select new {
         Id = d.Id,
         Name = d.Name,
         City = d.Address.Location.City
      };

   var documents = q.ToList();
   Console.WriteLine("Found {0} UK customers", documents.Count);

   foreach (var document in documents) {
      var d = document as dynamic;
      Console.WriteLine(" Id: {0}; Name: {1}; City: {2}", d.Id, d.Name, d.City);
   }

   Console.WriteLine();
}

SDK 将把我们的 LINQ 查询转换为 DocumentDB 的 SQL 语法，根据我们的 LINQ 语法生成 SELECT 和 WHERE 子句

The SDK will convert our LINQ query into SQL syntax for DocumentDB, generating a SELECT and WHERE clause based on our LINQ syntax

Step 5 − 现在从 CreateDocumentClient 任务调用上述查询。

Step 5 − Now call the above queries from the CreateDocumentClient task.

private static async Task CreateDocumentClient() {
   // Create a new instance of the DocumentClient

   using (var client = new DocumentClient(new Uri(EndpointUrl), AuthorizationKey)) {
      database = client.CreateDatabaseQuery("SELECT * FROM c WHERE c.id =
         'myfirstdb'").AsEnumerable().First();

      collection = client.CreateDocumentCollectionQuery(database.CollectionsLink,
         "SELECT * FROM c WHERE c.id = 'MyCollection'").AsEnumerable().First();

      //await CreateDocuments(client);
      await QueryDocumentsWithPaging(client);
      QueryDocumentsWithLinq(client);
   }

}

执行上述代码后，您将收到以下输出。

When the above code is executed, you will receive the following output.

**** Query Documents (paged results) ****
Quering for all documents
 Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1;
 Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1;

**** Query Documents (LINQ) ****
Quering for US customers (LINQ)
Found 2 UK customers
 Id: 7e9ad4fa-c432-4d1a-b120-58fd7113609f; Name: New Customer 1; City: Brooklyn
 Id: 34e9873a-94c8-4720-9146-d63fb7840fad; Name: New Customer 1; City: Brooklyn