ODATA Reads with Parameters

Data READs with Parameters

In this post, We'll focus on how to read a OData service with a parameter.

Service method with a parameter

Now, we'll see how to retrieve data with a parameter,

Url in the browser

In the browser, we can see only filtered students. in the url, append the parameter with the values.

Power Query Window

From the Power Query window, we can see the dataset like this.

ODATA Reads

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

All the relevant libraries are installing 

Register the OData endpoint 

Register the service endpoint in Register method of the WebApiConfig file

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

Student Controller implements OData Controller

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

How it works in the browser

now ping to the OData endpoint,

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

Read Students

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

Read Data types

We can read the 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 

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

But we get an error!!!!!!

It clearly says OData v3 or any earlier data feeds only can be integrated with Excel 2016 :)

But, for your information, 

With Office 2016 as well as the 2013, we can integrated 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.

 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.

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

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

Power Query 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.

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

ODATA Features

ODATA

ODATA is a open data protocol to access data using REST architecture. ODATA is a Microsoft standard, but available with Android & IOS platforms as well. ODATA supports for JSON and Atom formats. With the help of ODATA , we can access data in variety of sources like relational databases, Web sites, File systems and Content Management Systems.

ODATA has more features than REST!

REST is an architectural pattern to implement a service, But with ODATA it's going to provide more features to more sources like File systems, Content Management Systems.
ODATA services supports for query options like filter, select, expand, order by and many more.
As per our requirement, we can filter the result set, order as we want. And we can skip some rows, Even we can select the top row as well.

It's 2016!!!!

Microsoft has introduced variety of features with Office 2016, We can implement a simple power BI solution just with Excel itself, If your old REST services are using ODATA protocol :)

ODATA Versions so far 

After ODATA V2 and ODATA V3, Now ODATA V4 has been released as a major release.
If you want to build a open data service, we can use ODATA v4 with WebAPI 2.2 as a solution,