Channels ▼


Working with Azure DocumentDB: SQL & NoSQL Together

In the first article in this two-part series on working with Microsoft Azure DocumentDB, I explained how to use the .NET SDK and C# to start working with DocumentDB databases, collections, documents, and queries. In this article, I explain other operations with documents, queries that navigate through the document hierarchy, and the power of server-side JavaScript that allows you to simplify complex queries.

Updating and Deleting Documents

The code I showed in the previous article explained how to create and search documents in the Games collection. Now, let's update and delete documents in this collection. However, before I can focus on the code that performs these simple operations, it is necessary to make some changes to the Game class. In my previous example, the Game class had an Id property mapped to the gameId key in the underlying document. This way, I can use my desired ID values for the game documents and DocumentDB generates its own document ID value whenever I insert a game. It is necessary to add a property mapped to the id key in the underlying document to the Game class to allow the update and delete methods to work as expected. If the .NET SDK doesn't find a property mapped with the id key that allows it to access the game document id, both the update and delete operations will raise exceptions.

The following lines add a DocumentId property mapped to the id key in the underlying document. Make sure you add them to the Game class.

[JsonProperty(PropertyName = "id")]
public string DocumentId { get; set; }

The following lines show a new version of the RunAsync method that retrieves the Entertainment database and the Games collection, then reads one of the game documents and replaces it with a new one. The code uses a Microsoft.Azure.Documents.Document instance and a POCO (short for Plain Old CLR Object); that is, an instance of the Game class.

private static async Task RunAsync()
    // Try to retrieve a Database if exists, else create the Database
    var database = await RetrieveOrCreateDatabaseAsync(databaseId);

    // Try to retrieve a Document Collection, else create the Document Collection
    var collection = await RetrieveOrCreateCollectionAsync(database.SelfLink, collectionId);

    //Read a Game Document from the database as a dynamic
    dynamic gameDocument = 
        d => d.Id == "690f1bf2-4d53-4882-bb53-3722e5624dfc").AsEnumerable().FirstOrDefault();

    if (gameDocument != null)
        Console.WriteLine("I've found Game with Document Id == {0}", gameDocument.Id);
        // Cast the retrieved game Document to a POCO object (Game)
        Game gameToUpdate = gameDocument;

        // Update the release date in the POCO
        gameToUpdate.ReleaseDate = new DateTime(2014, 8, 12);

        Console.WriteLine("Game document selfLink == {0}", gameDocument.SelfLink);

        // Use the document's SelfLink and the POCO object to replace the document
        var savedGameDocument = 
          await client.ReplaceDocumentAsync(gameDocument.SelfLink, gameToUpdate);
        Console.WriteLine("Game document not found.");

The code for the Game class mapped the Id property to a gameId property in the JSON document. Thus, the Id property for the Game class is different than the Id property of the related Microsoft.Azure.Documents.Document instance. So, if you want to retrieve a Document instance with a LINQ query, you need to use the Id that DocumentDB automatically generated when you created the document. The following code reads a game Document from the database as a dynamic. Notice the call to client.CreateDocumentQuery<Document> and the call to AsEnumerable().FirstOrDefault(). The Document allows me to access the SelfLink property that subsequent lines of code will use to replace the document with a new one. The update operation is in fact a document replacement operation.

dynamic gameDocument = 
    d => d.Id == "690f1bf2-4d53-4882-bb53-3722e5624dfc").AsEnumerable().FirstOrDefault();

Next, the following line casts the retrieved Document instance (gameDocument) to a Game POCO instance (gameToUpdate).

Game gameToUpdate = gameDocument;

This way, it is possible to update one of the Game POCO instance properties; specifically, the RelaseDate property:

gameToUpdate.ReleaseDate = new DateTime(2014, 8, 12);

Finally, the following line calls the client.ReplaceDocumentAsync asynchronous method with the retrieved Document instance SelfLink property and the Game POCO instance with the new ReleaseDate value as the arguments. The document replacement operation returns the document that has been stored in the document collection.

var savedGameDocument = 
    await client.ReplaceDocumentAsync(gameDocument.SelfLink, gameToUpdate);

If you work with classes that extend the Microsoft.Azure.Documents.Document class, you can make changes to the instance and call the ReplaceDocumentAsync with the updated instance as the single argument because the class is capable of accessing the SelfLink property. If you have experience with MongoDB, you will definitely miss push operations to update single values. So far, DocumentDB doesn't provide an equivalent operation to simplify specific update scenarios.

If you want to delete a document form the document collection, you just need to call the DeleteDocumentAsync asynchronous method with SelfLink property for the document you want to delete. For example, the following line would delete the previously retrieved game document. The call to DeleteDocumentAsync returns a Microsoft.Azure.Documents.Client.ResourceResponse<Microsoft.Azure.Documents.Document> that provides information about the operation that you might want to explore.

var resourceResponse = await client.DeleteDocumentAsync(gameDocument.SelfLink);

Creating Queries with Sub-documents

One of the interesting features of a document-oriented database is that you can easily navigate through the documents hierarchies with simple queries. The following DocumentDB SQL query retrieves all the scores from the game documents.

FROM Games.scores[0] s

The following code uses the previous SQL line to retrieve all the scores as a list of Score POCO instances:

var scores = client.CreateDocumentQuery<Level>(collection.SelfLink,
    "SELECT * FROM Games.scores[0] s").ToList();
foreach (var score in scores)
    Console.WriteLine("Player name: {0}, Score: {1}", score.PlayerName, score.BestScore);

These lines show an equivalent LINQ query:

var scores = client.CreateDocumentQuery<Game>(collection.SelfLink)
    .SelectMany(game => game.Scores)
    .Select(s => s)
foreach (var score in scores)
    Console.WriteLine("Player name: {0}, Score: {1}", score.PlayerName, score.BestScore);

It is possible to achieve the same results by using a join. The following DocumentDB SQL query retrieves the playerName and score values from the registered scores of all the game documents.

SELECT s.playerName, s.score FROM Games g JOIN s IN g.scores

It is possible to add a filter condition by using a WHERE clause to retrieve only the sub-documents that satisfy the specified filter. For example, the following DocumentDB SQL query retrieves all the levels from all the game documents that have a value higher than two for the parrots key or property.

FROM Games.levels[0] l
WHERE l.parrots > 2

However, as you might guess, the previous query can be really expensive with the default hash indexes for collections. To run the query with the default hash indexes, specify the EnableScanInQuery feed option in the Microsoft.Azure.Documents.Client.FeedOption class. The following line shows the call to CreateDocumentQuery with the SQL and the necessary option enabled. Don't forget to check the DocumentDB indexing possibilities before you run range queries.

var levels = client.CreateDocumentQuery<Level>(collection.SelfLink,
    "SELECT * FROM Games.levels[0] l WHERE l.parrots > 2", 
     new FeedOptions { EnableScanInQuery = true}).ToList();
foreach (var level in levels)
    Console.WriteLine("Title: {0}, Parrots: {1}", level.Title, level.ParrotsCount);

These lines show an equivalent LINQ query. Remember the ParrotsCount property in the Level class is mapped to the parrots key in the underlying document.

var levelsWithTwoParrotsOrMore = client.CreateDocumentQuery<Game>(collection.SelfLink, new FeedOptions { EnableScanInQuery = true})
    .SelectMany(game => game.Levels)
    .Where(levels => levels.ParrotsCount > 2)
    .Select(l => l)
foreach (var level in levelsWithTwoParrotsOrMore)
    Console.WriteLine("Title: {0}, Parrots: {1}", level.Title, level.ParrotsCount);

Related Reading

More Insights

Currently we allow the following HTML tags in comments:

Single tags

These tags can be used alone and don't need an ending tag.

<br> Defines a single line break

<hr> Defines a horizontal line

Matching tags

These require an ending tag - e.g. <i>italic text</i>

<a> Defines an anchor

<b> Defines bold text

<big> Defines big text

<blockquote> Defines a long quotation

<caption> Defines a table caption

<cite> Defines a citation

<code> Defines computer code text

<em> Defines emphasized text

<fieldset> Defines a border around elements in a form

<h1> This is heading 1

<h2> This is heading 2

<h3> This is heading 3

<h4> This is heading 4

<h5> This is heading 5

<h6> This is heading 6

<i> Defines italic text

<p> Defines a paragraph

<pre> Defines preformatted text

<q> Defines a short quotation

<samp> Defines sample computer code text

<small> Defines small text

<span> Defines a section in a document

<s> Defines strikethrough text

<strike> Defines strikethrough text

<strong> Defines strong text

<sub> Defines subscripted text

<sup> Defines superscripted text

<u> Defines underlined text

Dr. Dobb's encourages readers to engage in spirited, healthy debate, including taking us to task. However, Dr. Dobb's moderates all comments posted to our site, and reserves the right to modify or remove any content that it determines to be derogatory, offensive, inflammatory, vulgar, irrelevant/off-topic, racist or obvious marketing or spam. Dr. Dobb's further reserves the right to disable the profile of any commenter participating in said activities.

Disqus Tips To upload an avatar photo, first complete your Disqus profile. | View the list of supported HTML tags you can use to style comments. | Please read our commenting policy.