Working with POCOs, LINQ, and a NoSQL Document Database

In this chapter, we will continue working with the .NET Core SDK, but this time we will work with POCOs and LINQ queries. We will take advantage of the strongly typed features of C# and the functional programming features that LINQ provides to work with Cosmos DB. We will improve the application we started in the previous chapter and we will understand the advantages of working with POCOs combined with LINQ.

Creating models and customizing serialization

So far, we have been working with dynamic objects and we wrote SQL queries in strings without taking advantage of the beloved LINQ features. Now we will create a new version of the application that will use POCOs to represent the competitions. This way, we will be able to use strongly typed properties and work with LINQ to build queries instead of composing queries with strings.

Whenever we have to persist a document in the document database, the C# object that represents the document will be serialized to a JSON document; that is, it will be encoded in a string. Whenever we have to retrieve a document from the document database, the JSON document will be deserialized to the C# object that represents the document; that is, the object will be built from the string.

One of the key benefits of working with Cosmos DB, its .NET Core SDK, and a document database based on the SQL API is that we don't have to use an Object-Relational Mapping (ORM) as we usually would when working with relational databases and object-oriented programming languages such as C#. With Cosmos DB, we just need to provide the necessary instructions to the JSON serializer for the classes that represent the documents whenever the default options aren't suitable. There is no need to perform any additional configuration tasks. This is much simpler than working with ORMs.

The fact that we decide to work with POCOs doesn't mean that we will lose the benefits of the schema-agnostic document database. In fact, we will write the code for the first version of the application and then we will make changes based on new requirements that will generate different schemas, and so we won't require any migration process.

Now we will use the .NET Core 2 console application we coded in the previous chapter as a baseline and we will code the following new elements:

  • CompetitionStatus: This enum defines the three possible statuses of a competition: scheduled, finished, and canceled. We will use the appropriate serialization settings to serialize the enum value as a string instead of as a number. Hence, for example, CompetitionStatus.Finished will be serialized as "Finished".
  • Location: This class defines a competition's location.
  • Player: This class defines a player.
  • Winner: This class defines a competition's winner.
  • Competition: This class defines a competition with a location, and its winners if its status is "Finished". This class represents the document we will persist in the document collection.

The code file for the solution with the new sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1.sln file.

In Visual Studio, right-click on the project name (SampleApp1) in Solution Explorer, select Add | New Folder, and enter Types as the desired name.

Right-click on the Types folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter CompetitionStatus.cs as the desired name.

Enter the following code to declare CompetitionStatusenum in the CompetitionStatus.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Types/CompetitionStatus.cs file:

namespace SampleApp1.Types
{
    public enum CompetitionStatus
    {
        // The competition is scheduled and didn't happen yet
        Scheduled,
        // The competition is finished and has winners
        Finished,
        // The competition has been canceled
        Canceled
    }
}

Right-click on the Types folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter GamingPlatform.cs as the desired name.

Enter the following code to declare the GamingPlatformenum in the GamingPlatform.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Types/GamingPlatform.cs file:

namespace SampleApp1.Types
{
    using Newtonsoft.Json;
     using Newtonsoft.Json.Converters;
    [JsonConverter(typeof(StringEnumConverter))]
    public enum GamingPlatform
    {
        Switch,
        PC,
        PS4,
        XBox,
        iOS,
        Android
    }
}

The enum declaration is preceded by the following attribute, which overrides the default JSON converter to indicate that we want to serialize the enum declaration to its string representation. For example, GamingPlatform.PS4 will be serialized to "PS4":

[JsonConverter(typeof(StringEnumConverter))]

In Visual Studio, right-click on the project name (SampleApp1) in Solution Explorer, select Add | New Folder, and enter Models as the desired name.

Right-click on the Models folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter Location.cs as the desired name.

Enter the following code to declare the Location class in the Location.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Models/Location.cs file:

namespace SampleApp1.Models
{
    using Newtonsoft.Json;
    public class Location
    {
        [JsonProperty(PropertyName = "zipCode")]
         public string ZipCode { get; set; }
        [JsonProperty(PropertyName = "state")]
         public string State { get; set; }
    }
}

In C#, public properties use upper CamelCase, also known as PascalCase, and therefore, start with an uppercase character. In our previous example, we used lower camelCase for the keys in the JSON documents, and therefore, the keys in the two documents inserted in the document collection start with a lowercase letter.

The ZipCode property for an instance of the Location class must be serialized to a keyvalue pair whose key must be "zipCode" instead of "ZipCode". Cosmos DB uses Json.NET, a popular JSON framework for .NET, to serialize POCOs to JSON documents and deserialize JSON documents to POCOs. The default behavior of Json.NET makes it convert each property name in the C# class to a JSON key without performing any case changes. Thus, if we use the default settings, ZipCode will be serialized to a key named ZipCode. JSON is case sensitive, and therefore, zipCode is not the same as ZipCode for a key.

We might solve this issue by configuring a contract resolver to handle this case conversion automatically in the serialization and deserialization processes handled by Json.NET. However, we want to stay focused and we will keep the example simple, so we will use attributes for each property that requires a different name. Just keep in mind there are simpler ways when we want to work with more classes.

For example, if we run the following query in the Competitions1 document collection, the results will be an array of empty JSON objects because the ZipCode key doesn't exist within the object in the location key of the two existing documents:

SELECT c.location.ZipCode FROM c

The following lines show the results of the previous query:

[
    {},
    {}
]

The next query uses the appropriate case for the zipCode key:

SELECT c.location.zipCode FROM c

The following lines show the results of the new query with the appropriate case:

[
    {
        "zipCode": "90210"
    },
    {
        "zipCode": "92075"
    }
]

We can use the Newtonsoft.Json.JsonProperty attribute with the PropertyName parameter set to the name of the JSON key to which a property must be mapped. In the previous code, the ZipCode property is mapped to the "zipCode" JSON key and the State property is mapped to the "state" key. We will use the JsonProperty attribute for all the properties we define for our models to indicate the appropriate name for the JSON key that is mapped to each property.

In this case, we are creating models that must be compatible with existing documents that have been inserted in the document collection. However, in other cases, we might decide to use the same case for the C# properties and the JSON document keys to avoid using the JsonProperty attribute for each property.

Right-click on the Models folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter Player.cs as the desired name.

Enter the following code to declare the Player class in the Player.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Models/Player.cs file:

namespace SampleApp1.Models
{
    using Newtonsoft.Json;
    public class Player
    {
        [JsonProperty(PropertyName = "nickName")]
         public string NickName { get; set; }
        [JsonProperty(PropertyName = "country")]
         public string Country { get; set; }
        [JsonProperty(PropertyName = "city")]
         public string City { get; set; }
    }
}

Right-click on the Models folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter Winner.cs as the desired name.

Enter the following code to declare the Winner class in the Winner.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Models/Winner.cs file:

namespace SampleApp1.Models
{
    using Newtonsoft.Json;
    public class Winner
    {
        [JsonProperty(PropertyName = "player")]
         public Player Player { get; set; }
        [JsonProperty(PropertyName = "position")]
         public int Position { get; set; }
        [JsonProperty(PropertyName = "score")]
         public int Score { get; set; }
        [JsonProperty(PropertyName = "prize")]
         public int Prize { get; set; }
    }
}

Right-click on the Models folder, select Add | New Item, and then select Visual C# Items | Code | Code File. Enter Competition.cs as the desired name.

Enter the following code to declare the Competition class in the Competition.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Models/Competition.cs file:

namespace SampleApp1.Models
{
    using Microsoft.Azure.Documents;
    using Newtonsoft.Json;
    using Newtonsoft.Json.Converters;
    using SampleApp1.Types;
    using System;
    public class Competition: Document
    {
        [JsonProperty(PropertyName = "title")]
         public string Title { get; set; }
        [JsonProperty(PropertyName = "location")]
         public Location Location { get; set; }
        [JsonProperty(PropertyName = "platforms")]
        public GamingPlatform[] Platforms { get; set; }
        [JsonProperty(PropertyName = "games")]
         public string[] Games { get; set; }
        [JsonProperty(PropertyName = "numberOfRegisteredCompetitors")]
         public int NumberOfRegisteredCompetitors { get; set; }
        [JsonProperty(PropertyName = "numberOfCompetitors")]
         public int NumberOfCompetitors { get; set; }
        [JsonProperty(PropertyName = "numberOfViewers")]
         public int NumberOfViewers { get; set; }
        [JsonProperty(PropertyName = "status")]
        [JsonConverter(typeof(StringEnumConverter))]
         public CompetitionStatus Status { get; set; }
        [JsonProperty(PropertyName = "dateTime")]
         public DateTime DateTime { get; set; }
        [JsonProperty(PropertyName = "winners")]
         public Winner[] Winners { get; set; }
    }
}

The Competition class represents the document that is persisted in the document collection and inherits from the Microsoft.Azure.Documents.Document class. This way, a Competition instance will be able to access the automatically generated key-value pairs through the properties inherited from the superclass of Document: Resource. We learned about these properties in the Understanding the main classes of the Cosmos DB SDK for .NET Core section in Chapter 4, Building an Application with C#, Cosmos DB, a NoSQL Document Database, and the SQL API.

The Competition class declares the Status property as an enum declaration of the previously created CompetitionStatus type. The following attribute overrides the default JSON converter to indicate that we want to serialize the enum to its string representation:

[JsonConverter(typeof(StringEnumConverter))]

We have coded the necessary models that will enable us to write code that inserts Competition instances in the Cosmos DB document collection, retrieve them, and write strongly typed LINQ queries.

Retrieving a POCO with a LINQ asynchronous query

Now open the Program.cs file and add the following using statements after the last line that declares a using statement: using System.Threading.Tasks;. The code file for the sample is included in the learning_cosmos_db_06_01 folder in the SampleApp1/SampleApp1/Program.cs file:

using System.Collections.Generic; using SampleApp1.Models; using SampleApp1.Types;

Add the following static field to the Program class after the code that declares the client static field: private static DocumentClient client;. In this new version, we will save the Uri instance for the document collection in this field to reuse it in all the methods that require the collection URI:

private static Uri collectionUri;

The following lines declare the code for the GetCompetitionByTitleWithLinq asynchronous static method that builds a LINQ query to retrieve a Competition instance with a specific title from the document collection. The code is the LINQ version of the existing GetCompetitionByTitle static method. Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<Competition> GetCompetitionByTitleWithLinq(string
title)
{
    // Build a query to retrieve a Competition with a specific title
    var documentQuery = client.CreateDocumentQuery<Competition>(collectionUri,
        new FeedOptions()
        {
            EnableCrossPartitionQuery = true,
            MaxItemCount = 1,
        })
        .Where(c => c.Title == title)
        .Select(c => c)
        .AsDocumentQuery();
    while (documentQuery.HasMoreResults)
    {
        foreach (var competition in await documentQuery.ExecuteNextAsync<Competition>())
        {
            Console.WriteLine(
                 $"The Competition with the following title exists: {title}");
            Console.WriteLine(competition);
            return competition;
        }
    }
    // No matching document found
    return null; 
}

First, the code calls the client.CreateDocumentQuery<Competition> method to create a query for documents of the Competition type with the following arguments:

  • collectionUri: The Uri instance for the document collection whose documents we want to query.
  • new FeedOptions() { EnableCrossPartitionQuery = true,MaxItemCount = 1 }: A new FeedOptions instance that specifies that we want to enable the query that sends more than one request, because its scope is not limited to a single partition key value. The query will check Competition instances whose Location property might have different ZipCode values, and therefore, we assign true to the EnableCrossPartitionQuery property. In addition, we assign 1 to the MaxItemCount property because we want a maximum of 1 result each time we perform the enumeration operation.

The client.CreateDocumentQuery<Competition> method returns a System.Linq.IOrderedQueryable<Competition> object, which the code converts to Microsoft.Azure.Documents.Linq.IDocumentQuery<Competition> by chaining a call to the AsDocumentQuery method after the chained LINQ query methods. The IDocumentQuery<Competition> object supports pagination and asynchronous execution and it is saved in the documentQuery variable.

The Where query method checks whether the Title property matches the title received as an argument, and the Select query method indicates we want to retrieve the Competition instance for each Competition that matches the criteria.

At this point, the query hasn't been executed. The use of the AsDocumentQuery method enables the code to access the HasMoreResults bool property in a while loop that makes calls to the asynchronous ExecuteNextAsync method to retrieve more results as long as they are available. The first time the documentQuery.HasMoreResults property is evaluated, its value is true. However, no query was executed yet. Hence, the true value indicates that we must make a call to the documentQuery.ExecuteNextAsync<Competition> asynchronous method to retrieve the first result set with a maximum number of items equal to the MaxItemCount value specified for the FeedOptions instance. After the code calls the documentQuery.ExecuteNextAsync<Competition> method for the first time, the value of the documentQuery.HasMoreResults property will be updated to indicate whether another call to the documentQuery.ExecuteNextAsync<Competition> method is necessary because another result set is available.

Note that we specify the type argument for the documentQuery.ExecuteNextAsync method within angle brackets (<Competition>) to make sure that the competition variable will be of the Competition type that we retrieve with the query. If we didn't specify the type argument, and we used var competition to declare the variable in the foreach loop, C# would use the dynamic type for competition, and we don't want this to happen.

A foreach loop iterates the IEnumerable<Competition> object provided by the FeedResponse<Competition> object returned by the documentQuery.ExecuteNextAsync<Competition> asynchronous method, which enumerates the results of the appropriate page of the execution of the query. Each retrieved competition is a Competition instance that Json.NET has deserialized from the JSON document retrieved from the document collection with the LINQ query. In addition, the LINQ query generates a SQL API query for the Cosmos DB document collection.

If there is a match, the code in the foreach loop will display the retrieved document; that is, the Competition instance that matches the title and the method will return this Competition instance. Otherwise, the method will return null. Note that the method returns Task<Competition>, and therefore, the code that calls this method will be able to work with a Competition instance instead of dealing with a dynamic object.

Note that the previous method doesn't chain a FirstOrDefault method to the query, because this method would execute the query with a synchronous execution. In our examples, we are always working with queries with an asynchronous execution and we won't use synchronous methods to run the queries.

Inserting POCOs

Now we will write the generalized InsertCompetition asynchronous method, which receives a Competition instance, inserts it into the document collection, and displays a message indicating that the competition with a specific status and title has been created. Add the following lines to the existing code of the Program.cs file. The code file for the

sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<Competition> InsertCompetition(Competition
competition)
{
    var documentResponse = await client.CreateDocumentAsync(collectionUri, competition);
    if (documentResponse.StatusCode == System.Net.HttpStatusCode.Created)
    {
        Console.WriteLine($"The {competition.Status} competition with the title {competition.Title} has been created.");
    }
    Competition insertedCompetition = (dynamic) documentResponse.Resource;
    return insertedCompetition; 
}

The first line calls the client.CreateDocumentAsync asynchronous method to request that Cosmos DB creates a document with collectionUri and the received Competition instance, which we want to serialize to JSON and insert as a document in the specified document collection.

The call to the client.CreateDocumentCollectionAsync method returns a ResourceResponse<Document> instance, which the code saves in the documentResponse variable. The created document resource is available in the documentResponse.Resource property of this instance.

The code checks the value of the HTTP status code available in the documentResponse.StatusCode property to determine whether the document has been created, and displays a message if this property is equal to the HTTP 201 created status (System.Net.HttpStatusCode.Created).

Then, the code casts the documentResponse.Resource variable to a dynamic object and uses it to generate the Competition instance, which was created as a document in the collection, and saves it in the insertedCompetition variable. Finally, the method returns this Competition instance.

Now we will write three methods that insert new documents that represent competitions by working with instances of the Competition class and its related classes. The methods will end up calling the recently coded InsertCompetition method.

The following lines declare the code for the InsertCompetition3 asynchronous static method, which creates and inserts a finished competition with two winners. Add the following lines to the existing code of the Program.cs file. The code file for the sample is

included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<Competition> InsertCompetition3()
{
    var competition = new Competition
    {
        Id = "3",
        Title = "League of legends - San Diego 2018",
        Location = new Location
        {
            ZipCode = "92075",
            State = "CA"
        },
        Platforms = new[]
        {
            GamingPlatform.Switch
        },
        Games = new[]
        {
            "Fortnite", "NBA Live 19", "PES 2019"
        },
        NumberOfRegisteredCompetitors = 80,
        NumberOfCompetitors = 30,
        NumberOfViewers = 390,
        Status = CompetitionStatus.Finished,
        DateTime = DateTime.UtcNow.AddDays(-20),
        Winners = new[]
        {
            new Winner
            {
                Player = new Player
                {
                    NickName = "BrandonMilazzo",
                    Country = "Italy",
                    City = "Milazzo"
                },
                Position = 1,
                Score = 12850,
                Prize = 800
            },
            new Winner
            {
                Player = new Player
                {
                    NickName = "Kevin Maverick",
                    Country = "Ireland",
                    City = "Dublin"
                },
                Position = 2,
                Score = 12500,
                Prize = 400
            },
        },
    };
    return await InsertCompetition(competition); }

The following lines declare the code for the InsertCompetition4 asynchronous static method, which creates and inserts a scheduled competition. Note that the dateTime value for the document will be calculated to be 300 days from now. Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<Competition> InsertCompetition4()
{
    // Insert a document related to a competition that is scheduled
    // and doesn't have winners yet
     var competition = new Competition
    {
        Id = "4",
        Title = "League of legends - San Diego 2019",
        Location = new Location
        {
            ZipCode = "92075",
            State = "CA"
        },
        Platforms = new[]
        {
            GamingPlatform.Switch, GamingPlatform.PC, GamingPlatform.XBox
        },
        Games = new[]
        {
            "Madden NFL 19", "Fortnite"
        },
        Status = CompetitionStatus.Scheduled,
        DateTime = DateTime.UtcNow.AddDays(300),
    };
    return await InsertCompetition(competition); }

The fact that we are working with strongly typed code to create the competitions ensures that we don't have typos in the property names. In addition, the use of enums that are serialized to strings makes sure that we don't have typos for the competition status and the supported gaming platforms.

Calculating a cross-partition aggregate with an asynchronous LINQ query

The following lines declare the code for the DoesCompetitionWithTitleExistWithLinq asynchronous static method, which builds a LINQ query to count the number of competitions with the received title. In order to compute this aggregate, we must run a cross-partition query, because the title for the competition can be at any location; that is, at any zip code.

Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<bool>
DoesCompetitionWithTitleExistWithLinq(string competitionTitle)
{
    var competitionsCount = await client.CreateDocumentQuery<Competition>(collectionUri,
        new FeedOptions()
        {
            EnableCrossPartitionQuery = true,
            MaxItemCount = 1,
        })
        .Where(c => c.Title == competitionTitle)
        .CountAsync();
    return (competitionsCount == 1); }

First, the code calls the client.CreateDocumentQuery<Competition> method to create a query for documents of the Competition type with the following arguments:

  • collectionUri: The Uri instance for the document collection whose documents we want to query
  • new FeedOptions() { EnableCrossPartitionQuery = true,MaxItemCount = 1 }: A new FeedOptions instance that specifies that we want to enable cross-partition queries

The client.CreateDocumentQuery<Competition> method returns a System.Linq.IOrderedQueryable<Competition> object, which the code chains to a Where LINQ query method and to the CountAsync asynchronous method to retrieve the number of competitions that match the criteria with an asynchronous query. The CountAsync asynchronous method performs the COUNT aggregate function for the competitions that match the specified criteria with the Where LINQ query method.

The CountAsync asynchronous method is declared in the Microsoft.Azure.Documents.Linq namespace. This namespace also provides the following asynchronous methods that we can use to calculate aggregates for the different numeric types:

  • AverageAsync: This method computes an average
  • MaxAsync: This method computes the maximum value
  • MinAsync: This method computes the minimum value
  • SumAsync: This method computes the sum of all the values

Reading and updating an existing document with a POCO

Now we will write a method that updates a persisted Competition instance that represents a competition that is scheduled. Specifically, the method changes the values for the DateTime, NumberOfRegisteredCompetitors, and Platforms properties.

The following lines declare the code for the UpdateScheduledCompetitionWithPlatforms asynchronous static method, which receives the competition id, its location zip code, the new date and time, the new number of registered competitors, and the new gaming platforms in the competitionId, competitionLocationZipCode, newDataTime, newNumberOfRegisteredCompetitors, and newGamingPlatforms arguments. The method retrieves the Competition instance whose ID matches the competitionId value received as an argument, and updates the values to the explained properties. Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task<Competition> 
UpdateScheduledCompetitionWithPlatforms(string competitionId,
    string competitionLocationZipCode,
    DateTime newDateTime,
    int newNumberOfRegisteredCompetitors,
    IList<GamingPlatform> newGamingPlatforms)
{
    // Retrieve a document related to a competition that is scheduled
    // and update its date, number of registered competitors and platforms
    // The read operation requires the partition key
    var documentToUpdateUri = UriFactory.CreateDocumentUri(databaseId, collectionId, competitionId);
    var readCompetitionResponse = await client.ReadDocumentAsync<Competition>(documentToUpdateUri, new RequestOptions()
    {
        PartitionKey = new PartitionKey(competitionLocationZipCode)
    });
    readCompetitionResponse.Document.DateTime = newDateTime;
     readCompetitionResponse.Document.NumberOfRegisteredCompetitors = newNumberOfRegisteredCompetitors;
    readCompetitionResponse.Document.Platforms = newGamingPlatforms.ToArray();
    var updatedCompetitionResponse = await client.ReplaceDocumentAsync(
        documentToUpdateUri,
        readCompetitionResponse.Document);
    if (updatedCompetitionResponse.StatusCode == System.Net.HttpStatusCode.OK)
    {
        Console.WriteLine($"The competition with id {competitionId} has been updated.");
    }
    Competition updatedCompetition = (dynamic)updatedCompetitionResponse.Resource;
    return updatedCompetition; 
}

The call to the client.ReadDocumentAsync<Competition> asynchronous method with documentToUpdateUri and a new RequestOptions instance as the arguments returns a DocumentResponse<Competition> instance, which the code saves in the readCompetitionResponse variable. The retrieved Competition instance deserialized from the read document is available in the readCompetitionResponse.Document property of this instance. Note that we specify the type argument for the client.ReadDocumentAsync method within angle brackets (<Competition>).

The next two lines assign the new desired values to the retrieved Competition instance. Note that we don't need to cast the Document property because it is already of the Competition type.

The next line calls the client.ReplaceDocumentAsync method with the following arguments:

  • documentToUpdateUri: The Uri instance for the document that will be replaced
  • readCompetitionResponse.Document: The Competition instance with the new values for the DateTime, NumberOfRegisteredCompetitors, and Platforms properties

The call to the client.ReplaceDocumentAsync method returns a ResourceResponse<Document> instance, which the code saves in the updatedCompetitionResponse variable. The persisted Competition instance is available in the updatedCompetitionResponse.Resource property of this instance. In this case, the Resource property is of the Document type.

The code checks the value of the HTTP status code available in the collectionResponse.StatusCode property to determine whether the document has been updated and displays a message if this property is equal to the HTTP 200 OK status (System.Net.HttpStatusCode.OK).

Then, the code casts the updatedDocumentResponse.Resource variable to a dynamic object and uses it to generate the Competition instance that was updated as a document in the collection and save it in the updatedCompetition variable. Finally, the method returns this Competition instance.

Querying documents in multiple partitions with LINQ

The following lines declare the code for the ListScheduledCompetitionsWithLinq asynchronous static method, which builds a LINQ query to retrieve the titles for all the scheduled competitions that have more than five registered competitors and show them in the console output.

Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task ListScheduledCompetitionsWithLinq()
{
    // Retrieve the titles for all the scheduled competitions that have more than 5 registered competitors
     var selectTitleQuery = client.CreateDocumentQuery<Competition>(collectionUri,
        new FeedOptions()
        {
            EnableCrossPartitionQuery = true,
            MaxItemCount = 100,
        })
        .Where(c => (c.NumberOfRegisteredCompetitors > 5)
        && (c.Status == CompetitionStatus.Scheduled))
        .Select(c => c.Title)
        .AsDocumentQuery();
    while (selectTitleQuery.HasMoreResults)
    {
        var selectTitleQueryResult = await selectTitleQuery.ExecuteNextAsync<string>();
        foreach (var title in selectTitleQueryResult)
        {
            Console.WriteLine(title);
        }
    }
}

The client.CreateDocumentQuery<Competition> method returns a System.Linq.IOrderedQueryable<Competition> object, which the code converts to Microsoft.Azure.Documents.Linq.IDocumentQuery<Competition> by chaining a call to the AsDocumentQuery method after the chained LINQ query methods.

The Where query method checks whether the NumberOfRegisteredCompetitors property is greater than 5 and whether the value of the Status property is equal to CompetitionStatus.Scheduled. The Select query method indicates we want to retrieve the Title property for each Competition that matches the specified criteria. Note that writing the criteria is easier than writing a SQL string, which was an error-prone practice.

Note that we specify the type argument for the documentQuery.ExecuteNextAsync method within angle brackets (<string>) to make sure that the title variable will be of the string type and not a dynamic object.

A foreach loop iterates the IEnumerable<string> object provided by the FeedResponse<string> object returned by the documentQuery.ExecuteNextAsync<string> asynchronous method, which enumerates the results of the appropriate page of the execution of the query. Each retrieved title is a Title property value for a Competition instance that Json.NET has deserialized from the "title" key of the JSON document retrieved from the document collection with the LINQ query.

Writing LINQ queries that perform operations on arrays

The following lines declare the code for the ListFinishedCompetitionsFirstWinner asynchronous static method. This method builds a LINQ query to retrieve the winner with the first position for all the finished competitions. The competitions are filtered to include only those that allowed the platform received as an argument and that are located in the zip code received as a parameter. The query restricts the location's zip code value, and therefore, it is a single-partition query.

Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task
ListFinishedCompetitionsFirstWinner(GamingPlatform gamingPlatform, string
zipCode)
{
    // Retrieve the winner with the first position for all the finished competitions
    // that allowed the platform received as an argument
    // and located in the zipCode received as an argument. 
    var winnersQuery = client.CreateDocumentQuery<Competition>(collectionUri,
        new FeedOptions()
        {
            MaxItemCount = 100,
        })
        .Where(c => (c.Location.ZipCode == zipCode)
        && (c.Status == CompetitionStatus.Finished)
        && (c.Platforms.Contains(gamingPlatform)))
        .Select(c => c.Winners[0])
        .AsDocumentQuery();
    while (winnersQuery.HasMoreResults)
    {
        var winnersQueryResult = await winnersQuery.ExecuteNextAsync<Winner>();
        foreach (var winner in winnersQueryResult)
        {
             Console.WriteLine($"Nickname: {winner.Player.NickName}, Score: {winner.Score}");
        }
    }
}

The Where query method uses the c.Platforms.Contains method to check whether the Platforms array of GamingPlatform contains the GamingPlatform received in the gamingPlatform argument. The SQL API query generated by LINQ will use the previously learned ARRAY_CONTAINS built-in array function.

The Select query method retrieves the first winner in the Winners array by specifying c.Winners[0]. All the finished competitions have at least one winner, and therefore, we didn't need to check the number of elements in the array that would add complexity to the query.

Note that we specify the type argument for the documentQuery.ExecuteNextAsync method within angle brackets (<Winner>) to make sure that the winner variable will be of the Winner type and not a dynamic object. The foreach loop displays the NickName for the Player object related to a Winner and the achieved Score.

Calling asynchronous methods that use POCOs to create and query documents

Now we will write the code for the CreateAndQueryCompetitionsWithLinqAsync asynchronous static method, which calls the previously created and explained asynchronous static methods. Add the following lines to the existing code of the Program.cs file. The code file for the sample is included in the learning_cosmos_db_05_01 folder in the SampleApp2/SampleApp1/Program.cs file:

private static async Task CreateAndQueryCompetitionsWithLinqAsync()
{
    var database = await RetrieveOrCreateDatabaseAsync();
    Console.WriteLine(
        $"The database {databaseId} is available for operations with the following AltLink: {database.AltLink}");
    var collection = await CreateCollectionIfNotExistsAsync();
    Console.WriteLine(
        $"The collection {collectionId} is available for operations with the following AltLink: {collection.AltLink}");
    collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId,collectionId);
    var competition3 = await GetCompetitionByTitleWithLinq("League of legends - San Diego 2018");
     if (competition3 == null)
    {
        competition3 = await InsertCompetition3();
    }
    bool isCompetition4Inserted = await DoesCompetitionWithTitleExistWithLinq("League of legends - San Diego 2019");
    Competition competition4;
     if (isCompetition4Inserted)
    {
        competition4 = await GetCompetitionByTitleWithLinq("League of legends - San Diego 2018");
         Console.WriteLine(
            $"The {competition4.Status} competition with the following title exists: {competition4.Title}");
    }
    else
    {
        competition4 = await InsertCompetition4();
     }
    var updatedCompetition4 = await UpdateScheduledCompetitionWithPlatforms("4",
        "92075",
        DateTime.UtcNow.AddDays(300),
        10,
        new List<GamingPlatform>
        {
            GamingPlatform.PC, GamingPlatform.XBox
        });
    await ListScheduledCompetitionsWithLinq();
    await ListFinishedCompetitionsFirstWinner(GamingPlatform.PS4, "90210");
    await ListFinishedCompetitionsFirstWinner(GamingPlatform.Switch,"92075");
}

Now find the following line in the existing Main method:

CreateAndQueryDynamicDocumentsAsync().Wait();

Replace the line with the next line:

CreateAndQueryCompetitionsWithLinqAsync().Wait();

The new method that is called by the Main method performs the following actions:

  1. Calls the RetrieveOrCreateDatabaseAsync method to create or retrieve the Cosmos DB document database specified in the appropriate key in the configuration.json file.
  2. Calls the CreateCollectionIfNotExistsAsync method to create or retrieve the Cosmos DB document collection specified in the appropriate key in the configuration.json file.
  3. Calls the GetCompetitionByTitleWithLinq method to check whether a competition with the title that matches "League of legends - San Diego 2018" exists. If the competition isn't found, the code calls the InsertCompetition3 method to insert the Competition instance that represents the third competition.
  4. Calls the DoesCompetitionWithTitleExistWithLinq method to check whether a competition with the title that matches "League of legends – San Diego 2019" exists. If the competition isn't found, the code calls the InsertCompetition4 method to insert the Competition instance that represents the fourth competition.
  5. Calls the UpdateScheduledCompetitionWithPlatforms method to update the date and time, the registered number of competitions, and the gaming platforms for the fourth competition.
  6. Calls the ListScheduledCompetitionsWithLinq method to list the titles for all the scheduled competitions that have more than five registered competitors.
  7. Calls the ListFinishedCompetitionsFirstWinner method to list the nickname and achieved score for the first position winner of all the finished competitions that allowed the PS4 platform and had a zip code of 90210.
  8. Calls the ListFinishedCompetitionsFirstWinner method to list the nickname and achieved score for the first position winner of all the finished competitions that allowed the Switch platform and had a zip code of 92075.

Now run the application for the first time and you will see the following messages in the console output:

The database Competition has been retrieved.
The database Competition is available for operations with the following AltLink: dbs/Competition 
The collection Competitions1 already exists.
The collection Competitions1 is available for operations with the following AltLink: dbs/Competition/colls/Competitions1 
The Finished competition with the title League of legends - San Diego 2018 has been created.
The Scheduled competition with the title League of legends - San Diego 2019 has been created.
The competition with id 4 has been updated.
Defenders of the crown - San Diego 2018
League of legends - San Diego 2019
Nickname: EnzoTheGreatest, Score: 7500
Nickname: BrandonMilazzo, Score: 12850

Use your favorite tool to check the documents in the Cosmos DB database and collection that you have configured in the configuration.json file that the application uses. Make sure you refresh the appropriate screen in the selected tool. Now you will see four documents that belong to two different partitions based on the value of the location.zipCode key.

Inspecting the SQL API queries that LINQ generates

Now we will establish a breakpoint in one of the methods that builds a LINQ query against the Cosmos DB database to inspect the SQL API query that LINQ generates. This way, we will be able to grab the SQL queries and run them in our favorite tool to query the document collection. In addition, we will learn how to add the necessary code to print the generated SQL query in the debug output whenever necessary.

Go to the following line within the GetCompetitionByTitleWithLinq static method: while (documentQuery.HasMoreResults)

Right-click on the line and select Breakpoint | Insert breakpoint in the context menu.

Start debugging the application.

Inspect the value for documentQuery and Visual Studio will display a JSON key-value pair with the generated SQL API query string in the value for the "query" key, as shown in the following screenshot:

We can add the following line to display the query key and its value to the debug output:

System.Diagnostics.Debug.WriteLine(documentQuery.ToString());

We can also write the following line in the Immediate window: documentQuery.ToString()

The following line shows the results:

{"query":"SELECT VALUE root FROM root WHERE (root[\"title\"] = \"League of legends - San Diego 2018\") "}

The following lines show the value for the query key with the generated SQL API query:

SELECT VALUE root FROM root WHERE (root[\"title\"] = \"League of legends -San Diego 2018\")

Note that LINQ uses root as the alias name followed by the property name enclosed in square brackets, backslashes, and double quotes to access each key, instead of using the alias followed by a dot and the key name. For example, LINQ generates the following expression to evaluate whether a title is equal to "League of legends - San Diego 2018":

root[\"title\"] = \"League of legends - San Diego 2018\"

The previous line is equivalent to the following expression:

root.title = "League of legends - San Diego 2018"

We can copy the query value and paste it in our favorite tool to check the results.

Test your knowledge

  1. Which of the following methods is an asynchronous method declared in the Microsoft.Azure.Documents.Linq namespace that allows us to execute a query with an aggregate function to compute the total number of elements:
    1. AsCountQuery
    2. Count
    3. CountAsync
  2. Which of the following attributes specify Json.Net that we want to serialize an enum to its string representation:
    1. [JsonConverter(typeof(StringEnumConverter))]
    2. [JsonConverter(typeof(String))]
    3. [JsonConverter(typeof(EnumToString))]
  3. Which of the following methods executes a query to a Cosmos DB document collection with an asynchronous execution and returns a result set based on the MaxItemCount value specified in the FeedOptions instance:
    1. ExecuteNextAsync
    2. AsDocumentQuery
    3. QueryAsync
  4. Which of the following attributes specify Json.Net that we want to map a property to the prize key in the JSON document:
    1. [JsonProperty(Name = "prize")]
    2. [JsonProperty(PropertyName = "prize")]
    3. [JsonProperty(KeyName = "prize")]
  5. Which of the following methods reads a document with an asynchronous execution:
    1. ReadDocument
    2. ReadDocumentAsync
    3. ReadNextDocumentAsync

Summary

In this chapter, we learned how to take advantage of strongly typed code in C#, LINQ, and Cosmos DB. First, we created models and other necessary types, and we customized the default serialization provided by Json.NET to match our specific needs.

We worked with the main classes of the Cosmos DB SDK for .NET Core and we built a second version of a .NET Core 2 application, which interacts with Cosmos DB by using POCOs instead of dynamic objects. We wrote code to retrieve instances of a specific class with asynchronous LINQ queries.

We wrote code that persisted strongly typed objects into documents that represent competitions. We read and updated existing documents with POCOs and we composed and executed single-partition and cross-partition queries with LINQ.

Now that we have a very clear understanding of how to use of POCOs and LINQ with the .NET Core SDK to perform create, read, and update operations with Cosmos DB, we will learn how to optimize the resource units consumed, work with different indexing options, and monitor an application that uses Cosmos DB, which are the topics we are going to discuss in the next chapter.