Click here to Skip to main content
15,861,125 members
Articles / Web Development / HTML

RESTful Day #9: OData in ASP.NET Web APIs

Rate me:
Please Sign up or sign in to vote.
4.98/5 (27 votes)
1 Apr 2016CPOL12 min read 90.5K   5.5K   40   15
This is the last article of the RESTful series in which I’ll explain how you can leverage OData capabilities in ASP.NET WebAPI. I’ll explain what OData is and we’ll create OData enabled RESTful services.

Table of Contents

Introduction

This is the last article of the RESTful series in which I’ll explain how you can leverage OData capabilities in ASP.NET WebAPI. I’ll explain what OData is and we’ll create OData enabled RESTful services. I’ll try to keep the article very concise with less theory and more practical implementations.

Roadmap

The following is the roadmap I have setup to learn WebAPI step by step:

I’ll purposely use Visual Studio 2010 and .NET Framework 4.0 because there are a few implementations that are very hard to find in .NET Framework 4.0, but I’ll make it easy by showing how we can do it.

OData

OData is a protocol that provides a flexibility of creating queryable REST services. It provides certain query options through which the on demand data can be fetched from the server by the client over HTTP.

The following is the definition from ASP.NET:

"The Open Data Protocol (OData) is a data access protocol for the web. OData provides a uniform way to query and manipulate data sets through CRUD operations (create, read, update, and delete)."

More elaborated from:

"OData defines parameters that can be used to modify an OData query. The client sends these parameters in the query string of the request URI. For example, to sort the results, a client uses the $orderby parameter:

http://localhost/Products?$orderby=Name

The OData specification calls these parameters query options. You can enable OData query options for any Web API controller in your project — the controller does not need to be an OData endpoint. This gives you a convenient way to add features such as filtering and sorting to any Web API application."

Suppose our product table in the database contains more than 50000 products and we want to fetch only the top 50 products based on certain conditions like product id or price or name. As per our current implementation of the service, I’ll have to fetch all the products from the server database and filter them on client. Another option could be that I fetch the data at server only and filter the same and send the filtered data to client. In both the cases I am bearing the cost of writing extra code for filtering the data. Here is where OData comes into the picture. OData allows you to create services that are queryable. If the endpoints of the exposed services are OData enabled, or supports OData query options then the service implementation would consider the OData request and process it accordingly. So had that request for 50 records been an OData request, the service would have fetched only 50 records from the server. Not only filtering, but OData provides features like searching, sorting, skipping the data, selecting the data too. I’ll explain the concept with practical implementation. We’ll use our already-created service and modify them to be enabled for OData query options.

Image 1

Query Options

The following are the OData query options that ASP.NET WebAPI supports:

  1. $orderby: Sorts the fetched record in particular order like ascending or descending.
  2. $select: Selects the columns or properties in the result set. Specifies which all attributes or properties to include in the fetched result.
  3. $skip: Used to skip the number of records or results. For example, I want to skip first 100 records from the database while fetching complete table data, then I can make use of $skip.
  4. $top: Fetches only top n records. For e.g. I want to fetch top 10 records from the database, then my particular service should be OData enabled to support $top query option.
  5. $expand: Expands the related domain entities of the fetched entities.
  6. $filter: Filters the result set based on certain conditions, it is like where clause of LINQ. For e.g. I want to fetch the records of 50 students who have scored more than 90% marks, and then I can make use of this query option.
  7. $inlinecount: This query option is mostly used for pagination at client side. It tells the count of total entities fetched from the server to the client.

Setup Solution

When you take the code base from my last article and open it in Visual Studio, you’ll see the project structure something like as shown in below image:

Image 2

The solution contains the WebAPI application and related projects.

Step1: Click on Tools-> Library Package manager-> Package manager console

Image 3

Step2: In Package manager console, select default project as WebApi and run the command: Install-Package Microsoft.AspNet.WebApi.OData -Version 4.0.0

Note that, since we are using VS 2010 and .NET framework 4.0, we need to install OData libraries compatible to it.

Image 4

The command will download few dependent packages and reference the DLL in your project references. You’ll get the OData reference DLL in your project references.

Image 5

Our project is set to make OData endpoints. You can create new services. I’ll modify my existing services to demonstrate the OData working.

OData Endpoints

Open the ProductController class in WebAPI project and go to the Get() method. This method fetches all the product records from the database. The following is the code:

SQL
[GET("allproducts")]
[GET("all")]
public HttpResponseMessage Get()
{
    var products = _productServices.GetAllProducts();
    var productEntities = products as List<ProductEntity> ?? products.ToList();
    if (productEntities.Any())
        return Request.CreateResponse(HttpStatusCode.OK, productEntities);
    throw new ApiDataException(1000, "Products not found", HttpStatusCode.NotFound);
}

Let’s run the code through test client. Just run the application and we get:

Image 6

Append/help in the URL and press enter, you’ll see the test client.

Image 7

Since our product Controller is secured, we need to get an authenticated token from the service and use the same to access product Controller methods. To read about WebAPI security, refer to this article. Click on POST authenticate API method and get the TestAPI page of test client.

Image 8

Let’s send the request with credentials now. Just add a header with the request. The header should be like:

Authorization : Basic YWtoaWw6YWtoaWw=

Here "YWtoaWw6YWtoaWw=" is my Base64 encoded user name and password in database i.e. akhil:akhil

If authorized, you’ll get a Token. Just save that token for making further calls to product Controller.

Now open your product controller’s "allproducts" endpoint in test client.

Image 9

Test the endpoint.

Image 10

We get a response with all six products:

Image 11

I’ll use this controller method and make it an OData endpoint and perform several query options over it.

Add an attribute named [Queryable] above the method and in Request.CreateResponse mark the productEntities to productEntities.AsQueryable().

SQL
[Queryable]
[GET("allproducts")]
[GET("all")]
public HttpResponseMessage Get()
{
    var products = _productServices.GetAllProducts().AsQueryable();
    var productEntities = products as List<ProductEntity> ?? products.ToList();
    if (productEntities.Any())
        return Request.CreateResponse(HttpStatusCode.OK, productEntities.AsQueryable());
    throw new ApiDataException(1000, "Products not found", HttpStatusCode.NotFound);
}

$top

Now test the API with the $top query option.

Image 12

Here in the above endpoint, I have just appended "?$top=2" in the endpoint of the service (like we append query strings). This statement means that I want to fetch only the top two products from the service and the result is:

Image 13

We get only two products. So you can see here that it was very simple to make a service endpoint queryable and we did not have to write a new service to achieve this result. Let us try few more options.

$filter

You can perform all the filtering over the records with this option. Let us try the $filter query option. Suppose we need to fetch all the products whose name is "computer." You can use the same endpoint with filtering as shown below.

Image 14

I used $filter=ProductName eq 'computer' as a query string, which means fetching the product having the product name "computer." As a result, we get only one record from the products list because there was only one record having product name as "computer."

Image 15

You can use the filter in many different ways as shown below.

Return all products with name equal to "computer."

http://localhost:50875/v1/Products/Product/allproducts?$filter=ProductName eq "computer"

Return all products with id less than 3.

http://localhost:50875/v1/Products/Product/allproducts?$filter=ProductId lt 3

Image 16

Logical operators: Return all products where id >= 3 and id <= 5.

http://localhost:50875/v1/Products/Product/allproducts?$filter=ProductId ge 3 and ProductId le 5

Image 17

String functions: Return all products with "IPhone" in the name.

http://localhost:50875/v1/Products/Product/allproducts?$filter=substringof('IPhone',ProductName)

Image 18

The filter option could also be applied on date fields as well.

$orderby

Let us try the orderby query with the same endpoint.

Return all products with sorting on product name descending

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductName desc

Output

[
   {
      "ProductId":6,
      "ProductName":"Watch"
   },
   {
      "ProductId":8,
      "ProductName":"Titan Watch"
   },
   {
      "ProductId":9,
      "ProductName":"Laptop Bag"
   },
   {
      "ProductId":1,
      "ProductName":"Laptop"
   },
   {
      "ProductId":11,
      "ProductName":"IPhone 6S"
   },
   {
      "ProductId":10,
      "ProductName":"IPhone 6"
   },
   {
      "ProductId":4,
      "ProductName":"IPhone"
   },
   {
      "ProductId":12,
      "ProductName":"HP Laptop"
   },
   {
      "ProductId":2,
      "ProductName":"computer"
   },
   {
      "ProductId":5,
      "ProductName":"Bag"
   }
]

Return all products with sorting on product name ascending

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductName asc

Output

[
  {
    "ProductId": 5,
    "ProductName": "Bag"
  },
  {
    "ProductId": 2,
    "ProductName": "computer"
  },
  {
    "ProductId": 12,
    "ProductName": "HP Laptop"
  },
  {
    "ProductId": 4,
    "ProductName": "IPhone"
  },
  {
    "ProductId": 10,
    "ProductName": "IPhone 6"
  },
  {
    "ProductId": 11,
    "ProductName": "IPhone 6S"
  },
  {
    "ProductId": 1,
    "ProductName": "Laptop"
  },
  {
    "ProductId": 9,
    "ProductName": "Laptop Bag"
  },
  {
    "ProductId": 8,
    "ProductName": "Titan Watch"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  }
]

Return all products with sorting on product id descending

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductId desc

Output

[
  {
    "ProductId": 12,
    "ProductName": "HP Laptop"
  },
  {
    "ProductId": 11,
    "ProductName": "IPhone 6S"
  },
  {
    "ProductId": 10,
    "ProductName": "IPhone 6"
  },
  {
    "ProductId": 9,
    "ProductName": "Laptop Bag"
  },
  {
    "ProductId": 8,
    "ProductName": "Titan Watch"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  },
  {
    "ProductId": 5,
    "ProductName": "Bag"
  },
  {
    "ProductId": 4,
    "ProductName": "IPhone"
  },
  {
    "ProductId": 2,
    "ProductName": "computer"
  },
  {
    "ProductId": 1,
    "ProductName": "Laptop"
  }
]

Return all products with sorting on product id ascending

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductId asc

Output

[
  {
    "ProductId": 1,
    "ProductName": "Laptop"
  },
  {
    "ProductId": 2,
    "ProductName": "computer"
  },
  {
    "ProductId": 4,
    "ProductName": "IPhone"
  },
  {
    "ProductId": 5,
    "ProductName": "Bag"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  },
  {
    "ProductId": 8,
    "ProductName": "Titan Watch"
  },
  {
    "ProductId": 9,
    "ProductName": "Laptop Bag"
  },
  {
    "ProductId": 10,
    "ProductName": "IPhone 6"
  },
  {
    "ProductId": 11,
    "ProductName": "IPhone 6S"
  },
  {
    "ProductId": 12,
    "ProductName": "HP Laptop"
  }
]

$orderby with $top

You can make use of multiple query options to fetch the desired records. Suppose I need to fetch only five records from top order by ProductId ascending. To achieve this, I can write the following query.

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductId asc&$top=5

Output

[
  {
    "ProductId": 1,
    "ProductName": "Laptop"
  },
  {
    "ProductId": 2,
    "ProductName": "computer"
  },
  {
    "ProductId": 4,
    "ProductName": "IPhone"
  },
  {
    "ProductId": 5,
    "ProductName": "Bag"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  }
]

The above output fetches five records with sorted ProductId.

$skip

As the name suggests, the skip query option is used to skip the record. Let’s consider following scenarios.

Select top 5 and skip 3

http://localhost:50875/v1/Products/Product/allproducts?$top=5&$skip=3

Output

[
  {
    "ProductId": 5,
    "ProductName": "Bag"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  },
  {
    "ProductId": 8,
    "ProductName": "Titan Watch"
  },
  {
    "ProductId": 9,
    "ProductName": "Laptop Bag"
  },
  {
    "ProductId": 10,
    "ProductName": "IPhone 6"
  }
]

$skip with $orderby

Order by ProductName ascending and skip 6

http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductName asc &$skip=6

Output

[
  {
    "ProductId": 1,
    "ProductName": "Laptop"
  },
  {
    "ProductId": 9,
    "ProductName": "Laptop Bag"
  },
  {
    "ProductId": 8,
    "ProductName": "Titan Watch"
  },
  {
    "ProductId": 6,
    "ProductName": "Watch"
  }
]

The following are some standard filter operators and query functions you can use to create your query taken from https://msdn.microsoft.com/en-us/library/gg334767.aspx

Standard filter operators

The Web API supports the standard OData filter operators listed in the following table.

OperatorDescriptionExample
Comparison Operators
eqEqual$filter=revenue eq 100000
neNot Equal$filter=revenue ne 100000
gtGreater than$filter=revenue gt 100000
geGreater than or equal$filter=revenue ge 100000
ltLess than$filter=revenue lt 100000
leLess than or equal$filter=revenue le 100000
Logical Operators
andLogical and$filter=revenue lt 100000 and revenue gt 2000
orLogical or$filter=contains(name,'(sample)') or contains(name,'test')
notLogical negation$filter=not contains(name,'sample')
Grouping Operators
( )Precedence grouping(contains(name,'sample') or contains(name,'test')) and revenue gt 5000

Standard query functions

The web API supports these standard OData string query functions.

FunctionExample
contains$filter=contains(name,'(sample)')
endswith$filter=endswith(name,'Inc.')
startswith$filter=startswith(name,'a')

Paging

You can create a paging enabled endpoint, which means if you have a lot of data on the database and the requirement is that the client needs to show data with like, ten records per page. So it is advisable that the server itself should send those ten records per request, so that the entire data payload does not travel on the network. This may also improve the performance of your services.

Let’s suppose you have 10000 records on the database. You can enable your endpoint to return ten records and entertain the request for the initial record and the number of records to be sent. In this case, client will make the request every time for the next set of records and the fetch pagination option is used, or the user navigates to the next page. To enable paging, just mention the page count at the [Queryable] attribute. For example, [Queryable(PageSize = 10)]

So our method code becomes:

SQL
[Queryable(PageSize = 10)]
[GET("allproducts")]
[GET("all")]
public HttpResponseMessage Get()
{
    var products = _productServices.GetAllProducts().AsQueryable();
    var productEntities = products as List<ProductEntity> ?? products.ToList();
    if (productEntities.Any())
        return Request.CreateResponse(HttpStatusCode.OK, productEntities.AsQueryable());
    throw new ApiDataException(1000, "Products not found", HttpStatusCode.NotFound);
}

Query Options Constraints

Image 19

You can put constraints over your query options too. Suppose you do not want the client to access filtering options or skip options, then at the action level you can put constraints to ignore that kind of API request. There are four types of Query Option constraints.

AllowedQueryOptions

Example : [Queryable(AllowedQueryOptions =AllowedQueryOptions.Filter | AllowedQueryOptions.OrderBy)]

The above example of query option states that only $filter and $orderby queries are allowed on the API.

SQL
[Queryable(AllowedQueryOptions =AllowedQueryOptions.Filter | AllowedQueryOptions.OrderBy)]
 [GET("allproducts")]
 [GET("all")]
 public HttpResponseMessage Get()
 {
     var products = _productServices.GetAllProducts().AsQueryable();
     var productEntities = products as List<ProductEntity> ?? products.ToList();
     if (productEntities.Any())
         return Request.CreateResponse(HttpStatusCode.OK, productEntities.AsQueryable());
     throw new ApiDataException(1000, "Products not found", HttpStatusCode.NotFound);
 }

So when I invoked the endpoint with $top query.

http://localhost:50875/v1/Products/Product/allproducts?$top=10

I got the following response:

Image 20

It says,

"Message": "The query specified in the URI is not valid.",

"ExceptionMessage": "Query option 'Top' is not allowed. To allow it, set the 'AllowedQueryOptions' property on QueryableAttribute or QueryValidationSettings."

That means it is not allowing other kind of queryoptions to work on this API endpoint.

AllowedOrderByProperties

Example : [Queryable(AllowedOrderByProperties = "ProductId")] // supply list of columns/properties

This means that the endpoint only supports sorting on the basis of ProductId. You can specify more properties for which you want to enable sorting. So as per the following code:

SQL
[Queryable(AllowedOrderByProperties = "ProductId")]
[GET("allproducts")]
[GET("all")]
public HttpResponseMessage Get()
{
    var products = _productServices.GetAllProducts().AsQueryable();
    var productEntities = products as List<ProductEntity> ?? products.ToList();
    if (productEntities.Any())
        return Request.CreateResponse(HttpStatusCode.OK, productEntities.AsQueryable());
    throw new ApiDataException(1000, "Products not found", HttpStatusCode.NotFound);
}

If I try to invoke the URL : http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductName desc

It gives error in response:

Image 21

Says,

"Message": "The query specified in the URI is not valid.",

"ExceptionMessage": "Order by 'ProductName' is not allowed. To allow it, set the 'AllowedOrderByProperties' property on QueryableAttribute or QueryValidationSettings."

The URL: http://localhost:50875/v1/Products/Product/allproducts?$orderby=ProductId desc will work fine.

AllowedLogicalOperators

Example : [Queryable(AllowedLogicalOperators = AllowedLogicalOperators.GreaterThan)]

In the above mentioned example, the statement states that only greaterThan (for example, the "gt" logical operator) is allowed in the query and query options with any other logical operator other that "gt" will return error. You can try it in your application.

AllowedArithmeticOperators

Example : [Queryable(AllowedArithmeticOperators = AllowedArithmeticOperators.Add)]

In the above mentioned example, the statement states that only Add arithmetic operator is allowed while API call. You can try it in your application.

Conclusion

Image 22

There are lot more things in OData that I cannot cover in one go. The purpose was to give an idea of what we can achieve using OData. You can explore more options and attributes and play around with REST API’s. I hope by you’ll be able to create a basic WebAPI application with all the required functionalities. The code base attached with all the articles in the series serves as a boilerplate for creating any Enterprise level WebAPI application. Keep exploring REST. Happy coding :) Download the complete source code from GitHub.

References

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

https://msdn.microsoft.com/en-us/library/azure/gg312156.aspx

Other Series

My other series of articles:

MVC: http://www.codeproject.com/Articles/620195/Learning-MVC-Part-Introduction-to-MVC-Architectu

OOP: http://www.codeproject.com/Articles/771455/Diving-in-OOP-Day-Polymorphism-and-Inheritance-Ear

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect https://codeteddy.com/
India India
Akhil Mittal is two times Microsoft MVP (Most Valuable Professional) firstly awarded in 2016 and continued in 2017 in Visual Studio and Technologies category, C# Corner MVP since 2013, Code Project MVP since 2014, a blogger, author and likes to write/read technical articles, blogs, and books. Akhil is a technical architect and loves to work on complex business problems and cutting-edge technologies. He has an experience of around 15 years in developing, designing, and architecting enterprises level applications primarily in Microsoft Technologies. He has diverse experience in working on cutting-edge technologies that include Microsoft Stack, AI, Machine Learning, and Cloud computing. Akhil is an MCP (Microsoft Certified Professional) in Web Applications and Dot Net Framework.
Visit Akhil Mittal’s personal blog CodeTeddy (CodeTeddy ) for some good and informative articles. Following are some tech certifications that Akhil cleared,
• AZ-304: Microsoft Azure Architect Design.
• AZ-303: Microsoft Azure Architect Technologies.
• AZ-900: Microsoft Azure Fundamentals.
• Microsoft MCTS (70-528) Certified Programmer.
• Microsoft MCTS (70-536) Certified Programmer.
• Microsoft MCTS (70-515) Certified Programmer.

LinkedIn: https://www.linkedin.com/in/akhilmittal/
This is a Collaborative Group

779 members

Comments and Discussions

 
QuestionValidate for Business Entities Pin
Member 1004263522-Nov-17 20:54
Member 1004263522-Nov-17 20:54 
QuestionVery clear and helpful Pin
frikrishna8-Jun-17 16:20
frikrishna8-Jun-17 16:20 
AnswerRe: Very clear and helpful Pin
Akhil Mittal9-Jun-17 20:24
professionalAkhil Mittal9-Jun-17 20:24 
Question$metadata URL to generate desktop client Pin
troika2021-Apr-17 22:42
troika2021-Apr-17 22:42 
QuestionMy vote of 5 with complex question Pin
Ashish Bachhav23-Dec-16 1:44
Ashish Bachhav23-Dec-16 1:44 
QuestionMy vote of 5 but one question Pin
sunset74727-Jul-16 4:06
sunset74727-Jul-16 4:06 
QuestionRe: My vote of 5 but one question Pin
Member 128373207-Nov-16 6:33
Member 128373207-Nov-16 6:33 
AnswerRe: My vote of 5 but one question Pin
Bernhard Marx1-Dec-16 3:03
Bernhard Marx1-Dec-16 3:03 
GeneralMy vote of 5 Pin
D V L7-Apr-16 7:30
professionalD V L7-Apr-16 7:30 
GeneralRe: My vote of 5 Pin
Akhil Mittal7-Apr-16 18:18
professionalAkhil Mittal7-Apr-16 18:18 
GeneralMy vote of 5 Pin
Newnz3-Apr-16 22:15
Newnz3-Apr-16 22:15 
GeneralRe: My vote of 5 Pin
Akhil Mittal3-Apr-16 23:29
professionalAkhil Mittal3-Apr-16 23:29 
GeneralRe: My vote of 5 Pin
Newnz4-Apr-16 1:56
Newnz4-Apr-16 1:56 
GeneralMy vote of 5 Pin
prashita gupta1-Apr-16 7:40
prashita gupta1-Apr-16 7:40 
GeneralRe: My vote of 5 Pin
Akhil Mittal2-Apr-16 6:50
professionalAkhil Mittal2-Apr-16 6:50 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.