This is the second part of Building OData Service using Asp.Net Web API. The topics we’ll cover are:
- OData Introduction and Querying Existing OData Service – Part 1.
- Create read-only OData endpoint using Asp.Net Web API – Part 2 (This Post).
- CRUD Operations on OData endpoint using Asp.Net Web API – Part 3.
- Consuming OData Service using AngularJS and Breeze.js – Part 4 (Coming Soon).
Create read-only OData endpoint using Asp.Net Web API
In this tutorial I’ll be using the same solution I’ve used in the previous tutorial but I will start new Web API 2 project then we’ll implement OData service using Web API. The source code for the new OData Service project can be found on my GitHub repository.
Practical example covering how to build OData Service
To keep things simple we’ll depend on my previous tutorial eLearning API concepts to demonstrate how to build OData Service. All we want to use from the eLearning API is its data access layer and database model, in other words w’ll use only project Learning.Data. I recommend you to check how we built the Learning.Data project here as the navigation properties between database entities are important to understand the relations that will be generated between OData entities.
Once you have your database access layer project ready then you can follow along with me to create new Learning.ODataService project.
I will assume that you have Web Tools 2013.1 for VS 2012 installed on your machine or you have VS 2013, so you can use the ASP.NetWeb API 2 template directly which will add the needed assemblies for ASP.Net Web API2.
Step 1: Create new empty ASP.Net Web API 2 project
We’ll start by creating a new empty ASP.Net Web API 2 project as the image below, you can name your project “Learning.ODataService”, do not forget to choose .NET framework 4.5 version. Once The project is created we need install Entity Framework version 6 using NuGet package manager or NuGet package console, the package we’ll install is named “EntityFramework“. When the package is installed, we need to add a reference for the class library “Learning.Data” which will act as our database access layer.
Step 2: Add support for OData to Web API
By default the ASP.Net Web API doesn’t come with a support for OData, to add this we need to install NuGet package named “Microsoft.ASP.NET Web API 2.1 OData” to our Web API project. To do this open package manager console and type “Install-Package Microsoft.AspNet.WebApi.OData”.
Step 3: Configure OData Routes
The ASP.Net Web API 2 template we used has by default a class named “WebApiConfig” inside the “App_Start” folder, when you open this class you will notice that inside the “Register” method it has a “config.MapHttpAttributeRoutes()” line and “DefaultApi” route which is used to configure traditional Web API routes, the nice thing here that we can define OData end points along with traditional Web API end points in the same application.
In this tutorial we want to define only OData end points so feel free to delete the default route named “DefaultApi” as well the “config.MapHttpAttributeRoutes()” line .
Now you need to replace the code in “WebApiConfig” class with the code below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
public static class WebApiConfig { public static void Register(HttpConfiguration config) { config.Routes.MapODataRoute("elearningOData", "OData", GenerateEdmModel()); } private static IEdmModel GenerateEdmModel() { var builder = new ODataConventionModelBuilder(); builder.EntitySet<Course>("Courses"); builder.EntitySet<Enrollment>("Enrollments"); builder.EntitySet<Subject>("Subjects"); builder.EntitySet<Tutor>("Tutors"); return builder.GetEdmModel(); } } |
The code above is responsible for defining the routes for our OData service and generating an Entity Data Model (EDM) for our OData service.
The EDM is responsible to define the type system, relationships, and actions that can be expressed in the OData formats, there are two approaches to define an EDM, the first one which depends on conventions should use class ODataConventionModelBuilder, and the second one should use class ODataModelBuilder.
We’ll use the ODataConventionModelBuilder as it will depend on the navigation properties defined between your entities to generate the association sets and relationship links. This method requires less code to write. If you want more flexibility and control over the association sets then you have to use the ODataModelBuilder approach.
So we will add four different entities to the model builder, note that the string parameter “Courses” defines the entity set name and should match the controller name, so our controller name must be named “CoursesController”.
The MapODataRoute is an extension method which will be availabe after we installed OData package, it is responsible to define the routes for our OData service, the first parameter of this method is friendly name that is not visible for service clients, the second parameter is the URI prefix for the OData endpoint, so in our case the URI for the Courses resource will be as the following: http://hostname/odata/Courses. As we mentioned before you can have multiple OData endpoints in the same application, all you need to do is to call MapODataRoute with different URI prefix.
Step 4: Add first OData Controller (Read only controller)
Now we want to add a Web API Controller which will handle HTTP requests issued against the OData URI “/odata/Courses”. To do this right-click on Controllers folder->Select Add->Name the controller “CoursesController” and choose “Empty API Controller” Template.
First thing to do here is to derive our “CoursesController” from “System.Web.Http.OData.EntitySetController“, the constructor of this class accepts two parameters, the first one is the entity type mapped to this controller, and the second one is the data type of the primary key for this entity, the code for this OData controller will look as the below:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
public class CoursesController : EntitySetController<Course, int> { LearningContext ctx = new LearningContext(); [Queryable(PageSize=10)] public override IQueryable<Course> Get() { return ctx.Courses.AsQueryable(); } protected override Course GetEntityByKey(int key) { return ctx.Courses.Find(key); } } |
The EntitySetController class has number of abstract and override methods for updating or querying an entity, so you will notice that there are many methods you can override such as: Get(), GetEntityByKey(), CreateEntity(), PatchEntity(), UpdateEntity(), etc..
As I mentioned before, this controller will be a read only controller, which means that I’ll implement only read support for URI “/odata/Courses”, so by looking at the code above we’ve implemented the following:
- Overriding the Get() method and attributing it with [Queryable] attribute which allow clients to issue HTTP Get request to this endpoint where they can encode filter, order by, pagination parameter in the URI. This Queryable attribute is an action filter which is responsible to parse and validate the query sent in the URI. This attribute is useful to protect your end point service from clients who might issue a query which takes long time to execute or ask for large sets of data, so for example I’ve set the PageSize of the response to return only 10 records at a time. you can read more about the set of parameters available here.
- Overriding the GetEntityByKey(int key) method which allow clients to issue HTTP Get request to a single Course on the form: “/odata/Courses(5)”, note that the key data type is integer as it represents the primary key in Courses entity.
Step 5: Testing The Courses Controller
Now we need to test the controller, we’ll use fiddler or PostMan to compose the HTTP Get requests, the accept header for all requests will be application/json so we’ll get JSON Light response, you can check how the results are formatted if you passed application/json;odata=verbose or application/atom+xml. The scenarios we want to cover as the below:
- use $filter: We need to filter all courses where the duration of the course is greater than 4 hours
- Get Request: http://hostname/OData/Courses?$filter=Duration%20gt%204
- use $orderby, $take: We need to order by course name and take the top 5 records
- Get Request: http://hostname/OData/Courses?$orderby=Name&$top=5
- use $select: We need to get the Name and Duration fields only for all fields
- Get Request: http://hostname/OData/Courses?$select=Name,Duration
- $expand: We need to get related Course Tutor and Subject for each Course and order the Courses by Name descending
- Get Request: http://hostname/OData/Courses?$expand=CourseTutor,CourseSubject&$orderby=Name desc
Notes about the $expand query:
- The $expand allow clients to ask for related entities inline based on the navigation properties defined between the entities, as you notice the $expand accepts comma separated values so you can ask for different entities at the same time, for more information about the $expand query you can visit this link.
- By looking at the JSON response below for the $expand query we requested you will notice that the fields UserName and Password (Lines 5 and 6) for each Tutor is returned in the response which doesn’t make sense.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
{ "CourseTutor": { "Id": 5, "Email": "Iyad.Radi@gmail.com", "UserName": "IyadRadi", "Password": "MXELYDAC", "FirstName": "Iyad", "LastName": "Radi", "Gender": "Male" }, "CourseSubject": { "Id": 5, "Name": "Commerce" }, "Id": 15, "Name": "Commerce, Business Studies and Economics Teaching Methods 1", "Duration": 3, "Description": "The course will talk in depth about: Commerce, Business Studies and Economics Teaching Methods 1" } |
The nice thing here that we can fix fix this issue by ignoring those two properties from the EDM model only without changing any property on the data model, to do this open the class “WebApiConfig” and replace the code in method GenerateEdmModel() with the code below, notice how we specified the ignored properties in lines 8 and 9:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
private static IEdmModel GenerateEdmModel() { var builder = new ODataConventionModelBuilder(); builder.EntitySet<Course>("Courses"); builder.EntitySet<Enrollment>("Enrollments"); builder.EntitySet<Subject>("Subjects"); var tutorsEntitySet = builder.EntitySet<Tutor>("Tutors"); tutorsEntitySet.EntityType.Ignore(s => s.UserName); tutorsEntitySet.EntityType.Ignore(s => s.Password); return builder.GetEdmModel(); } |
In the next post we’ll see how we can implement a controller which will support full CRUD operations on the Tutors resource.
Please drop your questions and comments on the comments section below.
Good day, im having diffuclty adding service reference with Web API Odata Service, i could not add the my OdataService inside my solution to another project, i have to run the project before i can get the service.. and i have to create a separate project to get the service bec. the ODataService Project is already running. Unlike Wcf Data Services w/c i can detect(Discover), w/out running the project. thanks
Or Run Page Inspector on the Odata Project (Set the project as Start Up Project first).
OK i have to run the service independently (Ctrl+F5) then get the reference.Thanks.
Hello Pal, thanks for sharing your answer, it is strange you should be able run it in debug mode as well.
Hi Taiseer Joudeh, Excellent article for beginners, Can you also recommend any good book for beginners.
Glad that you liked it, I recommend reading “Designing Evolvable Web APIs with ASP.NET” Good luck!
Thank you Taiseer, My next project is working on oData services, when I looked at table of content for Designing Evolvable, I did not find any information related to oData.Just want to check with you before I purchase.
Sorry Sanjay, I thought you are asking about good Web API book. There is new book which has chapter about OData, I didn’t read it so I can’t tell if it is complete or not, you can check the book ASP.NET Web API 2 Recipes: A Problem-Solution Approach Good luck in your next project.
Hi Taiseer, I have successfully created OData services in WebAPI 2 using Entity Framework. Now I want to OData service which expose data from NoSQL data base. Is there any example for creating service from POCO and mapping them to database fields ?
Hi Sanjay,
I’m sorry but really can’t find/think about good resource to help you reading ata from NoSQL database. If you find any please drop comment here. Good luck 🙂
Thank you for posting this. I have read through and downloaded the source from github. I published to my local IIS 7.5 but am getting the 404 error. This is Win 7 32-bit. This is frustrating! Any ideas?
Just to follow up with some more details. I tried http://localhost/eLearning/$metadata and get the 404. I’ve checked the Handlers in IIS manager to make sure the verbs are set to *. I have also run aspnet_regiis -ir.
Hi, that is strange, I need to check it. You were able to find solution?
I was not able to get it to work. I’ve found a lot of hits out there but nothing seems to help. Some mention verb settings, others WebDav, and more tweaking of the config files. Very frustrating and strange. I will post back if I find a fix.
Thanks for this valuable post. I have following thoroughly. One thing the filter $inlinecount=allpages is not returning total count. Is there any changes needed to get this?
Hi Taiser, Very Nice article for beginners. Could you please clarify if we can develop OData service using Dapper?
Never heard about Dapper before. Need to read more about it before.
Hi Taiseer Joudeh,
Your articles are very helpful to me. I appreciate that. Could I have a question is that how can I have DbContext in service layer instead of exposing it to Controller?
Hi Tony,
You can use some repository pattern when you abstract the database implementation in it and still you can inject this repository in controllers.