OData Using ASP.NET Web API 2

Introduction

In this post, we will learn about OData by using ASP.NET Web API 2 in an MVC application.

What’s OData Protocol?

The Open Data Protocol (OData) is a data access protocol for the web. OData provides a uniform way to query and manipulate data sets through CRUD operations (create, read, update, and delete).

Prerequisites

As I said before, we are going to use the OData protocol in our MVC application. For this, you must have Visual Studio 2015 (.NET Framework 4.5.2) and a SQL Server.

SQL Database Part

Here, you find the script to create database.

Create the Database

USE [master]
GO

/****** Object:  Database [EmployeeDB]    Script Date: 9/27/2016 2:58:52 AM ******/
CREATE DATABASE [EmployeeDB]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'EmployeeDB', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EmployeeDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'EmployeeDB_log', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EmployeeDB_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [EmployeeDB] SET COMPATIBILITY_LEVEL = 110
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [EmployeeDB].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [EmployeeDB] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [EmployeeDB] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [EmployeeDB] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [EmployeeDB] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [EmployeeDB] SET ARITHABORT OFF 
GO

ALTER DATABASE [EmployeeDB] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [EmployeeDB] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [EmployeeDB] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [EmployeeDB] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [EmployeeDB] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [EmployeeDB] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [EmployeeDB] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [EmployeeDB] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [EmployeeDB] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [EmployeeDB] SET  DISABLE_BROKER 
GO

ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [EmployeeDB] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [EmployeeDB] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [EmployeeDB] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [EmployeeDB] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [EmployeeDB] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [EmployeeDB] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [EmployeeDB] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [EmployeeDB] SET  MULTI_USER 
GO

ALTER DATABASE [EmployeeDB] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [EmployeeDB] SET DB_CHAINING OFF 
GO

ALTER DATABASE [EmployeeDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [EmployeeDB] SET TARGET_RECOVERY_TIME = 0 SECONDS 
GO

ALTER DATABASE [EmployeeDB] SET  READ_WRITE 
GO

Create Your MVC Application

Open Visual Studio and select File, click New Project.

The "New Project" window will pop up. Select ASP.NET Web Application (.NET Framework), name your project, and click OK.

Now, a new dialog will pop up for selecting the template. We are going to choose the Web API template and click Ok.

Add a Model Class

In solution explorer, right click on Models folder > Add > Class > Name your class.

Employee.cs

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace WebAPIODataApp.Models
{
    [Table("Employee")]
    public class Employee
    {
        public int EmployeeID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Gender { get; set; }
        public string Designation { get; set; }
        public int Salary { get; set; }
        public string City { get; set; }
        public string Country { get; set; }


    }
}

Create a Controller

Now, we are going to create a controller. Right-click on the controllers folder > Add > Controller> selecting Web API 2 OData v3 Controller with actions, using Entity Framework > click Add.

After clicking on the Add button, a window will pop up as shown below.

We need to specify our Model class (in this case Employee.cs) and name our controller.

Finally, in order to add a data context class. Click on new data context > given a name for our new data context > Click Add.

The scaffolding adds two code files to the project:

• EmployeesController.cs: defines the controller which implements the OData endpoint.
• EmployeeServContext.cs: ensures that our application is connected with the database.

EmployeesController.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using System.Web.Http;
using System.Web.Http.ModelBinding;
using System.Web.Http.OData;
using System.Web.Http.OData.Routing;
using WebAPIODataApp.Models;

namespace WebAPIODataApp.Controllers
{

    public class EmployeesController : ODataController
    {
        private EmployeeServContext db = new EmployeeServContext();

        // GET: odata/Employees
        [EnableQuery]
        public IQueryable<Employee> GetEmployees()
        {
            return db.Employees;
        }

    }
}

EmployeeServContext.cs

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

namespace WebAPIODataApp.Models
{
    public class EmployeeServContext : DbContext
    {
        // You can add custom code to this file. Changes will not be overwritten.
        // 
        // If you want Entity Framework to drop and regenerate your database
        // automatically whenever you change your model schema, please use data migrations.
        // For more information refer to the documentation:
        // http://msdn.microsoft.com/en-us/data/jj591621.aspx

        public EmployeeServContext() : base("name=EmployeeServContext")
        {
        }

        public System.Data.Entity.DbSet<WebAPIODataApp.Models.Employee> Employees { get; set; }
    }
}

Add EDM and Route

First of all, we need to add a connection string.

In solution explorer, Open the web.config file and add the following section inside the configuration element.

<connectionStrings>
        <add name="EmployeeServContext" connectionString="Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings> 

Note: You must specify the connection string from the EmployeeDB database which has been created above.

Next step, in solution explorer, select App_Start > double click on WebApiConfig.cs, then we should add the following code to the register method:

public static void Register(HttpConfiguration config)
        {
            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
            builder.EntitySet<Employee>("Employees");
            config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());

        }

Explanation

The register method ensures two things:

• Creates an EDM (Entity Data Model).
• Adds a route for OData service.

The EDM is used to create the service metadata document.

At this level, we have two possibilities by using:

• The ODataConventionModelBuilder class which creates an EDM by using default naming conventions.
• The ODataModelBuilder class to create the EDM by adding properties, keys, and navigation properties.

We need to call the MapOdataRoute extension method for routing. We can conclude that our URI for Employees EntitySet is http://localhost:56262/odata/Employees.

Note: When we run our URI for the first time, the database table will be created as follows.

Don’t forget to add some records into the Employee table for a demo.

Consuming OData Service

In order to consume OData service, we will work to display data by using the jqxGrid plugin.

Let’s GO.

Create a Controller

Now, we are going to create a controller. Right-click on the controllers folder > Add > Controller> selecting MVC 5 Controller – Empty > click Add.

HomeController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace WebAPIODataApp.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {

            return View();
        }
    }
}

Adding a View

In HomeController, just right-click on the Index() action, select Add View, and a window will pop up. Create a name for your view. Finally, click Add.

Index.cshtml

@{
    ViewBag.Title = "Data Employees";
}

<h2> Data Employees </h2>

<div id="gridEmployee" style="margin:20px auto;"></div>

@section scripts {


    <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/scripts/jquery-1.11.1.min.js"></script>

    <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/jqx-all.js"></script>

    <link rel="stylesheet" type="text/css" href="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/styles/jqx.base.css" />

    <script type="text/javascript">

    $(document).ready(function () {

        // In this part, you need to prepare your data
        var source =
            {
                datatype: "json",

                // Here you will declare all fields that must be used in the grid
                datafields: [

               { name: 'EmployeeID', type: 'number' },
               { name: 'FirstName', type: 'string' },
               { name: 'LastName', type: 'string' },
               { name: 'Gender', type: 'string' },
               { name: 'Designation', type: 'string' },
               { name: 'Salary', type: 'number' },
               { name: 'City', type: 'string' },
               { name: 'Country', type: 'string' }

                ],
                // call the action which retrieve data employees in json format
                url: '/odata/Employees'
            };

        var dataAdapter = new $.jqx.dataAdapter(source);

        // displaying data in the grid with jqxGrid
        $("#gridEmployee").jqxGrid(
            {
                width: 800,
                source: dataAdapter,
                pageable: true,
                sortable: true,

                columns: [

                    { text: "Employee ID", datafield: "EmployeeID" },
                    { text: "FirstName", datafield: "FirstName" },
                    { text: "LastName", datafield: "LastName" },
                    { text: "Gender", datafield: "Gender" },
                    { text: "Designation", datafield: "Designation" },
                    { text: "Salary", datafield: "Salary" },
                    { text: "City", datafield: "City" },
                    { text: "Country", datafield: "Country" }

                ]
            });
    });
    </script>
}

Output

That’s all. Please send your feedback and queries in the comments.