Getting started with OData and ASP.NET Web API

To paraphrase from odata.org – “The Open Data Protocol (OData) is a Web protocol for querying and updating data. OData does this by applying and building upon Web technologies such as HTTP, Atom Publishing Protocol (AtomPub) and JSON to provide access to information from a variety of applications, services, and stores.”

In plain-speak, OData is a protocol that works over HTTP (the same protocol on which the entire Internet, as we know it, works) and uses pre-established data exchange formats like ATOM (or JSON) to expose data in the Server.

To expose the data, server needs to implement an OData endpoint, which then enables any client application, capable of making HTTP requests, to query this data. That’s of course an over-simplistic scenario as data in server is usually protected and you need to have proper Authorization to access that data. However, the moot point is having an OData endpoint to serve up data locked away in data silos, opens up the data for seamless integration with existing OData clients.

What has ASP.NET Web API got to do with OData?

As we saw above, OData is a protocol, and a protocol needs end points to be useful. ASP.NET Web API is becoming Microsoft’s Go-to mechanism for building modern web-services that work over HTTP. Hence it’s natural that OData endpoints on the Server side in Microsoft Stack be built over Web API. That’s precisely the relationship between OData and Web API. The OData implementation on ASP.NET builds on top of Web API.

Show me the Code!

Had enough of theory and want to see it all in action? Let’s fasten our seatbelts and put our proverbial fingers where the code is!

If you want to continue with theory of OData this MSDN Post is a good start.

Building a simple OData Endpoint using ASP.NET Web API

Let’s fire up Visual Studio and create a new ASP.NET MVC Project to use the Web API Template .
Prerequisites: You need Visual Studio Web Tools Update 2012.2 or install the latest package (Microsoft.AspNet.WebApi.OData) from Nuget.

1. Once the project is setup, let’s add a class to the Model folder called Blog

public class Blog
{
public int Id { get; set; }
public string Title { get; set; }
public string Post { get; set; }
public int Views { get; set; }
}

The Id property is the key and clients can query using it. In the DB, it will be the PK column

2. Select the default ApiController that was added by default and rename it from ValuesController to BlogController.

3. The OData controller is an implementation of ApiController, so our class BlogsController should inherit from ODataController. However we will go one step up and make it a strongly typed controller and inherit it from EntitySetController<T,K>.

public class BlogsController : EntitySetController<Blog, int>
  
4. It’s a good idea to pause and make 100% clear as to where we got the EntitySetController<TEntiy,Key> from. The following inheritance diagram should help. As we see, the EntitySetController derives from the ODataController.



Note: The EntitySetController is in the System.Web.Http.OData namespace.

5. We could use a Database in the backend but for now we’ll use an in-memory store and create a Generic list of blogs (List<Blog>).

6. We update the ProductsController as follows

public class BlogsController : EntitySetController<Blog, int>
{
List<Blog> _blogs = new List<Blog>()
{
  new Blog() { Id = 1, Title = "OData 1", Post =
    "Lorem ipsum dolor sit amet", Views = 100, Author="Suprotim" },
  new Blog() { Id = 2, Title = "OData 2", Post =
       "Quisque tristique imperdiet justo", Views = 203, Author="LazyBlogger" },
  new Blog() { Id = 3, Title = "OData 3", Post =
   "Nam luctus metus sed dolor pretium", Views = 33, Author="LazyBlogger" },
  new Blog() { Id = 4, Title = "OData 4", Post =
   "Phasellus ac elit urna, non pulvinar tortor", Views = 955, Author="LazyBlogger"},
  new Blog() { Id = 5, Title = "OData 5", Post =
   "Curabitur rhocus erat odio, ut venenatis tortor",Views = 300, Author="Suprotim"},
};
public override IQueryable<Blog> Get()
{
  return _blogs.AsQueryable();
}
public override Blog GetEntityByKey(int id)
{
  Blog entity = _blogs.Find(bl => bl.Id == id);
  return entity;
}
}
That pretty much covers the EndPoint with respect to retrieving data. Now that we are done ‘building’ the endpoint, let’s go and configure it.

Configuring the OData Endpoint

Configuring an end-point requires us to update the App_Start\WebApiConfig.cs file.
First up we instantiate the ODataConventionModelBuilder and tell it that the Blog entity will be referred to as Blogs. This is much like the EF Model Builder.

ODataModelBuilder modelBuilder = new ODataConventionModelBuilder();
modelBuilder.EntitySet<Blog>("Blogs");
Next we update the Route by passing a name, the url identifier and an instance of IEdmModel implementation retrieved from the modelBuilder.

Microsoft.Data.Edm.IEdmModel model = modelBuilder.GetEdmModel();
config.Routes.MapODataRoute("ODataRoute", "odata", model);
  
Finally we enable Queryable support by uncommenting the following

lineconfig.EnableQuerySupport();

The Final code of Register method in App_Start\WebApiConfig is as follows:

public static void Register(HttpConfiguration config)
{
config.Routes.MapHttpRoute(
  name: "DefaultApi",
  routeTemplate: "api/{controller}/{id}",
  defaults: new { id = RouteParameter.Optional }
);
// Uncomment the following line of code to
// enable query support for actions with an IQueryable
// or IQueryable<T> return type.
// To avoid processing unexpected or malicious queries,
// use the validation settings on QueryableAttribute to validate incoming queries.
// For more information, visit http://go.microsoft.com/fwlink/?LinkId=279712.
config.EnableQuerySupport();
ODataModelBuilder modelBuilder = new ODataConventionModelBuilder();
modelBuilder.EntitySet<Blog>("Blogs");
Microsoft.Data.Edm.IEdmModel model = modelBuilder.GetEdmModel();
config.Routes.MapODataRoute("ODataRoute", "odata", model);
 
// To disable tracing in your application, please comment out or
// remove the following line of code
// For more information, refer to: http://www.asp.net/web-api
config.EnableSystemDiagnosticsTracing();
}

Retrieving JSON from the OData feed

Our OData feed is ready and configured so let’s run the application.
From the Home page add /odata/ to the URL and hit enter. The browser will ask if it can download Blogs.json file. Download and open the file in Notepad. You’ll see the JSON returned as follows and that it has all the posts that we had added in Step 6 above



If you have seen simple JSON serialization by Web API, you will recognize the difference here. Here, the entire collection of returned values is wrapped in an object. This object has two attributes - ‘odata.metadata’, which is self-describing as in where the data came from (the full odata URL that is included). Second attribute is ‘value’ that has the array of JSON serialized Blog objects that were returned.

Now that we’ve got the JSON, we can build any client that we want. On the browser we could use plain JavaScript to build a UI. However, the premise of OData is that it will work with existing clients without any changes on the client side. Let’s test this out. Let’s see if we can use the data in Excel.

Accessing OData endpoint from Excel

1.    I am using Office 2013 here, I open Excel and navigate to the Data Tab



2.    Next from the ‘From Other Sources’ pull down, I select ‘From OData Data Feed’ option.

3.    This brings up the Data Connection Wizard, where we provide the URL of our OData endpoint and click Next



4. Excel goes and pulls up all the tables it can find at the Endpoint. In our case it’s only 1. We select the Blog table and click Next.



5. Excel asks where we can save the query file and a friendly name for the Service.



6. When we click Finish, Excel ask us what to do with the data, as in move to an Excel Table, or create a Pivot Chart out of it etc. Let’s select Pivot table



7. At this point, Excel will have our data with itself. When we build a Pivot table for Authors vs. Summation of Views per author as well as number of articles written. The Pivot chart looks as follows.



As we can see Excel was able to use the OData endpoint we built, to retrieve data, without knowing anything else beyond the URL of the endpoint.

Conclusion

Today we took our first step towards implementing OData end points in ASP.NET Web API. OData is pretty involved and in future we will see more of its capabilities regarding querying and data updating!

Download the entire source code of this article (Github)