SData with Entity Framework (EF) Model & Database First

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:

http://localhost:49068/api/values/1

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#:

http://visualstudiogallery.msdn.microsoft.com/da740968-02f9-42a9-9ee4-1a9a06d896a2

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

1. Right Click “Controllers” folder and “Add Controller”
2. The Add Controller Wizard will popup enter the following: 
3. Specify Controller name that matches target Entity, E.G: "CustomerController" for the Customer model class
4. Template: drop box select “API controller with empty read/write actions".
5. Model class: Select the Model class, e.g.  "Customer"
6. 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:

http://localhost:1489/sdata/contract/customer(ALFKI)

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