It has been a while since I’ve made a post. Real life needed some attention as well and I had to invest into some study into Entity Framework, which is one of the topics I have to master for the upcoming ADO.NET certification. During preparation I found some information on how to construct WCF REST services with ODATA exposing data directly from an entity framework datasource.
For some this might not be interesting, but I sure as hell find this an interesting topic, considering how interoperable and scalable REST services are. In this post I’ll run through the setup and configuration of a Restfull WCF Data Service with Entity Framework.
I’ve written 2 post before on WCF REST Services, which could be interesting to browse through quickly to understand the basics:
WCF REST service with XML / JSON response format according to Content-Type header
WCF REST service operation with JSON and XML also supporting ATOM syndication feed format
If you are looking for building REST services, this can also be done by the ASP.NET Web API:
Building and consuming REST services with ASP.NET Web API and OData support
Quoted directly from the Open Data Protocol website:
The Open Data Protocol (OData) is a Web protocol for querying and updating data that provides a way to unlock your data and free it from silos that exist in applications today. 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. The protocol emerged from experiences implementing AtomPub clients and servers in a variety of products over the past several years. OData is being used to expose and access information from a variety of sources including, but not limited to, relational databases, file systems, content management systems and traditional Web sites.
OData is consistent with the way the Web works – it makes a deep commitment to URIs for resource identification and commits to an HTTP-based, uniform interface for interacting with those resources (just like the Web). This commitment to core Web principles allows OData to enable a new level of data integration and interoperability across a broad range of clients, servers, services, and tools.
For this post I used the AdventureWorks database that is provided by Microsoft. You can find it here:
After attaching the database to your local IIS SQL server, you should have something as following:
You will have a database with a load of tables and ready-to-use data present. We will be building a WCF Data Service on top of this database. My solution looks as following:
I have a WCF Service application called “EntityFramework.DataService” and I have a client console application called “EntityFramework.DataService.Client“.
In our WCF Service application will we add an “ADO.NET Entity Data Model” that will be linked to our Adventureworks database.
For demo purposes we will only add the Product and ProductReview to our Entity Data Model instead of adding all the tables from the AdventureWorks database. After setting up your model, it should look as following:
Make sure your project has the references to System.Data.Entity and System.Data.Services. The System.Data.Entity is the namespace that holds the plumbing for the Entity Framework. The System.Data.Services is the namespace that holds the plumbing for the OData support for your WCF Data Service.
We will add a “WCF Data Service” to our project, which is premade template of a WCF REST service supporting the OData protocol. We do not need to write all the plumbing for our service to support the OData protocol.
We will call our WCF Data Service “Products”. It should look like this after being created:
Note the part I marked. The Products service derives from the DataService<DataSourceEntities>, of which we need to define which data source entities it is working on. Note that DataService belongs to the System.Data.Services namespace.
When we added our ADO.NET Entity Data Model, we had to define a name for our Entities used for the model. In this case I took AdventureWorksEntities as name, which you also can find in the web.config file:
So our Data Service Products will have to derive from DataService<AdventureWorksEntities>
In the comments you will also see that you need to set a rule to indicate what the set rights are. For the time being, we will only allow READ access to our “Products” set.
For each entity set you want to expose data, you need to set the SetEntitySetAccessRule for that EntitySet. You can also add a rule with the “*”, though that exposes all the data in your data model, so that might be a bit unsafe.
Our WCF Data service looks as following:
Our SetName of the Product entity is called Products, which you can also find in your ADO.NET model when you click the Product entity:
So we defined an OData REST WCF Service that sets the access rule to the entity set Products to AllRead, exposing our Product information from our Adventureworks database to externals.
I am hosting the WCF REST Service in my local IIS:
The identity of my applicationpool my WCF Service application is running on on IIS is the NETWORK SERVICE. Make sure the NETWORK SERVICE also has sufficient rights to the AdventureWorks database, or your WCF Service will not be able to access the database:
When visiting our Products service in our browser:
It returns an AtomPub format response, on which a collection of the name “Products” is visible. When surfing to this Products location: (click to enlarge)
As you can notice, we are getting Product entries back and as you can notice at the scrollbar, we are getting a HUGE list of products back. Actually we are getting the entire list of products back that is present in our adventureworks database. Now if you are using the OData REST service for internal use and you are sure people requesting product information will use the correct filters and not request all products even though only needing some, this isn’t a big issue. However if you are exposing the product information to externals, you might want to restrict the amount of products one can request in a single request. This is what we call paging.
In your service you can set the amount of entities that can be retrieved in a single request like this:
We set the SetEntitySetPageSize for the Products entity set to 5. This means that a products request can only retrieve maximum 5 products. If they want the next 5 products, they will need to work with paging and get the 5 next resulsts. When rebuilding your WCF service application and requesting the Products by browser, you will only get 5 products instead of the entire list:
As you can see on the scrollbar, the amount of results is a lot less and is restricted to 5, as what we defined in our DataServiceConfiguration object. This object only gets initialized only once when the service is being loaded and is being cached onward.
I will not go to deep into the OData protocol, but the Odata protocol lets you filter, order and select data by certain URI commands, just like you would be able to do by LINQ queries. The most common Odata query operations can be constructed with $filter, $select and $orderby in the URL request. Odata protocol defines how to retrieve a specific entity, how to retrieve a specific entity value and so forth by url construction.
For example, instead of requesting a list of products, I want to request the Product with ID 1:
We request by the Products(1) url and we will only retrieve 1 product instead of all products:
There are a lot of options to filter and query information by the Odata Protocol. I will not go into detail, since that would be out of scope, but some examples to get you started:
Selecting how many items you want with the $top operator and skipping a few items with the $skip operator:
This will get the Products on location 6 and 7 as we define to skip the first 5 products and only take the top 2, so we will get product on location 6 and 7. If the $top operation is larger then the entity set page size, the maximum results returned will be the entity set page size obviously.
Ordering the products by the ListPrice descending:
Filtering the results by only getting the products where the ListPrice is greater (gt) then 3000 and only selecting the name instead of retrieving all properties:
Only retrieving the Name property value of Product with ID 1:
Of course there are a lot of possibilites with the OData protocol, but it should be clear to you this is quite powerful. It lets you query data and make transformations and projections on the requested data, without you having the write any plumbing at all to support this functionality. Imagine if you had to write a REST Service that exposes a lot of data and that supports querying, filtering, selectioning by the client and that you would have to write all the support of url parameters to provide this sort of functionality. Now you only wrote 3 rules of code and your clients can read your Products data and query it as needed.
2. Querying the WCF OData service by a client DataServiceContext
Quering the data by browser is possible, but this is not the option you would use when creating a client that consumes the service. You can work with a webClient which invokes requests to the service and gets responses as result, which you can construct back to entities. But you can also generate a client library that consumes the OData service and constructs the urls according to the OData protocol while you can create your queries with the familiar LINQ.
Just as you can create a client with the Add Service Reference for the common WCF service, so you can add a client to work with the OData WCF Service. By default, the OData WCF service exposes it’s metadata by the Products.svc/$metadata url:
Since Visual Studio is smart enough when adding a Service Reference to an OData WCF Service, it recognizes the WCF service as an OData service. Instead of using the SvcUtil commandline to generate the client proxy, it will use the DataSvcUtil commandline to generate the client proxy. Yes, this means the client proxy is constructed differently for a client consuming an OData service compared to a common WCF service.
For demo purposes we will use the Add Service Reference in visual studio. In the background it uses the DataSvcUtil commandline, so you have the option which one to use.
You can add a service reference at the client console application like this:
If you show all files in your client console application, you will see the following:
If you drill a bit into the generated client proxy code, you will find the following:
An AdventureWorksEntities gets created, which derives from the DataServiceContext which belongs to the System.Data.Services.Client namespace. Basically a client objectContext is being created which we will be using as a proxy. We will execute commands against the DataServiceContext with LINQ queries, which will construct the LINQ queries into URL requests adapted to the OData protocol, extracting having to write the low-level Odata urls to retrieve data.
To work with the DataServiceContext, you will need to know the basics about Entity Framework, so you know how to work with the ObjectContext. Knowing how to retrieve information, update or create information and knowing what lazy loading is. Entity framework basics are out of the scope of this post. If you want to learn entity framework, I would suggest the Programming Entity Framework book by Julie Lerman: http://shop.oreilly.com/product/9780596520298.do
Now let’s query the product with productID 1 from our client console application:
As you can notice, we create an AdventureWorksEntities which is defined in the localhost namespace, which I defined at the add service reference. So we are creating a new instance of the DataServiceContext at the client side. You need to pass the root url of the OData service as an uri to the entities object. Next on we use a simple LINQ query to retrieve the name of the product with productID 1. When executing the client console application:
As you can see we get the name of the product back with productID 1. So at our client console application we wrote a LINQ query, which at execution gets converted to an url request with the OData protocol, which our OData service understands.
Now let’s retrieve some list of products. I want to retrieve the list of products where the ListPrice is larger then 3000. If we look at our Products in the database for products with a ListPrice larger then 3000.
We get 13 results back if we query this in the database:
Let’s get these products by a LINQ Query from our client console application:
When executing the client console application:
Sadly enough we only get 5 results back instead of 13. If you think about it, it makes sense as we set the max pagesize for our Products entity set at 5 at our DataServiceConfiguration.
This means we will need to request this list of products by paging. If the first request returns and there are still products not returned, we will execute another request to get the next 5 products untill we retrieved all products.
This sounds a bit messy, but luckily the plumbing has already been written for us. For the people used to Entity Framework, this will be nothing new:
To work with paging, you will need to work with a QueryContinuation. The first 5 products we retrieve with the response will have a QueryContinuation for the next 5 products (basically the next page). We can use this QueryContinuation on our context.Execute to get the next set of results. We will use a Do While loop to keep getting the next page of results as long the QueryContinuation is not null. When the continuation is null, it means there are no more results after those we just requested.
One important thing to notice is that we no longer work with a LINQ Query, but we work with the context.Products.Execute method. We need to use the Execute() method to be able to work with the QueryOperationResponse and the DataServiceQueryContinuation to work with the paging and to retrieve all results. In our LINQ query we constructed a where and an orderby, so we will need to add this where and orderby clause to our Execute() function. You can do this with the AddQueryOption() method at our Entity Sets before we invoke the Execute() method. The AddQueryOption allows you to add an ODATA operation and define the value of that operation. We add a $filter query option, which allows us to filter for the products with ListPrice greater then 3000. We also add a query option for $orderby so we can order the products by the listprice descending.
It’s a bit of a hassle to work with paging with an OData WCF service as you can not use the common LINQ query we use. For paging you need to work with the context.EntitySet.Execute() or the context.Execute<Product>() methods. The operations you normally set in your LINQ query you need to add by the AddQueryOption method and add OData operations and values according the OData protocol.
When executing our client console application now:
We get all the results back for the products with a ListPrice greater then 3000, ordered by the listprice descending.
In theory you could also do the paging with LINQ like this:
We just keep taking the products and skipping everytime 5 products more, until we retrieved all products. It will output the same results as the code with the QueryContinuation. However I’m pretty sure doing it like this will have some performance implications, but that’s up to you.
Adding, updating or deleting a new product are just common Entity Framework operations. Adding a product goes like this:
We create a new Product, Add it to the Context and trigger the SaveChanges() method of the context which will save all changes made to the context that have not been saved. Notice I catch a DataServiceRequestException in case the SaveChanges() throws an Exception. When executing the client console application:
We get a forbidden error when adding a new product, which is quite normal, as we set the entity set access rule to AllRead at our Service. Let’s change that:
Note we set the EntitySet access rules for the products set to AllRead or WriteAppend. This means you can read and you can add new products. You can not update or delete current products.
When executing the client console application:
The new product we created is added in the database. We simply create a product, add it to the context and call the SaveChanges. The context handles the HTTP POST request with the product information, retrieves the new ID of the inserted product and adds this ID to our product in our context.
Deleting a product is as simple as this:
Make sure you set the rights on your REST WCF OData service so that delete is allowed or it will fail.
One thing you might also notice with your IIS hosted service, is that the UPDATE and DELETE methods to the WCF service will fail:
Error: The remote server returned an error: (405) Method Not Allowed.
In case you have this issue, you will have to go to IIS, go to your EntityFramework.DataService application and go to the Modules:
Remove the “WebDavModule“ from the list of modules and the UPDATE/DELETE/MERGE request operations will succeed.
3. Adding and invoking a custom method to your WCF Data Service
It is also possible to add custom methods to your WCF Data Service and invoke those from the client.
Our WCF Data Service code looks as following:
Note we added a simple operation GetResult, which is marked by the WebGet attribute and the SingleResult attribute, to register it as a single result method. We also added an SetServiceOperationAccessRule in our DataServiceConfiguration for the GetResult method we added. If you do not set an access rule for this operation, you will not be able to invoke it.
You can also invoke this custom operation by the browser to test it:
If you check the service metadata by $metdata you will find this custom operation in there:
Even though the custom function is in the metadata, if you update your service reference, the custom operation will not appear on your objectContext. I don’t know if this is a bug or whether this is intended, but you can invoke the operation at the client as following:
Executing the client console application:
4. Query and change interceptors on Entity Set operations
To finalize, one feature that might be needed when creating your WCF Data Service. Let’s say a client is adding a new product and you want to intercept this product, to first validate it against some of your rules to check whether some of the properties are also valid. To do this with the WCF Data Service, there is a thing called QueryInterceptor (for GET operations) and a ChangeInterceptor (for UPDATE/PUT/MERGE/DELETE operations).
Now lets say our policy only allows people to add Products with a ListPrice between 0 and 5000.
We add following ChangeInterceptor to our Data Service which will intercept all PUT/DELETE/MERGE/UPDATE requests to our WCF Data Service for the Products Entity Set:
If the ListPrice isn’t between 0 and 5000, we will throw an DataServiceException notifying the client the price of the product does not match our policy.
We change our client console application to add a Product with a ListPrice of 5005:
When executing our client console application:
A QueryInterceptor is triggered on the GET Requests on an EntitySet, which you can use to check authorization, or to filter out certain requests you don’t want people to see.
In our AdventureWorks Products table we have 504 products. However 200 of these products are with a ListPrice of 0. Ofcourse our company does not want to expose the products of which the ListPrice is 0, which currently is happening. To solve this, we for one solution, could add a QueryInterceptor on the Products entity set.
Our client console application will get the Count of all Products:
As expected, we get 504 results, though only 304 should be returned. To filter out those with a ListPrice of 0 we can add the following QueryInterceptor:
We put a QueryInterceptor for the Products entity set. The operation should return an Expression<Func<Entity,bool>>. In the operation we return a delegate (=func), which checks whether the ListPrice of the product is larger then 0. If it is, this returns true and the product can be returned to the client. If it is not larger then 0, the lambda will return false and the item will not be returned.
When executing the client console application:
All the products with a ListPrice of 0 are filtered out of the results on any GET query.
Any suggestions, remarks or improvements are always welcome.
If you found this information useful, make sure to support me by leaving a comment.
Cheers and have fun,