ASP.NET Web API 2.0 and the new OData keywords

Web API gained OData Support with the Visual Studio 2012 Update 2 release. However it was missing support for some keywords like $select, $expand and $batch. Today we’ll see how to setup our ASP.NET project to use Web API 2.0 beta binaries in Visual Studio 2012 Update 2 and use the new Keywords.

Setting up a WebAPI 2.0 project using Visual Studio 2012

Step 1: We start our project with a Visual Studio 2012 ASP.NET Project. Note that we use .NET Framework 4.5. This is because Web API 2.0 takes dependency of .NET Framework 4.5.


Step 2: We select the Empty project template because we’ll be adding WebAPI pre-release dependencies so might as well start with an empty slate.


Open the Nuget Package Manager Console and fire away the following install commands:

1. Install WebAPI OData first thing. This installs the required WebAPI dependencies as well.

PM> install-package Microsoft.AspNet.WebApi.OData –pre

2. We will use WebAPI’s help pages to create dummy data. To do this, we need to uninstall the Microsoft.AspNet.Mvc.FixedDisplayMode package first. Once uninstalled, we install the Microsoft.AspNet.WebApi.HelpPage pre-release package.

PM> uninstall-package Microsoft.AspNet.Mvc.FixedDisplayModes
PM> install-package Microsoft.AspNet.WebApi.HelpPage –pre
PM> install-package WebApiTestClient

3. The TestClient package also uses jQuery, jQuery UI and KnockoutJS packages. So we install that next.

PM> install-package jquery
PM> install-package jquery.ui.combined
PM> install-package knockoutjs

One final piece of hookup is necessary to show the Test Client in the Help Pages. Open the Api.cshtml in the path Areas/HelpPage/Views/Help.


After the last <div> ends add the following line to include the Test Client UI


In the same page, in the ‘Scripts’ section, add the following



1. Finally we install EntityFramework package to save and load data from our database.

PM> install-package EntityFramework

This completes the Project setup and we can now go ahead and build our API Controller and backend.

The data model and scaffolding the controllers

Next we setup our Model. We’ll have three classes Customer, Order and OrderDetails. A Customer may have zero or more Orders and each Order will have one or more Order Details.

public class OrderDetails
    public int Id { get; set; }
    public string Name { get; set; }
    public int Amount { get; set; }
    public decimal Price { get; set; }

public class Order
    public int Id { get; set; }
    public DateTime PurchaseDate { get; set; }
    public string BillingAddress { get; set; }
    public virtual IList<OrderDetails> OrderItems { get; set; }

public class Customer
    public int Id { get; set; }
    public string Name { get; set; }
    public virtual IList<Order> Orders { get; set; }

Before we scaffold the API Controllers, build the project.

Scaffolding the Customers Controller

To scaffold the Customers controller, we right click on the Controllers folder and select ‘Add->New Controller’ to add a new Api controller for the Customer Model class and call it CustomersController.


Making it Queryable via OData

Once the code generation complete, open the Customers Controller and on the  GetCustomers() action method, add the attribute called Queryable as follows:

public IEnumerable<Customer> GetCustomers()
    return db.Customers.AsEnumerable<Customer>();

The Queryable attribute enables OData querying. However the attribute has a bunch of parameters to enable fine grained control over data being returned. Above we have, set MaximumExpansionDepth parameter to 2. This enables us to query Customers and two levels deep from Customers, which in our case is Orders and OrderItems.

Additional parameters in the Queryable attribute are as follows:


This is all that we’ve to do enable OData querying. Let’s run the application and some data.

Adding Data using TestClient

We run our application and navigate to the /Help page. We’ll see Web API Help Page generate the following page for us


To add data, we click on ‘POST api/Customer’ link.

At the bottom of the page, we’ll see the ‘Test API’ button. Click on the button to bring up the following dialog. It actually comes up with Sample data. I’ve Modified the sample data slightly. You can see the Data in the SampleData.Json.txt file in the code repository.


In the popup, click Send to POST the data. We’ll get the following response indicating success:


This adds one Customer, three orders and three Order Items per order. Now let’s see how we can query the data.

Querying Using $select and $expand

1. The $select keyword enables us to filter out the specific number of queries we would like. So let’s do a $select=Name. This should return only the Customer Name.


As we can see, the downloaded JSON has only the Name. We could give /Customers?$select=Name,Id to get both Name and Id.

2. The $expand query allows us to specify which collections in the current Entity to load. For example, $expand=Orders will return the Customer and all their orders.


3. We can specify hierarchy of collections to load using the / separator. Example $expand=Orders/OrderItems will get the Orders and OrderItems for each order.


4. We can use both $expand and $select to retrieve selected fields in the entire hierarchy. For example the following query will get the Name, PurchaseDate and Amount from the hierarchy:
expand=Orders/OrderItems&$select=Name,Orders/PurchaseDate, Orders/OrderItems/Amount


Pretty neat! The $select and $expand OData keywords thus add a lot of flexibility for us to fine tune the data we want to retrieve.

Note: All the JSON data shown in the above screenshots have been formatted manually for better readability.


Web API 2.0 brings two new keywords $select and $expand that help us fine tune the data returned by our OData queries over Web API.

Additionally we also saw how to setup a Web API 2 project using Visual Studio 2012 and how to use the Web API help Page and the Test Client packages.

Download the entire source code of this article (Github)

About The Author

Suprotim Agarwal
Suprotim Agarwal, Developer Technologies MVP (Microsoft Most Valuable Professional) is the founder and contributor for DevCurry, DotNetCurry and SQLServerCurry. He is the Chief Editor of a Developer Magazine called DNC Magazine. He has also authored two Books - 51 Recipes using jQuery with ASP.NET Controls. and The Absolutely Awesome jQuery CookBook.

Follow him on twitter @suprotimagarwal.

No comments: