The SIF is a great framework for building SData providers, see my
previous post on the SIF for more about it.
In this post I'm going to provide complete instructions on how to generate a RESTful web service
starting with the Northwind sample database. The web service will be able to handle SData or OData URLs/Queries.
With Visual Studio (VS) it's easy to build a RESTful web service starting Model & Database and adding support for SData URLs is easy too.
I'm going to do this with Visual Studio 11 Beta, Entity Framework (EF) 5 and EF 5.x DbContext Generator for C#. I'll generate my Model from SQL Server and the Northwind sample database.
Video Tutorial
I provide instructions below the video if you prefer. The Video will show:
- Creating the MVC .NET 4 Web API project from the Northwind database
- Routes for handling SData Single and Collection URLs
- Referencing the SDataTransformHandler which handles complex SData queries
Instructions for
generating a RESTful web service from the Northwind Demo database.
Follow my instruction or this Microsoft tutorial:
EF 4.2 Model & Database First Walkthrough
Create Web API project in VS
1. File
New Project
2. APS.NET
MVC 4Web Application (Wizard)
3. Web
API (Wizard)
Build Solution!
You should now be able to run default wizard generated Web
API project and make a REST request like:
To see a JSON content type returned add the ACCEPT header
and JSON type: E.G, Add Header:
Accept application/json
Add database connection
1. Open
Server Explorer View
2. Right
click Data Connections -> Select “Add Connection” wizard
3. Select
Server Name and then select database name
4. Test
Connection
Add model from database
1. Right
Click “Models”, select: Add -> New Item
2. Select
“ADO.NET Entity Data Model from the list
3. In
Entity Data Model Wizard select “Generate
from database”
4. Select
Connection (E.G. onm512295.Northwind.dbo)
5. Click
Next button and then select all tables from the “Entity Data Model Wizard”
6. Finish
Build Solution!
Generate the code from the model
If using VS 11 then install the EF 5.x DbContext
Generator for C#:
1. Right
click on the model_name.edmx diagram/editor and click “Add Code Generation
Item…”
2. Select
“Code” from the left hand list
3. Select
“EF 5.x DbContext Generator” and click Add
4. Click
ok if you get a warning
There will now be a node called [some name].tt within Models in
Solution Explorer. Within [some name].tt there will be all of the Entities. E.g.
Customer, Order, etc.
There should also be a [some name].Context.tt node under
the Models node in Solution Explorer. This is required later (see below).
Important warning: If the keyword “virtual” appears in the Entities
(E.G. in Order.cs, etc.) then delete it. If the virtual keyword exists on an entities member then the entity will
not be serialized; an error like the following will appear at runtime when you eventually
execute a GET:
Exception Details: System.Xml.XmlException: You must
write an attribute 'type'='object' after writing the attribute with local name
'__type'.
Build Solution!
Generate Controller for each the Entity
- Right
Click “Controllers” folder and “Add Controller”
- The Add Controller Wizard will popup enter the following:
- Specify
Controller name that matches target Entity, E.G: "CustomerController" for the Customer model class
- Template:
drop box select “API controller with empty read/write actions".
- Model
class: Select the Model class, e.g. "Customer"
- Data
context class: Select the DbContext created in step 3 of Generate the code from the model instructions above. E.G: "NorthwindEntities"
Add logic to the controller class to read
data
public class
CustomerController : ApiController
{
NorthwindEntities db = new NorthwindEntities();
// GET
/api/customer
public
IQueryable<Customer> Get()
{
IQueryable<Customer> retVal = db.Customers.AsQueryable();
if
(retVal == null)
throw
new HttpResponseException(HttpStatusCode.NotFound);
return
retVal;
}
// GET
/api/customer/5
public
Customer Get(string selector)
{
Customer retValue = db.Customers.Single(o => o.CustomerID == selector);
if
(retValue == null)
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
return
retValue;
}
}
Now run the project, open a web browser and request
customer resource, e.g:
If you hit the following error:
"Exception Details: System.Xml.XmlException: You
must write an attribute 'type'='object' after writing the attribute with local
name '__type'."
It’s because you did not remove the “virtual” keyword
from the generated entities classes as indicated above. This is a bug in MS's
framework.
At this point you should have a RESTful web service that can handle simple HTTP GETs with SData Single or Collections URLs. To add support for complex SData URLs/Queries follow the instructions I provided in my post:
SData URLs in MVC .NET Web API
