Data READs

Let’s start with a newer version of ODATA to access data from a existing service, In this solution I have used Web API 2.2 with ODATA v4

ODATA v4 package

Visual Studio has a built in package to create ODATA v4 services, Package contains all the libraries required to create a ODATA v4 endpoint.

Integrate ODATA v4 package into solution

We can add the necessary OData package into solution like this, Select Microsoft ASP.NET Web API 2.2 for OData v4.0

nuget package management store.

All the relevant libraries are installing

All the ODATA related libraries are installing.

Register OData endpoint

Register service endpoint in Register method of WebApiConfig file

Register service endpoints.

In line no 22, I have exposed the Student entity, Data read method is implemented in OData controller, StudentController

Student Controller implements OData Controller

Student Controller

StudentController inherits from OData Controller, So all the CRUD operations related to student can be implemented in this controller.

How it works in the browser

now ping to the OData endpoint,

Available entities

We can see all the available entities in the endpoint with metadata.

Read Students

Read Students

By appending entity, Student into url, we can read student data

Read Data types

Read data types

We can read data types of student using the $metadata tag

Excel 2016 with OData v4

We already know office is not only for manipulating documents, We can perform many advanced queries with Excel. Excel supports to fetch data from different other sources, newer update is OData sources

Excel 2016 - ODATA v4

In excel 2016, We can connect to a OData source like this,

Data connection wizard

But we get an error!!!!!!

error from Data connection wizard - office 2016 odata v4

It clearly says Excel 2016 can be only integrated with Odata v3 or any earlier data feeds :) But for your information, with Excel 2016 as well as Excel 2013, we can integrate OData v3

Excel 2013 & OData v4 ?

With Excel 2013, ODATA v4 is not compatible. But we can use a V3 format service with Excel 2013.

error - office 2013 odata v4

Excel 2016 with OData v4 - Power Query

With ODATA v4, we can’t use ODATA source option, But we have Power Query option to analyse data.

Excel 2016 - ODATA v4 with Power Query

In Data tab, we have many options to fetch data from different other sources, We can select From OData Feed option.

PowerQuery - Odata feed

We can use Office 2016 Power Query option with OData v4.

Power Query Editor

PowerQuery editor

This is the query editor, we can select/remove columns, can remove duplicates, group by a column, create a new column based on a mathematical function and much more advanced operations.

Query editor window

In the next section, we’ll see how to read data by passing a value :)

I could create a github repo for this code sample, Please feel free to check it, https://github.com/hansamaligamage/ODATAExample