Data Points - Creating Azure Functions That Can Read from Cosmos DB with Almost No Code

Data Points - Creating Azure Functions That Can Read from Cosmos DB with Almost No Code

By Julie Lerman | February 2018 | Get the Code

Read the entire EF Core 2 and UWP App Data series:

Using EF Core 2 and Azure Functions to Store UWP App Data Locally and Globally, Part 1
Using EF Core 2 and Azure Functions to Store UWP App Data Locally and Globally, Part 2
Using EF Core 2 and Azure Functions to Store UWP App Data Locally and Globally, Part 3
Using EF Core 2 and Azure Functions to Store UWP App Data Locally and Globally, Part 4

Julie LermanIn my previous two columns, I created a simple Universal Windows Platform (UWP) game, CookieBinge, that tracks the number of cookies a game player gobbles up. In the first of those columns, I showed you how to use Entity Framework Core 2 (EF Core 2) to store the game scores locally on the Windows 10 device where the game is being played. In the second column, I walked you through building an Azure Function that would take game scores sent over HTTP and store them into a Cosmos DB database. The ultimate goal is to modify the game to be able to send its scores to this Function, as well as to retrieve score data from parallel functions.

In this column, I’ll show you how to create the Azure Functions that will respond to requests to retrieve two sets of score data: the top five scores for one user across all of that user’s devices, and the top five scores of all users playing the CookieBinge game around the world. Next time, in the final column in the series, I’ll integrate the new Azure Functions into the CookieBinge UWP app to allow users to share and compare their scores.

The Azure Function I created in the previous article responds to an HTTP call and, using the Function’s integrations, is able to push the data from the HTTP request into the Cosmos DB database without me having to write any data access code. Figure 1 displays one of the documents stored in the Binges collection of the CookieBinge database. The first seven properties are what I stored (“logged” through “worthit”) and the rest are metadata created by Cosmos DB when the record was inserted.

A Record from the CookieBinge Binges Collection
Figure 1 A Record from the CookieBinge Binges Collection

For the next two functions, I’ll again take advantage of Azure Function integrations to interact with the same Cosmos DB database. The only code required for the database interaction this time will be some SQL that represents the necessary query string, and this gets built into the integrations, not the function’s code. Both functions will be triggered by HTTP calls. The first will take in the ID of the user making the request and then return the top five scores the user logged across all of the devices on which they played the game. The second won’t take any input; it will simply return the top five scores of all gamers from around the world.

I’ll walk you through building the first of these Functions using the Azure portal workflow, adding on to the lessons of the previous column. Walking through the first of these functions should help you comprehend how all of the puzzle pieces fit together. I’ll then show you a shortcut when it’s time to build the second. Remember that it’s also possible to create Azure Functions with the tooling built into Visual Studio 2017, as well as by using the Functions command-line interface (CLI) plus Visual Studio Code.

Creating a Function to Return a User’s Top Scores

Let’s return now to the CookieBinge Azure Functions app in the Azure portal and add a new Function to the app. Recall that when you hover over the Functions group header under the function app’s name, you’ll see a plus sign on which you can click. When you do, you’ll be prompted to choose a template for the new function and, as with the first function you created, it will be a C# HTTP trigger. Name the new function GetUserScores, change the authorization level to Anonymous, then click Create.

Before modifying the code in the run.csx file, I’ll build the function’s integrations. Recall that there are three types of integrations:

  • Trigger: There can only be a single trigger and in this case, it’s the HTTP request to the Function.
  • Input integrations: There can be multiple input integrations and these represent other data you want to be available to the Function.
  • Output integrations: In the previous article, I wired up an output integration to the CookieBinge Cosmos DB database, which caused the results of the Function to be stored into the database. With this new GetUserScores function, the output will be the results of the Function sent back to the requestor as an HTTP response.

I’ll wire up the input integration to retrieve data from the Cosmos DB database where the StoreScores Function from last month’s article stored them. This input was a confusing concept for me at first. I assumed I’d have to write some code in the Function to connect to that database and query for the users’ scores. But the input integration is pretty cool. I can define it with the relevant SQL for querying the Cosmos DB database and let it know to use the UserId value that was sent in with the HTTP request. And Azure Functions takes care of the rest—connecting to the database, executing the query and handing me the query results to work with in the function code. Yes, very cool indeed! Let’s do it.

Creating an Input Integration to Read from Cosmos DB Using HTTP Request Data

Start by clicking New Input in the Integrations panel, then from the list of input types presented, choose Cosmos DB and click the Select button below it. This results in the Cosmos DB input form, its fields populated with default values. Modify the values as shown in Figure 2.

The Azure Cosmos DB Input Integration Settings
Figure 2 The Azure Cosmos DB Input Integration Settings

The values for the Database name, Collection Name and Azure Cosmos DB account connection should match the ones you used for creating the StoreScores function’s output integration. In my case, that’s CookieBinge, Binges and datapointscosmosdb_DOCUMENTDB. I’ve named my parameter “documents,” which I’ll use in my function’s code. Document ID is left blank. If you were building a function to retrieve a single Cosmos DB document by its ID, this would be a handy option. But this function will be querying based on an incoming UserId.

The SQL Query is cut off in the screenshot, so here it is in full. Keep in mind that you need to add it as a single line, though here I’ve used line returns to make it easier to read:

SELECT  TOP 5 c.score,c.worthit,c.deviceName,c.dateTime FROM c WHERE c.userId={userId} ORDER by c.score DESC

Azure Cosmos DB has a SQL query syntax that makes it nicely familiar to work with.

Notice the placeholder for the userID variable, {userId}. I’ll use this variable to wire up the trigger and this input integration. First, I need to save the settings of this input. Then I’ll need to create a binding parameter to bind the trigger to the input. This can be done in a way that was not immediately obvious to me. In fact, before I got it set up correctly, I kept getting an error about the binding parameter being missing and it took some effort putting together what I was learning from the docs, Web search results and some experimentation before I got it right. Part of the problem was my lack of understanding, although once I found the solution, the connections made sense to me. Hopefully you’ll see the light more quickly because I’m about to spoon-feed the solution to you!

Select the HTTP (req) trigger and specify a route template for the trigger. The template should first provide a name for the route, and then specify the binding variable, userId.

The route doesn’t have to match the name of the function, but it’s probably clearer if it does. I named mine GetUserScores/{userId}, as shown in Figure 3. Don’t forget to save your changes.

The HTTP Trigger Integration Settings
Figure 3 The HTTP Trigger Integration Settings

The output integration can stay at its default, which is an HTTP response.

Now it’s time to return to the function code, which you can do by clicking on the GetUserScores Function. This will display the run.csx code, which is still in its default state.

Writing the Function Code to Respond to the Integrations

Consider the SQL for the integration input, which retrieves four columns: score, worthit, deviceName and dateTime. You could create a class to match each type (as I did in the StoreScores Function of the previous article), or just tell the function that the input Function will be passing in a dynamic type. That’s what my code does. Figure 4 shows the entire function in the run.csx file.

Figure 4 The Run Method of the GetUserScores Function
using System.Net; public static HttpResponseMessage Run(string userId,                HttpRequestMessage req, IEnumerable<dynamic> documents, TraceWriter log) {   if (documents != null)   {     log.Info($"Document Count: {documents.Count()}");     return req.CreateResponse(HttpStatusCode.OK,documents);   }   else   {     return req.CreateResponse(HttpStatusCode.NotFound);   } }

In its signature, the Run method acknowledges that the first parameter is a string named userId that comes in via the route specified in the trigger settings. After the userId, the parameters expect the HttpRequestMessage (req) and then the IEnumerable containing the documents. Behind the scenes, the Function will take the userId that came in through the trigger’s route and pass it on to the input integration, which will execute the query (using the userId) and populate the documents variable with the query results. After this, the Function will finally call the Run method’s logic that I’ve written.

The code first checks that the documents were, indeed, passed in to the method. I output a message to my log with the count of those documents and then simply pass them back in the HTTP response. If I weren’t doing any checks and balances, the method would still work in this simpler format:

public static HttpResponseMessage Run(string userId,               HttpRequestMessage req, IEnumerable<dynamic> documents) {   return req.CreateResponse(documents); }

In other words, for this entire Function to receive a userID and return that user’s top five scores from the database takes just a single line of code that’s no more than “send back the documents that came in from the input integration.”

The Azure Function’s integrations have really done all the work for me. I am truly impressed.

I can test this Function in the portal or in a tool like Fiddler or Postman. Testing from a browser won’t work with the Function in its current state because the Function is returning dynamic objects and a browser can’t send the necessary Accept header. Alternatively, you could define a class that matches the query results and use that class instead of a dynamic object. But as it is, the Function will work correctly when calling it from Fiddler, Postman or your APIs, such as the one you’ll eventually use from the UWP app.

The Get Function URL link tells me I can call https://cookie­­UserScores/{userId}. If the Function weren’t anonymous, then I’d have to pass some credentials in, but I’m intentionally keeping it simple here.

With my userId, 54321, in the placeholder, the URL looks like this:

This URL returns five JSON-formatted documents with the schema shaped by the query. Here’s one of the documents returned in the HTTP response, showing that when I played on my Xbox, I scarfed down 18 cookies, but wasn’t so happy about my binge that day.

{   "score": 18,   "worthit": false,   "deviceName": "XBox",   "dateTime": "2017-11-05T15:26:00" }
A Shortcut for Creating Another Fully Integrated Azure Function

Now that I have the first Function set up, it’s time to create the second, which will retrieve the top five scores for all players around the globe. Rather than go through all of the settings forms again, though, I’m going to take a short cut. The settings for all of the integrations of an Azure Function are stored in the function.json file, which you can open up in the portal from the View Files pane. Figure 5 shows the function.json for the GetUserScores Function. The bindings section wraps all of the integration settings.

Figure 5 The function.json File for GetUserScores
{ "bindings": [   {     "authLevel": "anonymous",     "name": "req",     "type": "httpTrigger",     "direction": "in",     "route": "GetUserScores/{userId}"   },   {     "name": "$return",     "type": "http",     "direction": "out"   },   {     "type": "documentDB",     "name": "documents",     "databaseName": "CookieBinge",     "collectionName": "Binges",     "sqlQuery": "SELECT  TOP {top} c.score,c.worthit,c.deviceName,       c.dateTime FROM c WHERE c.userId={userId} ORDER by c.score DESC",     "connection": "datapointscosmosdb_DOCUMENTDB",     "direction": "in"   } ], "disabled": false }

The first binding is the httpTrigger, noted in its type property. Note that all of the rest of its settings are described by the other properties the authLevel, name, direction and the route. Next you can see the http output binding and, finally, the input binding with all of the settings I specified in the form.

Now that you have a better understanding of all of the puzzle pieces of the Function, you don’t really need to go through the forms if you don’t want to. You can just create the function.json file directly and that’s what I’ll do for the second Function. You still need to add the new function to the Function app, so do that, again using a C# HttpTrigger template, and call it GetGlobalScores.

But this time, rather than going to the Integration section, open the function.json file in the View Files pane and replace it with the code in Figure 6. Note that the sqlQuery value is wrapped here in the listing, but that string should be on one line.

Figure 6 The function.json File for GetGlobalScores
{   "bindings": [     {       "authLevel": "anonymous",       "name": "req",       "type": "httpTrigger",       "direction": "in"     },     {       "name": "$return",       "type": "http",       "direction": "out"     },     {       "type": "documentDB",       "name": "documents",       "databaseName": "CookieBinge",       "collectionName": "Binges",       "connection": "datapointscosmosdb_DOCUMENTDB",       "direction": "in",       "sqlQuery": "SELECT  TOP 5 c.userName, c.score,c.worthit,c.deviceName,         c.dateTime FROM c ORDER by c.score DESC"     }   ],   "disabled": false }

Because this request doesn’t need to pass in an id, there’s no route on the httpTrigger binding as with the first Function. The output binding is the same as earlier. The only difference with the input binding is that there’s a different query that doesn’t filter on a userId.

Understanding and applying the bindings in their raw format can certainly save you a lot of time. Taking the explicit path of first using the forms to input the settings was beneficial, however, as it helped me comprehend the settings as I was first learning about them. In fact, now that you see how the configuration is stored, you are very well prepared to build your Azure Functions in Visual Studio or Visual Studio Code, rather than in the portal.

Now for the Function code, which is nearly identical to the code for the first Function, except that it doesn’t take in the userId binding as its first parameter. Here’s the modified signature of the Run method:

public static HttpResponseMessage Run(               HttpRequestMessage req, IEnumerable<dynamic> documents, TraceWriter log)

I manually modified some of the test data in my Cosmos DB Binges collection to be sure there were a number of different userId values before running the new GetGlobalUserScores Function to validate that everything was working correctly.

Linking the UWP App with the Azure Functions

With the three Azure Functions and the Cosmos DB document database for storing and retrieving user scores in hand, the final installment of this series will return to the UWP app to integrate those Functions. Recall that the UWP app currently uses Entity Framework Core 2 to store the user scores locally onto whatever Windows 10 device where the game is being played. In the next iteration, in addition to the local storage, a user’s scores (with their permission) will be sent to the StoreScores Azure Function to store in the cloud. And users will be able to retrieve a list of their own top scores across all the devices on which they’ve played, as well as to see the top scores of all players around the globe. The app will call the Functions created here in order to report that data to the user.

Please note that I’ll probably shut down my own demo Functions at the URLs noted earlier in this article. They’re hosted on my Visual Studio Subscription account, which is for testing purposes and has a spending limit.

Julie Lerman is a Microsoft Regional Director, Microsoft MVP, software team coach and consultant who lives in the hills of Vermont. You can find her presenting on data access and other topics at user groups and conferences around the world. She blogs at the and is the author of “Programming Entity Framework,” as well as a Code First and a DbContext edition, all from O’Reilly Media. Follow her on Twitter: @julielerman and see her Pluralsight courses at

Thanks to the following Microsoft technical expert for reviewing this article: Jeff Hollan
Jeff Hollan is a Senior Program Manager for Microsoft Azure Functions.  He joined Microsoft a little over 4 years ago and has spent time managing backend IT systems and integrations, and helping manage other products in Azure including Logic Apps. Jeff is passionate about all things tech, and is known for his presentations at conferences around the world. You can follow Jeff on Twitter @jeffhollan