ASP.NET Web API feat. OData

OData is an open standard protocol allowing the creation and consumption of queryable and interoperable RESTful APIs. It was initiated by Microsoft and it’s mostly known to .NET Developers from WCF Data Services. There are many other server platforms supporting OData services such as Node.js, PHP, Java and SQL Server Reporting Services. More over, Web API also supports OData and this post will show you how to integrate those two.

Theory

OData is known for it’s powerfull query capabilities. I am sure that most of you have come across an HTTP REST request such as the following:

http://www.website.com/odata/Employees?$top=10&$orderby=name

The above request will retrieve the top 10 employees from the database ordered by name. The key point to remember for the moment, is that the result set will be executed at the database and not at the service layer nor at the client side. This allows clients, to encode and execute complex query instructions directly to the database without increasing web server’s memory or CPU usage. OData follows the REST architectural style and is used to expose data over HTTP services in an elegant way. Not only queries but also CRUD operations are supported, something that we will certainly show later on this post. One of it’s great features is that OData exposes metadata to be used by code generation tools, in order to create proxies for consuming it’s services. You can read more about OData here.

There are two ways to use OData in a Web API project. The first and the simplest one, is this where you just want to enable OData query syntax for your Web API controllers. The second one is where you want to use all the OData package capabilities by declaring the OData available serialization formats, the resources and the availables actions on that resources you want to expose. Enough with the theory, let’s move to practice.

Enable OData Query syntax to a Web API Controller

To start with, create an empty solution named ODataWebAPI and add a class library project named ODataWebAPI.Data. We ‘ll use this project to boostrap a database using Code First Entity Framework. Make sure you install Entity Framework and MockData packages using the Nuget Package Manager. We ‘ll use the latter to create multiple records when seeding the database for the first time. Add the following 3 entity classes:

Employee.cs

public class Employee
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string Surname { get; set; }
        public string Email { get; set; }
        public int AddressID { get; set; }
        public virtual Address Address { get; set; }
        public int CompanyID { get; set; }
        public virtual Company Company { get; set; }
    }

Address.cs

public class Address
    {
        public int ID { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string State { get; set; }
        public string ZipCode { get; set; }
    }

Company.cs

public class Company
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public virtual List<Employee> Employees { get; set; }
        public Company()
        {
            Employees = new List<Employee>();
        }
    }

Now add the EntityTypeConfigurations for the above classes:

EmployeeConfiguration.cs

class EmployeeConfiguration : EntityTypeConfiguration<Employee>
    {
        public EmployeeConfiguration()
        {
            ToTable("Employees");
            Property(e => e.CompanyID).IsRequired();
            Property(e => e.AddressID).IsRequired();
        }
    }

CompanyConfiguration.cs

public class CompanyConfiguration: EntityTypeConfiguration<Company>
    {
        public CompanyConfiguration()
        {
            ToTable("Companies");
        }
    }

One employee may have one specific address and one company may have many employees. Time to add the DbContext required class.

EntitiesContext.cs

public class EntitiesContext : DbContext
    {
        public EntitiesContext()
            : base("EntitiesContext")
        {
            // Do not forget to set the connection string in Web.config..
            Configuration.ProxyCreationEnabled = false;
        }
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Company> Companies { get; set; }
        public DbSet<Address> Addresses { get; set; }
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new EmployeeConfiguration());
            modelBuilder.Configurations.Add(new CompanyConfiguration());
        }
    }

I chose on purpose to build a schema such as the previous, cause using the MockData package, it will be easy to seed the database with as many records as we wish. MockData supports fake data for properties such as First name, Surname, Email, Address etc.. Add the following initializer class, which create 10 companies and 200 employees. All employees will be assigned to one of the created companies.

EntitiesInitializer.cs

public class EntitiesInitializer : DropCreateDatabaseIfModelChanges<EntitiesContext>
    {
        protected override void Seed(EntitiesContext context)
        {
            // Companies
            GetCompanies().ForEach(c => context.Companies.Add(c));
            // Addresses
            GetAddresses().ForEach(a => context.Addresses.Add(a));
            // Employees
            GetEmployees().ForEach(e => context.Employees.Add(e));
            base.Seed(context);
        }
        private static List<Company> GetCompanies()
        {
            List<Company> _companies = new List<Company>();
            for (int i = 1; i <= 10; i++)
            {
                _companies.Add(new Company()
                    {
                        ID = i,
                        Name = MockData.Company.Name()
                    });
            }
            return _companies;
        }
        private static List<Address> GetAddresses()
        {
            List<Address> _addresses = new List<Address>();
            for (int i = 1; i <= 200; i++)
            {
                _addresses.Add(new Address()
                {
                    ID = i,
                    City = MockData.Address.City(),
                    Country = MockData.Address.Country(),
                    State = MockData.Address.State(),
                    ZipCode = MockData.Address.ZipCode()
                });
            }
            return _addresses;
        }
        private static List<Employee> GetEmployees()
        {
            List<Employee> _employees = new List<Employee>();
            for (int i = 1; i <= 200; i++)
            {
                _employees.Add(new Employee()
                {
                    ID = i,
                    FirstName = MockData.Person.FirstName(),
                    Surname = MockData.Person.Surname(),
                    AddressID = i,
                    CompanyID = new Random().Next(1, 10),
                    Email = MockData.Internet.Email(),
                });
            }
            return _employees;
        }
    }

Web API

Add a new Empty Web Application project named ODataWebAPI.API selecting the Web API template. For start add a reference to the ODataWebAPI.Data project and a connection string the EntitiesContext we created before. Alter it if required to reflect your development environment (make sure it takes one line only).

Web.config connection string

<connectionStrings>
  <add name="EntitiesContext" providerName="System.Data.SqlClient" connectionString="Server=(localdb)\v11.0; Database=CompanyDB; Trusted_Connection=true; MultipleActiveResultSets=true" />
</connectionStrings>

Install Entity Framework from nuget packages and set the Database initializer at the Application Start event.

Global.asax

protected void Application_Start()
        {
            GlobalConfiguration.Configure(WebApiConfig.Register);
            // Init the database
            Database.SetInitializer(new EntitiesInitializer());
        }

Supposing you wanted to expose your Employees through an API Controller you would create an API Controller as follow:

EmployeesController.cs

public class EmployeesController : ApiController
    {
        private EntitiesContext _dbContext;
        public EmployeesController()
        {
            this._dbContext = new EntitiesContext();
        }
        public IEnumerable<Employee> GetEmployees()
        {
            try
            {
                return _dbContext.Employees.ToArray();
            }
            catch(Exception ex)
            {
                throw new HttpResponseException(HttpStatusCode.BadRequest);
            }
        }
        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            _dbContext.Dispose();
        }
    }

Build, run your application and request all employees as you would normally have done for a common Web API Controller:

http://localhost:your_port/api/employees

Ensure that seed data have been successfully deployed to database.

Now, if you try to make an OData request such as the following, the query will fail and bring the same data again.

http://localhost:46184/api/employees?$top=5

It only takes two steps to enable OData syntax querying for the specific Web API Controller:

• Make action return IQuerable instead of IEnumerable
• Apply Queryable attribute in the action

Let’s apply the above changes to the GetEmployees action:

[Queryable]
public IQueryable<Employee> GetEmployees()
  {
      return _dbContext.Employees;
  }

Now again, fire the application and request top 5 employess using OData syntax querying. Now try to order those 5 employees by Surname:

http://localhost:46184/api/employees?$top=5&$orderby=Surname

Check you employees data, find a specific one and try to locate the record using a filter operator.

http://localhost:46184/api/employees?$filter=Surname eq 'McLaughlin'

Ensure that both of the two OData queries work. But wait a minute. At the service side we only return _dbContext.Employees so how is this possible anyway? The answer is Deferred execution. Before start explaining the concept of deferred execution, open SQL Profiler and start a trace for the server where the CompanyDB database exists. Then put a breakpoint in the EmployeesController at the point where we return all employees and request top 5 employees as we did before. Notice that even when you pass this breakpoint, nothing has been executed yet.

When you hit F5 and before the response is returned to client, you can see the query executed in database:

exec sp_executesql N'SELECT TOP (@p__linq__0)
    [Extent1].[ID] AS [ID],
    [Extent1].[FirstName] AS [FirstName],
    [Extent1].[Surname] AS [Surname],
    [Extent1].[Email] AS [Email],
    [Extent1].[AddressID] AS [AddressID],
    [Extent1].[CompanyID] AS [CompanyID]
    FROM [dbo].[Employees] AS [Extent1]
    ORDER BY [Extent1].[ID] ASC',N'@p__linq__0 int',@p__linq__0=5

So what is actually happening here, is that 2 different expression trees were merged at the Web API stack level before actually transformed by Entity Framework to SQL query and being executed at database.

To complete the Enable OData Query syntax to a Web API Controller section, let’s see some of the basic operations enabled by applying the enabling OData syntax quering to our Web API Controller.

• $filter: LINQ Where equivalent expression [/api/employees?$filter=Surname eq ‘Rice’]
• $orderby: LINQ OrderBy equivalent expreesion [/api/employees?$top=5&orderby=Surname]
• $top: LINQ Take equivalent expreesion [/api/employees?$top=5]
• $skip: LINQ Skip equivalent expreesion [/api/employees?$top=5&$skip=10]
• $select: LINQ Select equivalent expreesion [api/employees?$select=FirstName,Surname,Email]

Enable OData formats and Entity Data Models

If OData Query syntax is not enough for you and you want more complex operations through OData services such as accessing all employees assigned to a specific company or supporting CRUD operations, then you need to take a step further than we did before, and unlock the full OData capabilities by enabling OData formatting and Entity Data Models as well. First thing you need to do, is install Microsoft.AspNet.WebApi.OData package in to your API project.

Next thing to do, is create a route for OData services by passing an Entity Data Model as one of it’s arguments. An Entity Data Model is a definition of your data you want to expose over the wire, the resources (EntitySets), the relationships between them (AssociationSets) and the available actions than can be invoked for those resources using OData services. Switch to the WebApiConfig.cs file and paste the following code:

WebApiConfig.cs

public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            // Web API configuration and services
            // Web API routes
            config.MapHttpAttributeRoutes();
            config.Routes.MapODataRoute("ODataRoute", "odata", GenerateEntityDataModel());
            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );
        }
        private static IEdmModel GenerateEntityDataModel()
        {
            ODataModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Employee>("Employees");
            builder.EntitySet<Address>("Addresses");
            builder.EntitySet<Company>("Companies");
            return builder.GetEdmModel();
        }
    }

We used the MapODataRoute extension method to register an OData route with a prefix odata, passing an Entity Data Model where we registered all of our Entities. You will not be able to use all OData capabilities through the default Web API Controller. Instead you need to create a controller that inherits EntitySetController<T,K> where T is the entity class you are exposing (e.g. Employee) and K is entity’s class primary key type (e.g. int). EntitySetController is an abstract class that derives from ODataController which gives you all the required functionality for OData services.

// Summary:
    //     Provides a convenient starting point for a controller that exposes an OData
    //     entity set. This is the synchronous version of System.Web.Http.OData.AsyncEntitySetController<TEntity,TKey>.
    [CLSCompliant(false)]
    [ODataNullValue]
    public abstract class EntitySetController<TEntity, TKey> : ODataController where TEntity : class
    {
        protected EntitySetController();
        public ODataPath ODataPath { get; }
        public ODataQueryOptions<TEntity> QueryOptions { get; }
        protected internal virtual TEntity CreateEntity(TEntity entity);
        public virtual void CreateLink(TKey key, string navigationProperty, Uri link);
        public virtual void Delete(TKey key);
        public virtual void DeleteLink(TKey key, string relatedKey, string navigationProperty);
        public virtual void DeleteLink(TKey key, string navigationProperty, Uri link);
        public virtual IQueryable<TEntity> Get();
        public virtual HttpResponseMessage Get(TKey key);
        protected internal virtual TEntity GetEntityByKey(TKey key);
        protected internal virtual TKey GetKey(TEntity entity);
        public virtual HttpResponseMessage HandleUnmappedRequest(ODataPath odataPath);
        public virtual HttpResponseMessage Patch(TKey key, Delta<TEntity> patch);
        protected internal virtual TEntity PatchEntity(TKey key, Delta<TEntity> patch);
        public virtual HttpResponseMessage Post(TEntity entity);
        public virtual HttpResponseMessage Put(TKey key, TEntity update);
        protected internal virtual TEntity UpdateEntity(TKey key, TEntity update);
    }

I have commented out the EmployeesController we created before and created the following implementation.

EmployeesController.cs

public class EmployeesController : EntitySetController<Employee, int>
    {
        private EntitiesContext _dbContext;
        public EmployeesController()
        {
            this._dbContext = new EntitiesContext();
        }
        [Queryable]
        public override IQueryable<Employee> Get()
        {
            return _dbContext.Employees;
        }
        protected override Employee GetEntityByKey(int key)
        {
            return _dbContext.Employees.Where(e => e.ID == key).FirstOrDefault();
        }
        // /odata/Employees(1)/Address
        public Address GetAddressFromEmployee(int key)
        {
            return _dbContext.Employees.Where(e => e.ID == key)
                .Select(e => e.Address).FirstOrDefault();
        }
        protected override Employee CreateEntity(Employee entity)
        {
            _dbContext.Employees.Add(entity);
            _dbContext.SaveChanges();
            return entity;
        }
        protected override Employee UpdateEntity(int key, Employee update)
        {
            if (!_dbContext.Employees.Any(e => e.ID == key))
            {
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                    HttpStatusCode.NotFound,
                    new ODataError
                    {
                        ErrorCode = "NotFound.",
                        Message = "Employee " + key + " not found."
                    }));
            }
            update.ID = key;
            _dbContext.Employees.Attach(update);
            _dbContext.Entry(update).State = System.Data.Entity.EntityState.Modified;
            _dbContext.SaveChanges();
            return update;
        }
        protected override Employee PatchEntity(int key, Delta<Employee> patch)
        {
            var employee = _dbContext.Employees.FirstOrDefault(e => e.ID == key);
            if (employee == null)
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                    HttpStatusCode.NotFound,
                    new ODataError
                    {
                        ErrorCode = "NotFound.",
                        Message = "Employee " + key + " not found."
                    }));
            patch.Patch(employee);
            _dbContext.SaveChanges();
            return employee;
        }
        public override void Delete(int key)
        {
            var employee = _dbContext.Employees.Where(a => a.ID == key).FirstOrDefault();
            if (employee != null)
            {
                _dbContext.Employees.Remove(employee);
                _dbContext.SaveChanges();
            }
            else
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                    HttpStatusCode.NotFound,
                    new ODataError
                    {
                        ErrorCode = "NotFound.",
                        Message = "Employee " + key + " not found."
                    }));
        }
        // The base POST method will call to form the location header
        protected override int GetKey(Employee entity)
        {
            return entity.ID;
        }
        protected override void Dispose(bool disposing)
        {
            base.Dispose(disposing);
            _dbContext.Dispose();
        }
    }

Let’s explain one by one all the actions we added on this EntitySetController controller. Since we wanted to expose Employee data through this controller, we made it of type EntitySetController<Employee,int> I believe the Get method is self explanatory. You still need to apply the Querable attribute if you want to query Employees using the OData query syntax. We have overriden the GetEntityByKey(int key) method, so we can access individual employees. Check the example bellow:

You can now access an individual employee by sending a request in the following format:

Access individual employee by id

http://localhost:46184/odata/Employees(id)

If you use Fiddler you will also notice some metadata to be used by code generation tools. In the preceding example I put application/json as the accept header but this isn’t the only format OData supports. You can also request application/atom+xml:

Let’s move forward and check the GetAddressFromEmployee(int key) which guess what, it fetches a specific employee’s address. Notice that this method isn’t an override but we added by ourselves in order to support a linking between an employee and it’s address, through OData querying. More over, there is a name convention you must follow for this kind of actions, that is first start with Get which declares that is an HTTP GET operation, followed by what exactly you want to bring, that is an employee Address, followed by a From and the controller’s entity Employee. Note that there must be a relation between an employee and an address otherwise nothing will happen. The following request will retrieve the address for the employee with id=55:

/odata/Employees(55)/Address

CreateEntity(Employee entity),UpdateEntity(int key, Employee update) and PatchEntity(int key, Delta patch) methods, work almost in the same way. Notice that there is no prefix such as POST, PUT or PATCH for all these methods which means that the base class has already those methods implemented for you. This means, that if you make a POST request to create an employee, the base POST method will capture your request and it will dispatch it to the CreateEntity(Employee entity). Before returning the result to the client it will call the GetKey(Employee entity) in order to get the created employee’s id and encapsulate it inside the response Location header. Update and Patch methods works quite similar. Let’s see a POST request in action:

If you want to delete a specific employee, simply send a DELETE HTTP request such as the following:

/odata/Employees(203)

Clients

I couldn’t resist not creating an AngularJS client and that’s because AngularJS makes accessing OData services a piece of cake. I won’t paste all the code on this post, since you can always download this solution from my github account. You will find that I have created two other EntitySetControllers, one for Companies and another for Addresses. For example, you will find that I created the following method in the CompaniesController so that you can access a company’s employees:

Part of CompaniesController.cs

// Uses name convention
        // /odata/Companies(1)/Employees
        [Queryable]
        public IQueryable<Employee> GetEmployeesFromCompany([FromODataUri] int key)
        {
            return _dbContext.Employees.Where(e => e.CompanyID == key);
        }

More over, just to show you how extensible OData protocol can be using the Web API, I have created a custom action for CompaniesController, so that you can add an employee directly to a specific company. I defined the custom action in the Entity Data Model..

private static IEdmModel GenerateEntityDataModel()
        {
            ODataModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Employee>("Employees");
            builder.EntitySet<Address>("Addresses");
            var companies = builder.EntitySet<Company>("Companies");
            // Define a custom action on Companies
            var addCompanyEmployeeByIdAction = builder.Entity<Company>().Action("AddCompanyEmployee");
            addCompanyEmployeeByIdAction.Parameter<string>("name");
            addCompanyEmployeeByIdAction.Parameter<string>("surname");
            addCompanyEmployeeByIdAction.Parameter<string>("email");
            addCompanyEmployeeByIdAction.Parameter<int>("address");
            addCompanyEmployeeByIdAction.ReturnsFromEntitySet<Employee>("Employees");
            return builder.GetEdmModel();
        }

And the respective action in the Companies controller:

[HttpPost]
        public Employee AddCompanyEmployee([FromODataUri] int key, ODataActionParameters actionParams)
        {
            Employee employee = null;
            try
            {
                if (actionParams["name"] != null && actionParams["surname"] != null && actionParams["email"] != null && actionParams["address"] != null)
                {
                    string employeeName = actionParams["name"].ToString();
                    string employeeSurname = actionParams["surname"].ToString();
                    string employeeEmail = actionParams["email"].ToString();
                    int employeeAddress = Int32.Parse(actionParams["address"].ToString());
                    employee = new Employee()
                    {
                        FirstName = employeeName,
                        Surname = employeeSurname,
                        Email = employeeEmail,
                        AddressID = employeeAddress,
                        CompanyID = key
                    };
                    _dbContext.Employees.Add(employee);
                    _dbContext.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw new HttpResponseException(
                    Request.CreateODataErrorResponse(
                    HttpStatusCode.NotFound,
                    new ODataError
                    {
                        ErrorCode = "Error",
                        Message = ex.Message
                    }));
            }
            return employee;
        }

Now if you wanted to invoke that method, you should sent a POST request to a URL such as this:

http://localhost:46184/odata/Companies(id)/AddCompanyEmployee

From AngularJS pespective, let us see the $resource service first and then the controller.

employeeService $resource service

angular.module('mainApp')
    .factory('employeeService', function ($resource) {
        var odataUrl = '/odata/Employees';
        return $resource('', {},
            {
                'getAll': { method: 'GET', url: odataUrl },
                'getTop10': { method: 'GET', url: odataUrl + '?$top=10' },
                'create': { method: 'POST', url: odataUrl },
                'patch': { method: 'PATCH', params: { key: '@key' }, url: odataUrl + '(:key)' },
                'getEmployee': { method: 'GET', params: { key: '@key' }, url: odataUrl + '(:key)' },
                'getEmployeeAdderss': { method: 'GET', params: { key: '@key' }, url: odataUrl + '(:key)' + '/Address' },
                'deleteEmployee': { method: 'DELETE', params: { key: '@key' }, url: odataUrl + '(:key)' }
            });
    })

angular.module('mainApp')
    .controller('appCtrl', function ($scope, employeeService, notificationFactory) {
        $scope.currentEmployee = {};
        // Get Top 10 Employees
        $scope.getTop10Employees = function () {
            (new employeeService()).$getTop10()
                .then(function (data) {
                    $scope.employees = data.value;
                    $scope.currentEmployee = $scope.employees[0];
                    $scope.setCurrentEmployeeAddress();
                    notificationFactory.success('Employeess loaded.');
                });
        };
        // Set active employee for patch update
        $scope.setEmployee = function (employee) {
            $scope.currentEmployee = employee;
            $scope.setCurrentEmployeeAddress();
        };
        $scope.setCurrentEmployeeAddress = function () {
            var currentEmployee = $scope.currentEmployee;
            return (new employeeService({
                "ID": currentEmployee.ID,
            })).$getEmployeeAdderss({ key: currentEmployee.ID })
            .then(function (data) {
                $scope.currentEmployee.City = data.City;
                $scope.currentEmployee.Country = data.Country;
                $scope.currentEmployee.State = data.State;
            });
        }
        // Update Selected Employee
        $scope.updateEmployee = function () {
            var currentEmployee = $scope.currentEmployee;
            console.log(currentEmployee.Email);
            if (currentEmployee) {
                return (new employeeService({
                    "ID": currentEmployee.ID,
                    "FirstName": currentEmployee.FirstName,
                    "Surname": currentEmployee.Surname,
                    "Email": currentEmployee.Email
                })).$patch({ key: currentEmployee.ID })
                .then(function (data) {
                    notificationFactory.success('Employee with ID ' + currentEmployee.ID + ' updated.')
                });
            }
        }
        $scope.deleteEmployee = function () {
            var currentEmployee = $scope.currentEmployee;
            return (new employeeService({
                "ID": currentEmployee.ID,
            })).$deleteEmployee({ key: currentEmployee.ID })
            .then(function (data) {
                notificationFactory.success('Employee with ID ' + currentEmployee.ID + ' removed.');
                $scope.getTop10Employees();
            });
        }
    });

When you run the application, the top 10 employees will be loaded on the left side. Selecting an employee from the table, will fill the form on the right side, where you can see employee’s information (name, surname, address, company etc.). You can PATCH update employee’s information or delete one.

That’s it, we are done showing how to use OData with ASP.NET Web API. There are much more you can do with OData and that’s why I recommended you to navigate and study it’s official website. I hope you enjoyed the post. You can download the project we built from here.