OData Using ASP.NET Web API 2

In this article, you will learn about OData using ASP.NET Web API 2.

Introduction

In this post, we will learn about OData by using ASP.Net Web API 2 in 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 earlier, we are going to use OData protocol in our MVC application. For this, you must have Visual Studio 2015 (.NET Framework 4.5.2) and SQL Server.

SQL Database part

Here, find the script to create database.

Create Database

  1. USE [master]  
  2. GO  
  3.   
  4. /****** Object: Database [EmployeeDB] Script Date: 9/27/2016 2:58:52 AM ******/  
  5. CREATE DATABASE [EmployeeDB]  
  6. CONTAINMENT = NONE  
  7. ON PRIMARY   
  8. NAME = N'EmployeeDB', FILENAME = N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\EmployeeDB.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )  
  9. LOG ON   
  10. 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%)  
  11. GO  
  12.   
  13. ALTER DATABASE [EmployeeDB] SET COMPATIBILITY_LEVEL = 110  
  14. GO  
  15.   
  16. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  17. begin  
  18. EXEC [EmployeeDB].[dbo].[sp_fulltext_database] @action = 'enable'  
  19. end  
  20. GO  
  21.   
  22. ALTER DATABASE [EmployeeDB] SET ANSI_NULL_DEFAULT OFF   
  23. GO  
  24.   
  25. ALTER DATABASE [EmployeeDB] SET ANSI_NULLS OFF   
  26. GO  
  27.   
  28. ALTER DATABASE [EmployeeDB] SET ANSI_PADDING OFF   
  29. GO  
  30.   
  31. ALTER DATABASE [EmployeeDB] SET ANSI_WARNINGS OFF   
  32. GO  
  33.   
  34. ALTER DATABASE [EmployeeDB] SET ARITHABORT OFF   
  35. GO  
  36.   
  37. ALTER DATABASE [EmployeeDB] SET AUTO_CLOSE OFF   
  38. GO  
  39.   
  40. ALTER DATABASE [EmployeeDB] SET AUTO_CREATE_STATISTICS ON   
  41. GO  
  42.   
  43. ALTER DATABASE [EmployeeDB] SET AUTO_SHRINK OFF   
  44. GO  
  45.   
  46. ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS ON   
  47. GO  
  48.   
  49. ALTER DATABASE [EmployeeDB] SET CURSOR_CLOSE_ON_COMMIT OFF   
  50. GO  
  51.   
  52. ALTER DATABASE [EmployeeDB] SET CURSOR_DEFAULT GLOBAL   
  53. GO  
  54.   
  55. ALTER DATABASE [EmployeeDB] SET CONCAT_NULL_YIELDS_NULL OFF   
  56. GO  
  57.   
  58. ALTER DATABASE [EmployeeDB] SET NUMERIC_ROUNDABORT OFF   
  59. GO  
  60.   
  61. ALTER DATABASE [EmployeeDB] SET QUOTED_IDENTIFIER OFF   
  62. GO  
  63.   
  64. ALTER DATABASE [EmployeeDB] SET RECURSIVE_TRIGGERS OFF   
  65. GO  
  66.   
  67. ALTER DATABASE [EmployeeDB] SET DISABLE_BROKER   
  68. GO  
  69.   
  70. ALTER DATABASE [EmployeeDB] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
  71. GO  
  72.   
  73. ALTER DATABASE [EmployeeDB] SET DATE_CORRELATION_OPTIMIZATION OFF   
  74. GO  
  75.   
  76. ALTER DATABASE [EmployeeDB] SET TRUSTWORTHY OFF   
  77. GO  
  78.   
  79. ALTER DATABASE [EmployeeDB] SET ALLOW_SNAPSHOT_ISOLATION OFF   
  80. GO  
  81.   
  82. ALTER DATABASE [EmployeeDB] SET PARAMETERIZATION SIMPLE   
  83. GO  
  84.   
  85. ALTER DATABASE [EmployeeDB] SET READ_COMMITTED_SNAPSHOT OFF   
  86. GO  
  87.   
  88. ALTER DATABASE [EmployeeDB] SET HONOR_BROKER_PRIORITY OFF   
  89. GO  
  90.   
  91. ALTER DATABASE [EmployeeDB] SET RECOVERY SIMPLE   
  92. GO  
  93.   
  94. ALTER DATABASE [EmployeeDB] SET MULTI_USER   
  95. GO  
  96.   
  97. ALTER DATABASE [EmployeeDB] SET PAGE_VERIFY CHECKSUM   
  98. GO  
  99.   
  100. ALTER DATABASE [EmployeeDB] SET DB_CHAINING OFF   
  101. GO  
  102.   
  103. ALTER DATABASE [EmployeeDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   
  104. GO  
  105.   
  106. ALTER DATABASE [EmployeeDB] SET TARGET_RECOVERY_TIME = 0 SECONDS   
  107. GO  
  108.   
  109. ALTER DATABASE [EmployeeDB] SET READ_WRITE   
  110. GO  
Create your MVC application

Open Visual Studio and select File >> New Project.

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



Now, new dialog will pop up for selecting the template. We are going to choose 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
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations.Schema;  
  4. using System.Linq;  
  5. using System.Web;  
  6.   
  7. namespace WebAPIODataApp.Models  
  8. {  
  9.     [Table("Employee")]  
  10.     public class Employee  
  11.     {  
  12.         public int EmployeeID { get; set; }  
  13.         public string FirstName { get; set; }  
  14.         public string LastName { get; set; }  
  15.         public string Gender { get; set; }  
  16.         public string Designation { get; set; }  
  17.         public int Salary { get; set; }  
  18.         public string City { get; set; }  
  19.         public string Country { get; set; }  
  20.   
  21.   
  22.     }  
  23. }  
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 Add button, window will pop up, as shown below.

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



Finally, in order to add 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 to the database.

EmployeesController.cs

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data;  
  4. using System.Data.Entity;  
  5. using System.Data.Entity.Infrastructure;  
  6. using System.Linq;  
  7. using System.Net;  
  8. using System.Net.Http;  
  9. using System.Threading.Tasks;  
  10. using System.Web.Http;  
  11. using System.Web.Http.ModelBinding;  
  12. using System.Web.Http.OData;  
  13. using System.Web.Http.OData.Routing;  
  14. using WebAPIODataApp.Models;  
  15.   
  16. namespace WebAPIODataApp.Controllers  
  17. {  
  18.       
  19.     public class EmployeesController : ODataController  
  20.     {  
  21.         private EmployeeServContext db = new EmployeeServContext();  
  22.   
  23.         // GET: odata/Employees  
  24.         [EnableQuery]  
  25.         public IQueryable<Employee> GetEmployees()  
  26.         {  
  27.             return db.Employees;  
  28.         }  
  29.   
  30.     }  
EmployeeServContext.cs
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.Entity;  
  4. using System.Linq;  
  5. using System.Web;  
  6.   
  7. namespace WebAPIODataApp.Models  
  8. {  
  9.     public class EmployeeServContext : DbContext  
  10.     {  
  11.         // You can add custom code to this file. Changes will not be overwritten.  
  12.         //   
  13.         // If you want Entity Framework to drop and regenerate your database  
  14.         // automatically whenever you change your model schema, please use data migrations.  
  15.         // For more information refer to the documentation:  
  16.         // http://msdn.microsoft.com/en-us/data/jj591621.aspx  
  17.       
  18.         public EmployeeServContext() : base("name=EmployeeServContext")  
  19.         {  
  20.         }  
  21.   
  22.         public System.Data.Entity.DbSet<WebAPIODataApp.Models.Employee> Employees { get; set; }  
  23.     }  
  24. }  
Add EDM and route

First of all, we need to add connection string.

In Solution Explorer, open web.config file and add inside configuration element in the following section.
  1. <connectionStrings>  
  2.         <add name="EmployeeServContext" connectionString="Data Source=.;Initial Catalog=EmployeeDB;Integrated Security=True" providerName="System.Data.SqlClient" />  
  3.   </connectionStrings>   
Note - You must specify the connection string from EmployeeDB database which has been created above.

Next step is - In Solution Explorer, select App_Start > double click on WebApiConfig.cs, then we should add the following code to the register method:
  1. public static void Register(HttpConfiguration config)  
  2.       {  
  3.           ODataConventionModelBuilder builder = new ODataConventionModelBuilder();  
  4.           builder.EntitySet<Employee>("Employees");  
  5.           config.Routes.MapODataRoute("odata""odata", builder.GetEdmModel());  
  6.   
  7.       }  
Explanation - 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.

In the last, 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 you run our URI first time, the database table will be created as follow. Don’t forget add some records into Employee table for demo.



Consuming OData Service

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

Let’s GO.

Create 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

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.Mvc;  
  6.   
  7. namespace WebAPIODataApp.Controllers  
  8. {  
  9.     public class HomeController : Controller  
  10.     {  
  11.         public ActionResult Index()  
  12.         {  
  13.   
  14.             return View();  
  15.         }  
  16.     }  
  17. }  
Adding View

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

Index cshtml
  1. @{  
  2.     ViewBag.Title = "Data Employees";  
  3. }  
  4.   
  5. <h2> Data Employees </h2>  
  6.   
  7. <div id="gridEmployee" style="margin:20px auto;"></div>  
  8.   
  9. @section scripts {  
  10.   
  11.   
  12.     <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/scripts/jquery-1.11.1.min.js"></script>  
  13.   
  14.     <script type="text/javascript" src="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/jqx-all.js"></script>  
  15.   
  16.     <link rel="stylesheet" type="text/css" href="http://jqwidgets.com/jquery-widgets-demo/jqwidgets/styles/jqx.base.css" />  
  17.   
  18.     <script type="text/javascript">  
  19.   
  20.     $(document).ready(function () {  
  21.   
  22.         // In this part, you need to prepare your data  
  23.         var source =  
  24.             {  
  25.                 datatype: "json",  
  26.   
  27.                 // Here you will declare all fields that must be used in the grid  
  28.                 datafields: [  
  29.   
  30.                { name: 'EmployeeID', type: 'number' },  
  31.                { name: 'FirstName', type: 'string' },  
  32.                { name: 'LastName', type: 'string' },  
  33.                { name: 'Gender', type: 'string' },  
  34.                { name: 'Designation', type: 'string' },  
  35.                { name: 'Salary', type: 'number' },  
  36.                { name: 'City', type: 'string' },  
  37.                { name: 'Country', type: 'string' }  
  38.   
  39.                 ],  
  40.                 // call the action which retrieve data employees in json format  
  41.                 url: '/odata/Employees'  
  42.             };  
  43.   
  44.         var dataAdapter = new $.jqx.dataAdapter(source);  
  45.   
  46.         // displaying data in the grid with jqxGrid  
  47.         $("#gridEmployee").jqxGrid(  
  48.             {  
  49.                 width: 800,  
  50.                 source: dataAdapter,  
  51.                 pageable: true,  
  52.                 sortable: true,  
  53.   
  54.                 columns: [  
  55.   
  56.                     { text: "Employee ID", datafield: "EmployeeID" },  
  57.                     { text: "FirstName", datafield: "FirstName" },  
  58.                     { text: "LastName", datafield: "LastName" },  
  59.                     { text: "Gender", datafield: "Gender" },  
  60.                     { text: "Designation", datafield: "Designation" },  
  61.                     { text: "Salary", datafield: "Salary" },  
  62.                     { text: "City", datafield: "City" },  
  63.                     { text: "Country", datafield: "Country" }  
  64.   
  65.                 ]  
  66.             });  
  67.     });  
  68.     </script>  
  69. }  
Output - That’s all. Please send your feedback and queries in comments box.