Steps to create a CRUD oData service with ASP.Net Web API

Quite a lot of ASP.Net Web API shows how to create a read-only oData service, which is not very useful in real project, the following steps can be used to create a full CRUD webapi odata service that can run on iis. The project is inspired by pluralsight's ASP.Net oData training project as well as other online help information.

Tools:
VS 2012 on Windows 7

1. Create a new MVC 4 project
2. Add Entity Framework and import Northwind database, use Windows integrated mode for database connection
3. Add WebApi oData package using NeGet, the ApiController does not work with circular json serialize, so it has to use EntitySetController to create controller class.
4. update WebApiConfig.cs as follows
        public static void Register(HttpConfiguration config)
        {
            config.Routes.MapODataRoute("Northwind", "odata", GetImplicitEDM());

            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
           ....
        }

        private static IEdmModel GetImplicitEDM()
        {
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            var customers = builder.EntitySet<Customer>("Customers");
            builder.EntitySet<Order>("Orders");
            builder.EntitySet<Shipper>("Shippers");
            return builder.GetEdmModel();
        }
    }

5. create a new CustomersController class, the name needs matching the EntitySet name.
public class CustomersController : EntitySetController<Customer, string>
{
   NORTHWNDEntities _context = new NORTHWNDEntities();

        [Queryable]
        public override IQueryable<Customer> Get()
        {
            return _context.Customers;
        }

        protected override Customer GetEntityByKey(string key)
        {
            return _context.Customers.FirstOrDefault(c => c.CustomerID == key);
        }

        [Queryable]
        public IQueryable<Order> GetOrdersFromCustomer([FromODataUri] string key)
        {
            return _context.Orders.Where(o => o.CustomerID == key);
        }

        protected override string GetKey(Customer entity)
        {
            return entity.CustomerID;
        }

        //sample request form fiddler
        //header
        //Content-Type: application/json
        //payload
        //{"CustomerID":"ABCDE","CompanyName":"ABCDE","ContactName":"ABCDE","ContactTitle":"ABCDE"}
        protected override Customer CreateEntity(Customer entity)
        {
            _context.Customers.Add(entity);
            _context.SaveChanges();
            return entity;
        }

        //sample request from fiddler, only specified fields from client will be set to the new objects, all other
        //fields will be set to null
        //url
        // put for http://localhost:58317/odata/Customers('ABCDE')
        //header
        //Content-Type: application/json
        //payload, customerid field will be ignored
        //{"CustomerID":"AAAA","CompanyName":"AAAA"}
        protected override Customer UpdateEntity(string key, Customer update)
        {
            if (!_context.Customers.Any(c => c.CustomerID == key))
            {
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                      HttpStatusCode.NotFound,
                      new ODataError
                      {
                         ErrorCode = "EntityNotFound",
                         Message = "Customer key " + key + " not found"
                      }));
            }

            update.CustomerID = key;
            _context.Customers.Attach(update);
            _context.Entry(update).State = System.Data.EntityState.Modified;
            _context.SaveChanges();
            return update;
        }

        //Similar to post but keeping the existing fields in object if client side does not specify them
        protected override Customer PatchEntity(string key, Delta<Customer> patch)
        {
         
            var customer = _context.Customers.FirstOrDefault(c => c.CustomerID == key);

            if (customer == null)
            {
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                      HttpStatusCode.NotFound,
                      new ODataError
                      {
                          ErrorCode = "EntityNotFound",
                          Message = "Customer key " + key + " not found"
                      }));
            }

            patch.Patch(customer);
            _context.SaveChanges();
            return customer;
        }

        //sample request from fiddler, only specified fields from client will be set to the new objects, all other
        //fields will be set to null
        //url
        //delete for http://localhost:58317/odata/Customers('ABCDE')
        //header
        //Content-Type: application/json
        public override void Delete([FromODataUri] string key)
        {

            var customer = _context.Customers.FirstOrDefault(c => c.CustomerID == key);

            if (customer == null)
            {
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                      HttpStatusCode.NotFound,
                      new ODataError
                      {
                          ErrorCode = "EntityNotFound",
                          Message = "Customer key " + key + " not found"
                      }));
            }
            _context.Customers.Remove(customer);
            _context.SaveChanges();
        }


        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            _context.Dispose();
        }
}

6. Start the app on IIS express, it should handle the oData request. But it will fail when running on IIS, continue the following steps to run it on IIS
7.Restart VS 2012 in admin mode, and change the project setting to create IIS virtual directory and use IIS for testing
8. Edit C:\Windows\System32\inetsrv\config\applicationHost.config for the following part

<add name="ASP.NET v4.0" autoStart="true" managedRuntimeVersion="v4.0" managedPipelineMode="Integrated">

<processModel identityType="ApplicationPoolIdentity" loadUserProfile="true" setProfileEnvironment="true" /></add>

(refer to http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-1-user-profile.aspx and http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx for details if you still get database connection error).