Developing OData Services Using Entity Framework

This topic has been due for quite long time from my end. Things kept me busy and couldn’t get bandwidth to write about this subject. All right, let’s get dive into the topic.

WCF Data Services supports two providers out-of-the-box: Entity Framework and Reflection providers. In my earlier post I discussed the latter: OData feeds using POCO entities. In a nutshell, every feed that your service exposes should be implemented as a property of type IQueryable for data retrieval. If you want to support insert, update and delete operations, then you should implement IUpdateable interface as well. It is required to implement both for OData. In this post, I will show how you can use Entity Framework Data Service provider of WCF to publish OData services. Believe me – it is extremely simple compared to the Reflection Data Services provider of WCF.

Due to its simplicity and lightweight-ness, I am going to use the familiar Northwind database for this post. The first step is to create the entity data model (EDM) for your data source -the Northwind database. I will quickly run through the steps to do this (assuming you have a running Northwind database running on an SQL Server instance):

  1. Open Visual Studio and create a Class Library type project
  2. Add a new ADO.NET Entity Data Model (under Data Templates)
  3. On the subsequent dialog, select Generate from database and follow the wizard steps by selecting the Northwind database and all the table objects (Views and Stored Procedures not required for now).

Now, you would see an .edmx file created and added to your class library project. You will also see an app.config file added to the project with a connection string entry looking like this (in a single line):

<connectionStrings>
<add name="NWEntities" connectionString="metadata=res://*/Northwind.csdl|res://*/Northwind.ssdl|res://*/Northwind.msl;
provider=System.Data.SqlClient;provider connection string=&quot;Data Source=(local);Initial Catalog=Northwind;
Integrated Security=True; &quot;" providerName="System.Data.EntityClient" />
</connectionStrings>


The designer-generated class for the .edmx file will have the entity and association classes defined for each table you selected during the EDM creation wizard process. But the most important generated-class is the entity container class derived from ObjectContext class looking like the highlighted one below:



image



The second step is to create a class that wraps and exposes the entity model as OData feeds (entity sets) and a WCF service host for the feeds. Let’s create a class (name as you like) but derive it from DataService<T> generic type. This is the same as what I did in my earlier post demonstrating the Reflection provider.



image



NWEntities is the name of my Entity Framework container class created during the first step (creating the EDM for Northwind). The data service initialization method must be exactly as shown above (including the method name). Yes, this method could have been defined as a virtual method in DataService class that could be overridden in the derived class with custom access rules but I do not know why Microsoft chose to let the developers define this method in the way shown above with some hard-wiring somewhere (I would be glad if you know why and leave a comment). It can completely be an empty method in which case the WCF will configure the service class with default values, but if you want to control access to various entities in the EDM then this is the only place you can do it and you cannot change it later unless you restart the service and recreate the app domain.



Now the hosting part:



  1. Add a Console type application to the default solution
  2. Copy the EDM connection string created (shown above) to the console application’s configuration file
  3. Assuming you have the following few lines of code (you have to add project reference to the class library with the EDM file and the data service wrapper class):


image



Running the host application should throw up the following window:



image



Now the OData service is ready for consumption. Open up Internet Explorer (or any application that can show XML response from HTTP requests, such as Fiddler) and navigate to the service address shown in the console window. The HTTP response would be an XML showing the list of feeds (entity sets) available at the service URL:



image



Depending on the tables and other objects selected during the EDM creation process and permissions set in the InitializeService method, you may see less or more number of feeds. Nevertheless, now you make all the OData data retrieval requests (HTTP GET) and for a sample refer to my earlier post. But what about entity creation, update and delete operations? We use HTTP verbs POST, PUT and DELETE for create, update and delete operations respectively. The body of the HTTP request will have the new values for the entity to be created, edited and deleted. For example, to a new category called Milk Products, you make a HTTP POST request as below (I used Fiddler to create all the HTTP requests shown from here on):





image



Note that I am not passing certain Category properties namely, category ID and Picture. You can omit any null-able (Picture) and identity type columns (Category ID). The Content-Type header specifies the payload format (in this case it is Atom XML, but it can be JSON too). On successful creation, the service will return an Atom XML representing the newly created entity with new identity fields (if any) inserted in:



image



You can add child entities directly to a parent. The following HTTP POST creates a new product for the newly created category ID 10:



image





Again, category ID and product ID are not passed because, the POST URI itself specifies the category ID implicitly and product ID is an identity column. The response on successful product creation looks like this:



image



Another scenario: what if you would like to add a parent and children in one shot? Let’s add a category and a couple of products to it in the same HTTP request. Please note how the parent (XML in dark red color) associated child products (XML in purple color) are specified inline in the feed itself:



image



image



The HTTP response for this request will have only the newly created parent Atom XML but at the backend the WCF Data Services would create the children and attach them with the newly created parent (fire up a SQL query and check for yourself).



Let’s see how you can update an entity, here an existing category with ID 13:



image



As per OData specification, PUT operation against an entity completely replaces the existing one in the data source; the direct effect is that if you miss a property in the request, it will be set to its default value or empty/null as the case may be. In order to avoid this situation, OData recommends HTTP MERGE operation, which merges new values with the existing one without overwriting.







image



The above operation updates only the Description property leaving the category name and picture as is. Had it been a PUT request, the latter ones would be set to null/empty and if the columns backing those properties were non-null able then the PUT operation would fail returning HTTP status code 500. It is also possible to target a PUT request against a specific entity property to update it directly. The following request updates the Decryption property of the Category with ID 13:



image



Note the content type is flipped back to application/xml. Finally the deletion, which is pretty straight-forward:





image



The above request deletes the category element with ID 13 from the data source. Obviously, the delete would fail if the target entity has associated children.



The important thing to note here is that all the CRUD operations performed are routed through the EDM created for the data source by the WCF Data Service infrastructure and hence you can perform any custom operations via stored procedure mapping for CUD requests (POST, PUT, MERGE and DELETE).



All right, I have shown you all the examples using raw XML markup and HTTP requests/responses but do you really have to deal with such raw format? The answer is No, unless you really like playing with XML directly and making HTTP requests yourself. OData has a client SDK for various platforms such as JavaScript, Objective-C, PHP and Java. As far as .NET is concerned, Visual Studio automatically creates all the necessary proxy entity classes to interact with the OData service and perform all supported CRUD operations.



Here is a simple client code that retrieves all products belonging to category ID 5 (assuming you have made a service reference to the WCF Data Service):



OData Entity Framework Client



If everything goes fine, running the above code should output:



image





I seriously hope this post helped you realize the power of OData services and its applicability in the enterprise and B2B scenarios and especially how super-duper easy it is to implement one with WCF and Entity Framework. In a future post I will discuss another little cool OData feature that I have not opened up my mouth yet! :-)