jQuery DataTables With ASP.NET Web API 2 OData Service

In this article, we will implement jQuery DataTables with ASP.NET Web API 2 OData Services for creating table listings and effective searching, sorting and pagination.
Used Environment: VS 2013 Update 1, Entity Framework 6, ASP.NET MVC 5, jQuery DataTables 1.9.4, jQuery DataTables OData connector

Create the Project:

1. Start Visual Studio > File > New Project > ASP.NET Web Application > Select Template “Empty” and select “Web API” in “Add folders and core references for” option > OK

2. In Solution Explorer, right-click the Models folder > Add > Class > Enter Name “Person.cs”

3. In Person.cs file, add the following class definition:

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22

namespace oDataSample.Models {     [Table("Person")]     public class Person     {           public int ID { get; set; }           [Required]         [MaxLength(50)]         public string Name { get; set; }                   [Required]         [MaxLength(20)]         public string Phone { get; set; }                   [Required]         [MaxLength(50)]         public string Email { get; set; }       } }

4. Build the solution/Project.

Add an OData Controller:

5. In Solution Explorer, right-click the the Controllers folder > Add > Controller. In the Add Scaffold dialog, select “Web API 2 OData Controller with actions, using Entity Framework” > Add

name the controller “PersonsController“. Select the “Use async controller actions” checkbox. In the Model drop-down list, select the Person class. (Make sure you built the project in VS as mentioned in step 4).

Click the New data context… button, enter name PersonServiceContext > Add.

The scaffolding adds two code files to the project:

PersonsController.cs defines the Web API controller that implements the OData endpoint.
PersonServiceContext.cs provides methods to query the underlying database, using Entity Framework.

Add the EDM and Route:

6. Open Solution Explorer > App_Start > WebApiConfig.cs

Replace the code with the following:

?

01 02 03 04 05 06 07 08 09 10

public static class WebApiConfig    {               public static void Register(HttpConfiguration config)        {            ODataConventionModelBuilder builder = new ODataConventionModelBuilder();            builder.EntitySet<Person>("Persons");            config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());        }    }

The EDM is used to create the metadata document and define the URIs for the service. It is an abstract model of the data.

ODataConventionModelBuilder: It creates an EDM by using a set of default naming conventions.
ODataModelBuilder: to create the EDM by adding properties, keys, and navigation properties explicitly.

In above code, ODataConventionModelBuilder is used for simplicity.

An endpoint can have multiple entity sets. Call EntitySet for each entity set, and then define a corresponding controller. Here I defined Persons entity set.

?

1	builder.EntitySet<Person>("Persons");

To define route

?

1	config.Routes.MapODataRoute("odata", "odata", builder.GetEdmModel());

The first parameter is a friendly name for the route.
The second parameter is the URI prefix for the endpoint. In our case, The URI for Person entity set is “http://hostname/odata/Persons” When you run it first time, the database is created. Add some data in database for testing.

Add OData Client:

7. To consume OData service, we will add MVC 5 emtpy controller in same app.
In solution explorer > Right click on Controller folder > Add > Controller > Select “MVC 5 Controller -Empty” > add > name “HomeController” > Add

You might need to add following in Application_Start method of Global.asax if not added automatically

?

1 2 3 4

AreaRegistration.RegisterAllAreas();  RouteConfig.RegisterRoutes(RouteTable.Routes);  FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);  BundleConfig.RegisterBundles(BundleTable.Bundles);

jQuery DataTables:

8. Install jquery datatables by running the following command in the Package Manager Console (Tools > Library Package Manager > Package Manager Console in Visual Studio)

Install-Package jquery.datatables

9. Download jQuery DataTables OData connector, extract file and copy “jquery.dataTables.odata.js” in “Scripts/ Datatables-*/media/js” folder.

10. Open HomeController > Right click on Index method > Add View > Remove “Use a layout page” selection > Add

11. Add following CSS references in head tag of View:

?

1 2 3 4 5

@Styles.Render("~/Content/css")  @Scripts.Render("~/bundles/modernizr")  <link href="~/Content/bootstrap.css" rel="stylesheet" />  <link href="~/Content/DataTables-1.9.4/media/css/demo_page.css" rel="stylesheet" />  <link href="~/Content/DataTables-1.9.4/media/css/demo_table.css" rel="stylesheet" />

12. Add following javascript references in head tag:

?

1 2 3

<script src="~/Scripts/DataTables-1.9.4/media/js/jquery.js"></script> <script src="~/Scripts/DataTables-1.9.4/media/js/jquery.dataTables.min.js"></script> <script src="~/Scripts/DataTables-1.9.4/media/js/jquery.dataTables.odata.js"></script>

13. Add following in body tag

?

01 02 03 04 05 06 07 08 09 10 11 12

<div style="max-width:750px">        <table cellpadding="0" cellspacing="0" border="0" class="display" id="personInfo">            <thead>                <tr>                    <th>Name</th>                    <th>Phone</th>                    <th>Email</th>                                   </tr>            </thead>            <tbody></tbody>        </table>    </div>

14. Add following code to implement jQuery DataTable

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18

$(function () {            $('#personInfo').dataTable({                  "sPaginationType": "full_numbers",                "aLengthMenu": [[2, 5, 10, -1], ["Two", "Five", "Ten", "All"]],                "bProcessing": true,                "bServerSide": true,                "sAjaxSource": "/odata/Persons",                "aoColumns": [                                { "mData": "Name" },                                { "mData": "Phone" },                                { "mData": "Email" }                ],                "fnServerData": fnServerOData,                "iODataVersion": 3,                "bUseODataViaJSONP": false // set to true if using cross-domain requests            });        });

If you run the app and open page /home/index, you will get javascript error:

The query specified in the URI is not valid. The value for OData query ‘$filter’ cannot be empty.

To fix this error, open “Scripts/Datatables-*/media/js/jquery.dataTables.odata.js” file and replace the following line

?

1	data.$filter = asFilters.join(" or ");

with

?

1 2 3 4

//Added if condition to fix blank filter issue       if (asFilters.length > 0) {           data.$filter = asFilters.join(" or ");       }

Now running page, you will get following error:

The query parameter ‘$callback’ is not supported.

The query specified in the URI is not valid. Query option ‘Format’ is not allowed. To allow it, set the ‘AllowedQueryOptions’ property on QueryableAttribute or QueryValidationSettings

To fix these errors, replace following line in jquery.dataTables.odata.js

?

1 2 3 4

var data = {         "$format": "json",        "$callback": "odatatable_" + (oSettings.oFeatures.bServerSide?oParams.sEcho:("load_" + Math.floor((Math.random()*1000)+1)  ))    };

with

?

1 2 3 4

var data = {    //  "$format": "json",    // "$callback": "odatatable_" + (oSettings.oFeatures.bServerSide?oParams.sEcho:("load_" + Math.floor((Math.random()*1000)+1)  )) };

I commented to prevent $format and $callback options in URI. Now you will get data displayed in the grid:

If you change page size entries then $top will be updated. On changing page, $skip will be changed. On searching, $filter will be changed accordingly. If you click on any column header then $orderby parameter will be changed.