OData – Displaying Hierarchical Data with Kendo UI, MVC and a Queryable Entity Framework Repository
It is recommended that you are proficient with using .Net, dependency injection, MVC, Fiddler, and SQL Server Profiler – ie. good debugging skills, and can use the Kendo UI Grid extension for data display purposes. Otherwise, you can access the OData URL paths for viewing the raw data.
Introduction
As a part of this solution I created a generic queryable repository that I accessed Entity Framework Database First Entities through a repository and unit of work pattern: DOWNLOAD.
To modify your *.tt template to output a compatible context class, see the following article: Modifying Entity Framework for use by a Repository and Unit of Work Pattern
The starting point for this article was a requirement for a new UI POC, for which Kendo UI (http://www.kendoui.com/) was chosen. An existing SQL database was used as a data source and a RESTful OData (http://www.odata.org/) API endpoint was created for data access.
Explaining how to setup Kendo UI and what OData is are out of scope for this article.
The purpose of this article is to identify a clean-cut way of using OData and hierarchical data. One of the most commonly asked questions is, “How do I only return the rows needed from my database server, and not 100,000 records then filter?” (or variations there-of). This article will identify how to accomplish this.
We are using OData because it represents an efficient way of retrieving data, as requested directly from the client, without having to write specific associated controller action methods for things like paging and filtering.
For reads, the client sends query parameters that are received by a single Get() method. The query options are then handled on the server, and can be passed directly through to SQL Server (in this case, through Entity Framework).
All CRUD operations are quite simple through OData as well, but are out of scope of this article (other than Reads).
Note: If you don’t want to use Dependency Injection, and the generic repository included – simply use Entity Framework directly, I include a controller action method at the end of this article that allows you to do this.
Background
The basis for any data-driven application is the DAL, and it’s important to know how the data is accessed and make sure it is done efficiently, as such, I have worked on several projects including rewriting a popular ORM to make use of SQL Server’s TVP’s (http://subsonic23tvp.codeplex.com/).
I am always cautious when controls automatically “do things”, especially when it comes to data access.
I initially setup a Kendo UI Grid with a details grid containing users. Everything looked fine – I expanded a group, and was able to navigate through a list of associated users.
In using the browser console and SQL Server Profiler, I noticed that all users were being returned from SQL Server, then filtering occurring before it reached the client.
In another attempt, I noticed that all records were returned to the client, an the control was paging through the entire data set.
What I wanted to see was that SQL Server was only returning, say 10 records at a time and applying the filtering options.
For this article I am using and making reference to two tables USERS and USERGROUPS. Use this article as a reference for your own data sets.
I typically put a lot of comments in my code to help others understand exactly what’s going on. These comments usually include some alternate examples and helpful URLs for additional background details.
Not everything is described in the text of this article, and most-likely some of your questions will be answered by actually reading through the code itself – highly recommend it.
Solution Setup
The ATMS.DAL project contains the auto-generated classes through Entity Framework Database First.
Here is what the basic solution structure looks like:
Next, we see the differences between our two sets of control classes – we have two sets:
We will be using Entity Framework Database First to generate our database entities. However, for testability, we will first use a generic repository pattern and dependency injection / IoC to access our entities through EF.
Explaining how this works and why it is important is out of scope of this article, but I have included the Repository project as a part of the article’s code. The Repository uses generics and as such is not tightly-coupled on either end. Use freely as needed.
The repository project can be added to any .Net solution and referenced in your associated projects.
Simple Injector (http://simpleinjector.codeplex.com/) was used for our DI container, which can be added through a Nuget package to your solution.
What's In Your Lunch 