OData

OData provide functionality of processing data directly inside the server on the data provided by ORM or the service itself, and recive a minimal version of data, which is useful to the client.

OData functionality can be achived using $ signed http query string options


Query Options

The Query Options are used to define the process that should be made over source data...
Microsoft Implemented a limited version of OData, and involving it's ORM, and Custom Models, this limitation increase, due to that, all of these operation may not work at all times..
The Main Operation are below:

Filter

To filter data using OData you need to use $filter... and assign the actual filter you want
So, a simple filter consists of "$filter=" part and a conditional expression in front of it which will appear inside a query string part of a URL

For example, to filter flights that they have capacity of greater than 0, we will use followig url:
http://<flightServiceUrl>/flights?$filter=Capacity gt 0

and it's encoded version can look like this:
http://<flightServiceUrl>/flights?%24filter=Capacity+gt+0

Note that the operand are case sensitive.

to build a filter you can use these operators:

Operator Description Example
Logical Operators
Eq Equal /Suppliers?$filter=Address/City eq 'Redmond'
Ne Not equal /Suppliers?$filter=Address/City ne 'London'
Gt Greater than /Products?$filter=Price gt 20
Ge Greater than or equal /Products?$filter=Price ge 10
Lt Less than /Products?$filter=Price lt 20
Le Less than or equal /Products?$filter=Price le 100
And Logical and /Products?$filter=Price le 200 and Price gt 3.5
Or Logical or /Products?$filter=Price le 3.5 or Price gt 200
Not Logical negation /Products?$filter=not endswith(Description,'milk')
Arithmetic Operators
Add Addition /Products?$filter=Price add 5 gt 10
Sub Subtraction /Products?$filter=Price sub 5 gt 10
Mul Multiplication /Products?$filter=Price mul 2 gt 2000
Div Division /Products?$filter=Price div 2 gt 4
Mod Modulo /Products?$filter=Price mod 2 eq 0
Grouping Operators
( ) Precedence grouping /Products?$filter=(Price sub 5) gt 10

Also following function can be of help in a more complex query:

Function Example
String Functions
bool substringof(string po, string p1) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=substringof('Alfreds', CompanyName) eq true
bool endswith(string p0, string p1) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=endswith(CompanyName, 'Futterkiste') eq true
bool startswith(string p0, string p1) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=startswith(CompanyName, 'Alfr') eq true
int length(string p0) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=length(CompanyName) eq 19
int indexof(string p0, string p1) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=indexof(CompanyName, 'lfreds') eq 1
string replace(string p0, string find, string replace) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=replace(CompanyName, ' ', '') eq 'AlfredsFutterkiste'
string substring(string p0, int pos) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=substring(CompanyName, 1) eq 'lfreds Futterkiste'
string substring(string p0, int pos, int length) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=substring(CompanyName, 1, 2) eq 'lf'
string tolower(string p0) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=tolower(CompanyName) eq 'alfreds futterkiste'
string toupper(string p0) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=toupper(CompanyName) eq 'ALFREDS FUTTERKISTE'
string trim(string p0) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=trim(CompanyName) eq 'Alfreds Futterkiste'
string concat(string p0, string p1) https://services.odata.org/Northwind/Northwind.svc/Customers?$filter=concat(concat(City, ', '), Country) eq 'Berlin, Germany'
Date Functions
int day(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=day(BirthDate) eq 8
int hour(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=hour(BirthDate) eq 0
int minute(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=minute(BirthDate) eq 0
int month(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=month(BirthDate) eq 12
int second(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=second(BirthDate) eq 0
int year(DateTime p0) https://services.odata.org/Northwind/Northwind.svc/Employees?$filter=year(BirthDate) eq 1948
Math Functions
double round(double p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=round(Freight) eq 32d
decimal round(decimal p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=round(Freight) eq 32
double floor(double p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=round(Freight) eq 32d
decimal floor(decimal p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=floor(Freight) eq 32
double ceiling(double p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=ceiling(Freight) eq 33d
decimal ceiling(decimal p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=floor(Freight) eq 33
Type Functions
bool IsOf(type p0) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=isof('NorthwindModel.Order')
bool IsOf(expression p0, type p1) https://services.odata.org/Northwind/Northwind.svc/Orders?$filter=isof(ShipCountry, 'Edm.String')

Order By

Using the $orderby query option you can sort data, and using comma, for non-distinct values, you can define secondary sort options too

For example if you want to sort flight by date and flight number, you can do the following:
http://<flightServiceUrl>/flights?$orderby=Date,FlightNumber desc

By defult sort operation will return data in a ascending order, for in above case, first we returned flight by Date in ascending order, and for flights with same date, we returned them in a descending order

Top & Skip

Using $top query option, we can limit the result of query, from the top of the list
And using $skip we can ignore some of the result from the top

For example:
To get 5 top item in the list:
http://<flightServiceUrl>/flights?$top=5

To ignore 10 first item in the list:
http://<flightServiceUrl>/flights?$skip=5

to get the 3rd page, when each page size is 10
http://<flightServiceUrl>/flights?$top=30&$skip=20

Depend on the source ORM, or data processor, you may be forced to include $orderby query option too, otherwise it may generate errors.

Select

using $select query option, we can project the property that are included in the results.
for example, if you want to know only the Flight Number, and Airline of a flight service, you will use following query: http://<flightServiceUrl>/flights?$select=FlightNumber,Airline




References

https://www.odata.org/documentation/odata-version-2-0/uri-conventions/#CustomerQueryOptions