{"id":3569,"date":"2020-01-05T01:37:22","date_gmt":"2020-01-05T08:37:22","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/odata\/?p=3569"},"modified":"2020-03-02T07:14:34","modified_gmt":"2020-03-02T14:14:34","slug":"integrating-cosmos-db-with-odata-part-3","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/odata\/integrating-cosmos-db-with-odata-part-3\/","title":{"rendered":"Integrating Cosmos DB with OData (Part 3)"},"content":{"rendered":"

Sometimes requiring to build an entire ASP.NET Core application just for the purpose of exposing a RESTFul API endpoint to give your API consumers the ability to query, filter and order the data could be a bit of an overhead, especially if you are not planning on adding in any additional business logic between your data and it’s consumers.<\/p>\n

It’s not also a very cost effective solution when it comes to the development effort, maintenance, infrastructure and all the bells and whistles that accompany developing a custom ASP.NET Core API.<\/p>\n

If that’s the case for your task, Cosmos DB offers native support for OData out of the box without having to add an additional layer of infrastructure.<\/p>\n

In this article, we will discuss how you can leverage Cosmos DB native support for OData while exposing an API and leveraging a secure reliable geo-redundant communications between your clients and your data resources.<\/p>\n

 <\/p>\n

Setting Things Up<\/h3>\n

Let’s start with Azure Dashboard where we need to setup up our infrastructure.<\/p>\n

1. At the front page of Azure Dashboard, Click on the plus sign to Create a resource\u00a0<\/strong>as highlighted in the screenshot below:<\/p>\n

\"\"<\/p>\n

2. Select Azure Cosmos DB<\/strong> Option from the list of cloud services presented in the next dialog:<\/p>\n

\"\"<\/p>\n

3. In this page we are going to setup all the information needed to create a Cosmos DB instance as shows in the following screenshot:<\/p>\n

\"\"<\/p>\n

After selecting the subscription you would like to use for your Cosmos DB, you will need to select the resource group where you need your Cosmos DB instance to reside. If you don’t have an existing resource group, you can click\u00a0Create new\u00a0<\/strong>link right below the resource group drop down to create a new resource group for your resources.<\/p>\n

You will also notice in the screenshot that we’ve chosen\u00a0studentsdata\u00a0<\/em>as an account name for the purpose of this demo, but the most important part of this setup is to select the right API<\/strong> to ensure you can leverage the native support of OData, in our demo here, you will need to select\u00a0Azure Table <\/strong>which is the API Cosmos DB offers with OData support as we will discuss further in this article.<\/p>\n

You can also enable the Geo-Redundancy option for your instance to enable global distribution on your account across multiple regions, you can also enable that option later as your data needs grow, additionally you can also enable the Multi-region Writes which should allow you to take advantage of the provisioned throughput for your databases and containers across the globe, for the purpose of this demo, we will keep both options disabled.<\/p>\n

4.Once you have accomplished the aforementioned steps, click the Review + Create\u00a0<\/strong>button to review all the information you’ve entered as follows:<\/p>\n

\"\"<\/p>\n

The process of creating a Cosmos DB instance takes between 5 – 10 minutes to be fully provisioned and deployed. Once the deployment is done click on\u00a0Go to resource <\/strong>blue button to start working with your Cosmos DB instance as shows in the following screenshot:<\/p>\n

\"\"\nLet’s move on to the next part of this article, which is to create a table with some data so we can examine our OData querying options.<\/p>\n

 <\/p>\n

Adding Some Data<\/h3>\n

To add some data to your new instance, we will need to create a table or container for this data first, in order for you to do that follow the following steps.<\/p>\n

1. Click on the\u00a0Data Explorer\u00a0<\/strong>option to navigate to data manipulation and querying page as shows in the following screenshot:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

2. Now in the new page, click\u00a0New Table\u00a0<\/strong>option at the top left corner to create a table that contains our sample data, once clicked, a dialog will appear from the right side to enable you to give your new data table a name, for the purpose of our demo here, we are going to call our new table\u00a0students\u00a0<\/em>as highlighted in the screenshot below:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

3. Now that your table is created, you will need to navigate to your table by expanding\u00a0TablesDB\u00a0<\/em>root item on the left hand in the navigation pane, then at the top click on\u00a0Add Entity\u00a0<\/strong>then start defining the properties of the student entity that you would like to store in the table as shows in the following screenshot:<\/p>\n

\"\"<\/p>\n

You will notice that the\u00a0PartitionKey\u00a0<\/em>and the\u00a0RowKey\u00a0<\/em>are mandatory keys that you cannot remove or modify their types, they play the role of primary key in an equivalent relational database system, but you can, however add unique values and query the data based on these values.<\/p>\n

Since both properties are of string data types, you can choose to add whichever values you want in there, stringified Guids, numbers or any other form of unique identifiers.<\/p>\n

But as it shows in the screenshot above, we needed to add more properties to our entity here, we added\u00a0Name\u00a0<\/em>with a data type string, and a\u00a0Score\u00a0<\/em>with data type Int32. now let’s add some entities for our demo as shows in the following screenshot:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Consuming the Data<\/h3>\n

This is the part where we consume the data that we have created and examine our OData querying against our the\u00a0students\u00a0<\/em>data table that we have created.<\/p>\n

But before we start writing any code, make sure you copy the\u00a0Connection String\u00a0<\/strong>of your Cosmos DB instance so you can establish a secure communication between your client and your Cosmos API.<\/p>\n

You can find the\u00a0Connection String\u00a0<\/strong>of your instance simply by going to the\u00a0Connection String\u00a0<\/strong>navigation option on the left hand side of your Azure instance and copying the\u00a0Primary Connection String <\/strong>as shows in the following screenshot:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

1. Now, let’s start by create a simple client as a Console Application\u00a0<\/strong>in Visual Studio to consume and query our data as follows:<\/p>\n

\"\"<\/p>\n

We will be going with a\u00a0Console App (.NET Core)\u00a0<\/strong>in this demo, let’s call our App\u00a0StudentsDataClient.<\/em><\/p>\n

2. We will need to add a nuget package to enable the authentication and communication, querying and navigation between our simple app and our Cosmos DB students table, the nuget package is\u00a0Microsoft.Azure.Cosmos.Table<\/strong><\/a> specifically version 1.0.6. You can add the package simply by using\u00a0Manage Nuget Packages\u00a0<\/strong>option in Visual Studio or by simply typing the following command in the\u00a0Package Manager Console:<\/strong><\/p>\n

Install-Package Microsoft.Azure.Cosmos.Table -Version 1.0.6<\/pre>\n
3. Now, that we had our project setup, let’s start by creating a model that reflects the same properties we have created in our students\u00a0<\/em>Cosmos DB table, so, in a new file let’s call it\u00a0Student.cs\u00a0<\/em>let’s create the following model:<\/p>\n
using Microsoft.Azure.Cosmos.Table;\r\n\r\nnamespace StudentsDataClient\r\n{\r\n    public class Student : TableEntity\r\n    {\r\n        public string? Name { get; set; }\r\n        public int? Score { get; set; }\r\n    }\r\n}\r\n<\/pre>\n
You will notice that our\u00a0Student\u00a0<\/strong>model here inherits from another model called\u00a0TableEntity,\u00a0<\/strong>this inheritance is necessary to include all the other properties that a Cosmos DB Table record has, such as the\u00a0PartitionKey, RowKey, Timestamp\u00a0<\/em>in addition to a native implementation of\u00a0ReadEntity\u00a0<\/em>and\u00a0WriteEntity\u00a0<\/em>functions which are needed to map a Cosmos DB record to a strongly typed model for both reading from and writing to your table.<\/p>\n
You can also override these methods and properties or simply replace\u00a0TableEntity\u00a0<\/strong>with\u00a0ITableEntity\u00a0<\/strong>and start implementing all the aforementioned properties and functions.<\/p>\n
You will also notice that we defined all our properties here as nullable with a question mark by the type of each, this enables us to ignore properties that are discarded in an OData query, otherwise the return result will be the initial value of the primitive type of the property, for instance,\u00a0Score\u00a0<\/em>would return 0’s.<\/p>\n
4. With our model being implemented, let’s instantiate a Cosmos DB client to establish a secure communication while retrieving everything in our table with an empty query as follows:<\/p>\n
using System.Threading.Tasks;\r\nusing Microsoft.Azure.Cosmos.Table;\r\n\r\nnamespace StudentsDataClient\r\n{\r\n    class Program\r\n    {\r\n        static async Task Main(string[] args)\r\n        {\r\n            string connectionString = \"YOUR_PRIMARY_CONNECTION_STRING\";\r\n            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);\r\n            CloudTableClient tableClient = storageAccount.CreateCloudTableClient();\r\n\r\n            CloudTable table = tableClient.GetTableReference(\"students\");\r\n            TableQuery<Student> tableQuery = new TableQuery<Student>();\r\n            \r\n            TableQuerySegment<Student> studentsSegment = \r\n                await table.ExecuteQuerySegmentedAsync(tableQuery, token: null);\r\n            \r\n            studentsSegment.Results.ForEach(i => \r\n                Console.WriteLine(\"{0,5} {1,10} {2,-10}\", i.PartitionKey, i.Name, i.Score));\r\n            \r\n            Console.ReadKey();\r\n        }\r\n    }\r\n}\r\n<\/pre>\n
Let’s walk through the previous code snippet:<\/p>\n
First of all make sure you reference\u00a0Microsoft.Azure.Cosmos.Table\u00a0<\/strong>in your usings so you can access all the models, methods and functionality the library offers.<\/p>\n
Secondly, make sure you change your main method from by synchronous to asynchronous by changing the return type from void to Task and adding the async option. you will need to do that so you can await and execute asynchronous calls in your Main method.<\/p>\n
Asynchronous Main methods were introduced with C# 7.1 – you will want to make sure you’re using that version or later to establish this.<\/p>\n
We have also used the\u00a0Connection String\u00a0<\/strong>variable to contain the reference we have copied from our Cosmos DB instance, please refer to the beginning of this part to find out where to get that value in Azure.<\/p>\n
The rest of the code is simply a validation\/parsing of the connection string then using a\u00a0CreateCloudTableClient\u00a0<\/strong>factory method to instantiate Cosmos DB table client for our further communications.<\/p>\n
We also, reference the\u00a0students\u00a0<\/em>table to target that particular data container in our Cosmos DB account, this step is important because a Cosmos DB account could contain many, many other tables.<\/p>\n
Our\u00a0TableQuery\u00a0<\/strong>in this basic example is completely empty, but in the next steps I’m going to show you how you can leverage that object to run OData queries shortly – but for now, running the code above results the following Console output as follows:<\/p>\n
    1     Hassan 155\r\n    2       Josh 133\r\n    3       Todd 189\r\n    4     Jackie 175\r\n    5    Sandeep 199\r\n    6      Kailu 211\r\n    7     Vishwa 183\r\n    8      Viral 125<\/pre>\n
<\/h3>\n
 <\/p>\n
OData Querying<\/h3>\n
Now that we have successfully retrieved all the data we entered in our Cosmos DB table, let’s execute some of the powerful features of OData.<\/p>\n
 <\/p>\n
Filtering<\/h4>\n
Let’s start with the filtering feature of OData to only return students with score higher than 150 as follows:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>\r\n{\r\n     FilterString = \"Score gt 150\"\r\n};<\/pre>\n
The result will be as follows:<\/p>\n
    1     Hassan 155\r\n    3       Todd 189\r\n    4     Jackie 175\r\n    5    Sandeep 199\r\n    6      Kailu 211\r\n    7     Vishwa 183\r\n<\/pre>\n
You can also establish the same filtering process by modifying your code as follows:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\ntableQuery.Where(\"Score gt 150\");<\/pre>\n
And here’s a third option as well:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\nstring filter = TableQuery.GenerateFilterConditionForInt(\"Score\", QueryComparisons.GreaterThan, 150);\r\ntableQuery.Where(filter);<\/pre>\n
For filtering with the last option, the library offers multiple methods for comparing several primitive data types, such as GenerateFilterConditionForBinary, GenerateFilterConditionForDate, GenerateFilterConditionForInt\u00a0<\/em>and many other options, the default method GenerateFilterCondition compares against strings by default.<\/p>\n
You can also use the same pattern to combine multiple filters as follows:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\n            \r\nstring filter = TableQuery.CombineFilters(\r\n     TableQuery.GenerateFilterConditionForInt(\"Score\", QueryComparisons.GreaterThan, 150),\r\n     TableOperators.And,\r\n     TableQuery.GenerateFilterCondition(\"Name\", QueryComparisons.Equal, \"Hassan\"));\r\n            \r\ntableQuery.Where(filter);<\/pre>\n
 <\/p>\n
Selecting<\/h4>\n
Now, let’s experiment with selecting particular properties off of our results, for instance, if we only care about students names, regardless of their score, we can implement the following code:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\nList<string> columns = new List<string> { \"Name\" };\r\ntableQuery.Select(columns);<\/pre>\n
The above code will yield the following results:<\/p>\n
    1     Hassan\r\n    2       Josh\r\n    3       Todd\r\n    4     Jackie\r\n    5    Sandeep\r\n    6      Kailu\r\n    7     Vishwa\r\n    8      Viral\r\n<\/pre>\n
The Score\u00a0<\/em>property are not in display in our code because we declared it as nullable property, therefore the Console writer will ignore it if not fulfilled by Cosmos API response.<\/p>\n
 <\/p>\n
Ordering<\/h4>\n
You can also execute ordering in an ascending or descending fashion based on a particular property such as\u00a0Name<\/em> as follows:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\ntableQuery.OrderBy(\"Name\");<\/pre>\n
The above code result the following details:<\/p>\n
    1     Hassan 155\r\n    4     Jackie 175\r\n    2       Josh 133\r\n    6      Kailu 211\r\n    5    Sandeep 199\r\n    3       Todd 189\r\n    8      Viral 125\r\n    7     Vishwa 183\r\n<\/pre>\n
You can also leverage the ordering in descending fashion by using\u00a0OrderByDesc\u00a0<\/em>the exact same way.<\/p>\n
 <\/p>\n
Take<\/h4>\n
You can also leverage the\u00a0Take\u00a0<\/strong>functionality by getting only a specific count of the final result to be returned as follows:<\/p>\n
TableQuery<Student> tableQuery = new TableQuery<Student>();\r\ntableQuery.Take(take: 3);<\/pre>\n
The above code should returning the following results:<\/p>\n
    1     Hassan 155\r\n    2       Josh 133\r\n    3       Todd 189\r\n<\/pre>\n
 <\/p>\n
Final Notes<\/h3>\n
    \n
  1. The Cosmos DB team has an extensive documentation about all the capabilities of the technology and thorough details on the client library you can find that documentation in this link.<\/a><\/li>\n
  2. There are few capabilities the library & the API do not offer such as\u00a0IN<\/strong> and Expand <\/strong>functionality, mainly due to the non-relational nature of Cosmos and Table API.<\/li>\n
  3. This is the link<\/a> to a github repository for the code we used in this article.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    Sometimes requiring to build an entire ASP.NET Core application just for the purpose of exposing a RESTFul API endpoint to give your API consumers the ability to query, filter and order the data could be a bit of an overhead, especially if you are not planning on adding in any additional business logic between your […]<\/p>\n","protected":false},"author":3348,"featured_media":3345,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-3569","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-odata"],"acf":[],"blog_post_summary":"
    Sometimes requiring to build an entire ASP.NET Core application just for the purpose of exposing a RESTFul API endpoint to give your API consumers the ability to query, filter and order the data could be a bit of an overhead, especially if you are not planning on adding in any additional business logic between your […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/3569","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/users\/3348"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/comments?post=3569"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/posts\/3569\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media\/3345"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/media?parent=3569"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/categories?post=3569"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/odata\/wp-json\/wp\/v2\/tags?post=3569"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}