OData Using C#, MVC, WebAPI, Entity Framework, Dependency Injection (DI)/Inversion of Control (IoC) and Kendo UI

Queryable Repository

With Entity Framework as our DAL base, we are accessing it through a Unit of Work / generic repository that that is queryable.  It was necessary to create this in order to pass along the OData query string parameters through to Entity Framework, in order to retrieve an optimized data set, rather than an inflated one and filtering later.

You can review some high-level notes and download the project from here: https://genericqueryablerepository.codeplex.com/


Front-End

Although the purpose of this article not to dive into the Kendo UI controls, you can install the Kendo UI Web libraries through a Nuget package: http://www.nuget.org/packages/KendoUIWeb/ – however if you need the MVC extensions, you need to sign up for a trial version to get the DLL.

UserGroups.cshtml:


@{
 Layout = null;
}

<!DOCTYPE html>

<html>
<head>
 <meta name="viewport" content="width=device-width" />
 <title>UserGroups</title>


 <script src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

<!-- Kendo UI Styles -->
 <link href="~/Content/kendo/kendo.common.min.css" rel="stylesheet" />
 <link href="~/Content/kendo/kendo.default.min.css" rel="stylesheet" />

 <link href="~/Content/Site.css" rel="stylesheet" />

<script src="~/Scripts/kendo/kendo.web.min.js"></script>
 <script src="~/Scripts/kendo/kendo.aspnetmvc.min.js"></script>
 <script src="~/Scripts/kendo/kendo.data.odata.min.js"></script>


 <script src="~/Scripts/odataUserGroupsGrid.js"></script>

</head>
<body>
 <div>
 User Groups:
 <div id="grid">

 <!-- Grid row details template -->
 <script type="text/x-kendo-template" id="userDetailsTemplate">
 Group Users:

@*In odataUserGroupsGrid.js, we append a new grid to the details
 div of the current grid, which appears here*@

</script>
 </div>

</div>
</body>
</html>

Next, we wire up our data sources and UI elements through JS (you can also do this through the MVC Razor extensions).

odataUserGroupsGrid.js:


$(function () {
 var groupDataSource = new kendo.data.HierarchicalDataSource({
 type: "odata",
 transport: {
 read: {
 // See http://www.odata.org/documentation/odata-v2-documentation/uri-conventions/ for OData URI conventions
 // OData: ~/api/Users?$inlinecount=allpages&top=2
 // OData: ~/api/Users?$inlinecount=allpages - includes odata.count
 // OData: inline filtering: ~/api/Users?$filter=USERNAME eq 'asgro'
 // to include hierarchical data, use the OData /api/UserGroups?$expand=USER
 // To reduce the payload sice, the query ~/api/UserGroups will
 // only include the USERGROUP entities, and not any navigation property content
 // url: "/api/UserGroups?$expand=USERS",
 //
 // For large data sets: http://www.asp.net/web-api/overview/
 // odata-support-in-aspnet-web-api/supporting-odata-query-options#examples
 // - retrieve the odata.nextLink parameter
 url: "/api/UserGroups?$orderby=GROUPNAME",
 dataType: "json"
 // the default result type is JSONP, but WebAPI does not support JSONP
 },
 parameterMap: function (options, type) {
 // this is optional - if we need to remove any
 // parameters (due to partial OData support in WebAPI
 var parameterMap = kendo.data.transports.odata.parameterMap(options);

//delete parameterMap.$inlinecount; // remove inlinecount parameter
 //delete parameterMap.$format; // remove format parameter

return parameterMap;
 }
 },
 schema: {
 data: function (data) {
 return data.value;
 }
 ,
 total: function (data) {
 console.log("count: " + data["odata.count"]);
 return data["odata.count"];
 },
 model: {
 //hasChildren: "hasChildren",
 id: "ID",
 // if not included, clicking edit/cancel will remove the row

fields: {
 ID: { type: "string" },
 NETWORKID: { type: "string" },
 GROUPNAME: { editable: true, type: "string" },
 DESCRIPTION: { type: "string" },
 DATECREATED: { type: "date" },
 DATEMODIFIED: { type: "date" },
 //ROLESSTRING: { type: "string" },
 SUBSCRIPTIONSTRING: { type: "string" }
 }
 }
 },
 error: function (e) {
 var message = e.xhr.responseJSON["odata.error"].message.value;
 var innerMessage = e.xhr.responseJSON["odata.error"].innererror.message;
 alert(message + "\n\n" + innerMessage);
 },
 pageSize: 10,
 serverPaging: true,
 serverFiltering: true,
 serverSorting: true
 });


 function userDataSource(groupData) {
 console.log("group data");
 console.log(groupData);
 var userDS = new kendo.data.DataSource({
 type: "odata",
 transport: {
 read: {
 // since we have enabled server paging and server filtering, the paging extension will add the requred parameters:
 // /api/Users?$filter=USERGROUPS/any(usergroup:%20usergroup/ID%20eq%20'a3a7ba
 // 66e3524703bc42703c5176097e')&%24inlinecount=allpages&%24format=json&%24top=10&%24skip=10
 url: "/api/Users?$filter=USERGROUPS/any(usergroup: usergroup/ID eq '" +
 groupData.ID + "')", // only need to expand users for the selected group
 //url: "/api/Users",
 dataType: "json",
 // the default result type is JSONP, but WebAPI does not support JSONP
 },
 update: {
 url: function (data) {
 // TODO: write UpdateEntity controller method
 return "/api/Users(" + groupData.ID + ")";
 },
 dataType: "json"
 },
 destroy: {
 url: function (data) {
 // TODO: write Delete controller method
 return "/api/Users(" + groupData.ID + ")";
 },
 dataType: "json"
 },
 parameterMap: function (options, type) {
 // this is optional - if we need to remove any parameters (due to partial OData support in WebAPI
 var parameterMap = kendo.data.transports.odata.parameterMap(options);
 return parameterMap;
 }
 },
 schema: {
 data: function (data) {
 console.log("USERS");
 console.log(data.value);
 return data.value;
 }
 ,
 total: function (data) {
 console.log("user count: " + data["odata.count"]);
 return data["odata.count"];
 },
 model: {
 fields: {
 ITEMID: { type: "string" },
 USERNAME: { type: "string" },
 FIRSTNAME: { type: "string" },
 LASTNAME: { type: "string" },
 EMAIL: { type: "string" }
 }
 }
 },
 error : function(e) {
 var message = e.xhr.responseJSON["odata.error"].message.value;
 var innerMessage = e.xhr.responseJSON["odata.error"].innererror.message;
 alert(message + "\n\n" + innerMessage);
 },
 pageSize: 10,
 //filter: { field: "odata.value.USERGROUPS.ID", operator: "eq",
 // value: groupData.ID }, // filter where the the user.group nav prop ID = group id
 serverPaging: true,
 serverFiltering: true,
 serverSorting: true
 });

return userDS;
 }

$("#grid").kendoGrid({
 dataSource: groupDataSource,
 navigatable: true,
 filterable: true,
 sortable: true,
 pageable: true,
 batch: true,
 toolbar: ["create", "save", "cancel"],
 detailTemplate: kendo.template($("#userDetailsTemplate").html()),
 detailInit: detailInit,
 dataBound: function () {
 this.expandRow(this.tbody.find("tr.k-master-row").first());
 },
 columns: [
 { editable: true, field: "GROUPNAME", title: "Group Name" },
 { field: "NETWORKID", title: "Network ID" },
 { field: "DATECREATED", title: "Date Created", format: "{0:d}" },
 { field: "DATEMODIFIED", title: "Date Modified", format: "{0:d}" },
 { command: ["destroy"], title: "&nbsp;", width: "200px" }
 ],
 editable: true
 });

function detailInit(e) {
 $("<div/>").appendTo(e.detailCell).kendoGrid({
 dataSource: userDataSource(e.data),
 scrollable: false,
 sortable: true,
 pageable: true,
 columns: [
 { field: "USERNAME", title: "User Name", width: "130px" },
 { field: "EMAIL", title: "Email", width: "200px" },
 { field: "NETWORKID", title: "Network ID" }
 ]
 });


 //var detailRow = e.detailRow;

//detailRow.find(".tabstrip").kendoTabStrip({
 // animation: {
 // open: { effects: "fadeIn" }
 // }
 //});
 }

// if we include an autocomplete on the same page
 $("#userSearch").kendoAutoComplete({
 dataSource: groupDataSource,
 dataTextField: "USERNAME",
 filter: "startswith",
 // filter: "startswith" is default; "contains", "endswith"

minLength: 1
 });
});

I have added several comments in order to make the code more understandable.

A new grid is appended to the main grid’s detail row, which contains a list of users that correspond to a specific group.

Earlier I mentioned that there exists a relationship between USERS and USERGROUPS. You can see this relationship using Fiddler to display the JSON data:

  •  ~/api/UserGroups?$expand=USERS
  • ~/api/Users?$expand=USERGROUPS

You will notice in our userDataSource that we do not need to use the $expand parameter when filtering for all users within a specific group:

  •   url: “/api/Users?$filter=USERGROUPS/any(usergroup: usergroup/ID eq ‘” + groupData.ID + “‘)”

You will also notice that we have both serverPaging and serverFiltering set to true. This passes along the OData query string parameters through to the server, that is, adding top= and skip=. Therefore, when expending a specific row, the details event fires and sends the group ID along to retrieve the associated users:

  • ~/api/Users?$filter=USERGROUPS/any(usergroup:%20usergroup/ID%20eq%20’Group1′)&%24inlinecount=allpages&%24format=json&%24top=10&%24skip=10

The resulting grid UI displays roughly as follows:

kendoui grid with odata

You will notice that the second expanded group has a total of 71 users. When clicking on a pagination item, the control executes two database queries:

  1. Get total count of users
  2. Get requested users – in this case, top 10, and apply the skip

The total is important for paging, and is retrieved as “odata.count“:


total: function (data) {
 console.log("count: " + data["odata.count"]);
 return data["odata.count"];
},

You can also play around with this directly without using any controls by simply access the data access API URLs directly and observing the output either in your browser or in Fiddler:

  •  /api/Users?$filter=USERGROUPS/any(usergroup:%20usergroup/ID%20eq%20’GROUP1′)&%24inlinecount=allpages&%24format=json&%24top=10&%24skip=10

Below, is a screenshot of the raw data returned from the query. You can see the accessible OData properties along with an array of users contained in value:

kendoui odata fiddler

SQL Queries

With the inclusion of $inlinecount=allpages OData will return the total number of records for a given data set.  This results in two queries being executed by SQL Server:

  • Get the total number of records
SELECT
[GroupBy1].[A1] AS [C1]
FROM ( SELECT
COUNT_BIG(1) AS [A1]
FROM [dbo].[USERS] AS [Extent1]
)  AS [GroupBy1]
go
  • Get the requested data with filtering applied (query not displayed)

The important thing to note here is that the total number of records is returned with the data set, as to allow for efficient client-side paging.

Omitting Dependency Injection and the Generic Repository

Once you have your Entity Framework Database First project setup, and you don’t want to use the generic repository and dependency injection, the following controller action method will do the trick:


&nbsp;

/// <summary>
 /// Example of using the EF db context directly
 ///
 /// Since we want manual control over applying query options it is not recommended to use the QueryableAttribute:
 /// [Queryable(AllowedQueryOptions = AllowedQueryOptions.All, PageSize = 10)]
 ///
 /// This will apply the query a second time - to the output - not what we want.
 ///
 /// Use this option if not applying the query options - using options.ApplyTo(...)
 /// </summary>
 /// <param name="options"></param>
 /// <returns></returns>
 public IEnumerable<USER> Get(ODataQueryOptions<USER> options)
 {
 var dbContext = new ATMS.DAL.AtmsContext();
 var ret = options.ApplyTo(dbContext.USERS).Cast<USER>().ToArray(); // applies filtering options
 //var ret = dbContext.USERS.ToArray(); // returns all records, filtered results to client
 //var ret = options.ApplyTo(dbContext.USERS).Cast<USER>() as IQueryable<USER>;

return ret;
 }

 

Points of Interest

With large data sets, you want to return filtered data from your SQL Server. In your OData controller action, make sure you don’t combine your client-side querying options with your server side options – pick one, as noted above.

You may have to call .ToList() instead of .ToArray() in the controller actions, if you are returning more complex objects.

Conclusion

Hopefully this will assist some of you in either getting started with OData, or answer some basic questions, most of which you will most-likely find the answers to in the code samples.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s