Supporting OData CRUD Operations in ASP.NET Web API

The OData protocol defines conventions for all of the CRUD operations: create, read, update (including partial update), and delete. The tutorial Getting Started with OData shows how to create a read-only service. In this tutorial, we'll add the remaining CRUD operations to this service.

Download the completed project.

Using Entity Framework Code First

For simplicity, the previous tutorial used a static list of products, stored in memory. The first change is to replace this list with an actual database, using Entity Framework (EF).

Start by adding a class named ProductsContext to the Models folder. This class derives from DbContext and is the "glue" between our domain model (the Product class) and EF.

using System.Data.Entity;

namespace ProductService.Models
{
    public class ProductsContext : DbContext
    {
        public ProductsContext()
            : base("name=ProductsContext")
        {
        }

        static ProductsContext()
        {
            Database.SetInitializer(new ProductsContextInitializer());
        }

        public DbSet<Product> Products { get; set; }
    }
}

Notice that the static constructor refers to a class named ProductsContextInitializer, which does not exist yet. We’ll create that class now.

Add a class named ProductsContextInitializer to the Models folder. This class will seed the database with some initial values.

using System.Collections.Generic;
using System.Data.Entity;

namespace ProductService.Models
{
    public class ProductsContextInitializer : 
        DropCreateDatabaseIfModelChanges<ProductsContext>
    {
        protected override void Seed(ProductsContext context)
        {
            var products = new List<Product>()
            {
                new Product() { Name = "Hat", Price = 15, Category = "Apparel" },
                new Product() { Name = "Socks", Price = 5, Category = "Apparel" },
                new Product() { Name = "Scarf", Price = 12, Category = "Apparel" },
                new Product() { Name = "Yo-yo", Price = 4.95M, Category = "Toys" },
                new Product() { Name = "Puzzle", Price = 8, Category = "Toys" },
            };

            products.ForEach(p => context.Products.Add(p));
        }
    }
}

Add a LocalDB connection string to Web.config, in the <connectionStrings> element:

  <connectionStrings>
    <add name="ProductsContext" 
         connectionString="Data Source=(localdb)\v11.0; Initial Catalog=ProductsContext; Integrated Security=True; MultipleActiveResultSets=True; AttachDbFilename=|DataDirectory|ProductsContext.mdf"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

Modify the Products Controller

Open the ProductsController class and replace the previous implementation with the following:

public class ProductsController : EntitySetController<Product, int>
{
    ProductsContext _context = new ProductsContext();

    public override IQueryable<Product> Get()
    {
        return _context.Products;
    }

    protected override Product GetEntityByKey(int key)
    {
        return _context.Products.FirstOrDefault(p => p.ID == key);
    }

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

}

This version implements the same GET methods; the only change is to get the products from the database using EF. Next, we will add methods for the remaining CRUD operations.

For POST requests, override the EntitySetController.CreateEntity and GetKey methods. The GetKey method returns the value of the entity key, given the entity.

protected override Product CreateEntity(Product entity)
{
    _context.Products.Add(entity);
    _context.SaveChanges();
    return entity;
}

protected override int GetKey(Product entity)
{
    return entity.ID;
}

For PUT requests, override the UpdateEntity method.

protected override Product UpdateEntity(int key, Product update)
{
    // Verify that a product with this ID exists.
    if (!_context.Products.Any(p => p.ID == key))
    {
        throw new HttpResponseException(HttpStatusCode.NotFound);
    }

    _context.Products.Attach(update); // Replace the existing entity in the DbSet.
    _context.Entry(update).State = System.Data.EntityState.Modified;  
    _context.SaveChanges();
    return update;
}

For PATCH requests (partial updates), override the PatchEntity method. This method is interesting because it takes a Delta<T> instance, where T is the model type. The Delta<T> class keeps track of which properties were changed. The Delta<T>.Patch method applies the changes to the existing entity.

protected override Product PatchEntity(int key, Delta<Product> patch)
{
    Product product = _context.Products.FirstOrDefault(p => p.ID == key);
    if (product == null)
    {
        throw new HttpResponseException(HttpStatusCode.NotFound);
    }
    patch.Patch(product);
    _context.SaveChanges();
    return product;
}

For DELETE requests, override the Delete method.

public override void Delete([FromODataUri] int key)
{
    Product product = _context.Products.FirstOrDefault(p => p.ID == key);
    if (product == null)
    {
        throw new HttpResponseException(HttpStatusCode.NotFound);
    }
    _context.Products.Remove(product);
    _context.SaveChanges();
}

Notice that almost all of this code deals with the data layer, not the HTTP messages.

Update the C# Client

This section builds on the C# client from the earlier tutorial, to add the CRUD operations on the client side.

If you followed that tutorial, you need to update the service reference, to reflect the changes in the OData service. Open the ProductsClient project in Visual Studio. In Solution Explorer, expand the Service References folder. Right-click on the service reference and select Update Service Reference.

Now you can add code to create, delete, or update entities.

Create a new product:

// Add a new product.
var newProduct = new ProductsService.Product()
{
    Name = "Orange Juice",
    Category = "Groceries",
    Price = 2.49M
};
container.AddObject("Products", newProduct);

// Commit changes at server.
container.SaveChanges();

Delete a product:

// Delete a product
var deletedProduct = container.Products.Where(p => p.ID == 2).FirstOrDefault();
if (deletedProduct != null)
{
    container.DeleteObject(deletedProduct);
    container.SaveChanges();
}

Update a product:

var updatedProduct = container.Products.Where(p => p.ID == 4).First();
if (updatedProduct != null)
{
    updatedProduct.Price = 9.99M;
    container.UpdateObject(updatedProduct);
    // Use PATCH not MERGE.
    container.SaveChanges(SaveChangesOptions.PatchOnUpdate);  
}

As you can see, WCF Data Services lets you call into the OData service without worrying too much about the underlying protocol. One thing to remember is that WCF does not send changes to the server immediately. Instead, it tracks changes locally until you call SaveChanges. For more information, see Managing the Data Service Context (MSDN).

By Mike Wasson, Mike Wasson is a programmer-writer at Microsoft.