OData Query syntax

Posted on: 2013-07-25

If you have setup WCF Data Service, you can now query your service with the OData syntax. If you are using Entity Framework, you will see a lot of similarity between Entity Framework and this string syntax. OData works with http request which is based on URI. Every queries statements need to be passed by this url. Here is a list of all possibles statement that can be used together as Entity Framework or a SQL statement.

OData ordering keyword : orderby

If you request one of your entity set, you may want to order the result. To be able to order, you will have to user orderby with = and the property of the entity. If you need multiply properties in your ordering, simply seperate them with a comma ,. Here is two examples.

http://yourwebsite/YourService.svc/YourEntitiesSet?$orderby=OneOfYourProperty

http://yourwebsite/YourService.svc/YourEntitiesSet?$orderby=OneOfYourProperty,ASecondProperty 

OData getting a limited amount of data keyword : top

This is the same keyword as Sql Server or Entity Framework. This let the consumer to ask to the WCF Data Service how many result maximum the consumer receive. It can be the specific number or less, or none. Here is an example.

 http://yourwebsite/YourService.svc/YourEntitiesSet?$top=10 

OData paging result keyword : skip

You can do paging with OData. For example, if you want to have a list that display 10 elements per page, you could request the WCF Data Service to get the first 10 entities and then ask the next 10 entities when the user request the next page. This can be done with the previous keyword top to get the 10 entities but to get which group of 10, we need to new keyword skip. If you want to have the third page of 10 elements, we will need to use skip with the value 20 and the top at 10. This can be translated by : "I want the third page of 10 elements". The value of skip is 20 because we have 10 elements by page. One page mean we display 10, two pages mean we display 20. The third page requires to skip those 20 pages and to display the next 10. This is specify with the top keyword.

 http://yourwebsite/YourService.svc/YourEntitiesSet?$skip=20&$top=10 

Condition with OData by the filter keyword

In SQL, we would use the keyword where and the keyword and if we have multiple conditions. It's the samething with Entity Framework. But, with OData, the keyword is not where. The keyword is filter.

 http://yourwebsite/YourService.svc/YourEntitiesSet?$filter=YourProperty equal('Test') 

OData contains several keyword to compare : http://go.microsoft.com/fwlink/?LinkId=186972

Joining entity with the keyword expand

Entity Framework gives the keyword include which add a reference to an other entity or a list of entities. You need to specify the name of the property and this one will be loaded in the foreign key that define the relationship in the database. With OData, the keyword is expand.

 http://yourwebsite/YourService.svc/YourEntitiesSet?$expand=YourPropertyThatLinkToAnOtherEntity 

Selecting specify properties of an entity with the OData keyword select

This is exactly the same as SQL and Entity. As you can see since the beginning of this article, a lot of keyword in OData are the same with Entity and SQL. This is a strong positive point that give a low learning curl. The select keyword give you the posibility to not returning the whole entity information but only specific property of this one.

 http://yourwebsite/YourService.svc/YourEntitiesSet?$select=Property1,Property2