Related entities and live search with Web API and Entity Framework

Retrieving and manipulating Entities with no related data using Web API and Entity Framework is quite easy and most times, by the time you create a Web API controller using a specific Model class and a DbContext instance you won’t have to alter the the code created for you. If you aren’t aware of what DbContext is there are a few posts on this blog that will help you. Fetching and manipulating related entities is a little bit different. You need to know exactly how your Model classes are structured which means you need to be aware of any relationships exist in database level and how Entity Framework structured your model classes as well. In this post we are going to show how to:

Retrieve related Data using Web API and DbContext

Add, Update and Delete Parent-Child (related) Entities

Call and display a database View through Web API

Live Search using the Web API

For this project we are going to use a database named MovieStore which in turns has two related tables , Genre and Review. There is a One-To-Many relationship between those tables where Genre is the parent table while the Review is the child one. Cause this project has a lot of code, each time I explain something I will give all the code you need to understand the current concept rather than writing all the code step by step. At the end of this post, as always you can find link to download the project and an SQL script inside the App_Data folder to create and populate the MovieStore database. If you do so, make sure you alter your Web.config file respectively, to point your SQL Server instance. Let’s start.
I have created a new ASP.NET MVC 4 project using the Web API template. The first thing you need to do is to create the Model classes using the Entity Framework. After creating the MovieStore database add an ADO.NET Entity Framework edmx model item, named MovieEntities. Add to this model the two related tables, Genre and Review and a View named vWGetReviewsByGenre. We will use that View later to display a JOIN from the database.
Now that we have the Model classes we can start creating the Web API controllers. Before doing so, paste the following code in the Register function of the WebApiConfig class.

You need this addition cause the default JSON serializer cannot serialize correctly any cycle references among the entities. There is a cycle reference between the Genre and the Review classes in your model, and unless you don’t make this configuration you won’t be able to get the right JSON results.
For start we will see how to Get, Update and Delete Genre Entities so go and create a Web API controller named GenresController using the Genre class as the Model and the MovieEntities as the DbContext class. We will use the default Index.cshtml page of the Home controller to display all our data so go and delete the current one and create a new one, right clicking inside the Index action of the Home controller. This time make sure you choose not to use either a Model class or a layout. Just a simple html page. You will also need the jquery library for making Ajax calls to your Web API so add a reference inside the head element of the page. Suppose you want to fill a table with the all the Movie genres now. Also you want to add a Delete button in each row so later you can delete a genre if you want. Your html code could be the following.

That should be enough to get your genre entities from the database. Now you need to call your Web API Get method from your Index.cshtml page. If you download the project at the end of this post, you will see that I have a different javascript file for manipulating Genres, Reviews and Search Results. That’s OK though, now we are going to paste only the code you need to see. To get the genres and populated the respective table write the following javascript code.

We used the $.getJSON jquery ajax method to get our genres in JSON. In function(data) “data” represents the JSON array result where later inside the $.each(data, function (key, val) “val” represents a single genre record. That way we can populate our table.
Before adding the Delete functionality let’s create the Adding one. Your html code for adding a Genre record could look like this.

We used the $.ajax jquery method and on the success method we call the GetGenres() method again to populate the genres table on real time. Now let’s create the Editing functionality where you will be able to update both the Name and the Sort Order of a genre. Your html code could look like this.

This is not the default code added for you when created the API controller. We have added code to make sure that the related genre’s Reviews are marked as DELETED before calling the SaveChanges(). Otherwise you will get a referential integrity error telling that you cannot delete a parent record when there are child records related to it. The javascript code for the deleteGenre(id) method is the following.

Why don’t we see it all in action now (if you cannot see the image, either switch browser or just click it to open in new tab).
Now let’s fetch all the Reviews records from database. For each Review I want to display the following properties:IdTitleSummaryGenreAuthorized
Notice though, that the Review Model class doesn’t have a Genre string property to display. And this property that represents the Genre’s model class “Name” property exist only in the Genre class. So how can we solve it? The best solution is to use Transfer Object. You need to understand that you don’t have to pass a real Model class to the View but instead you can pass anything you want! Notice though that you have to create the Transfer Object in a way that you can use it in case you want to pass specific values from the View to the Controller. For example you will need the Review’s Id in the transfer object for updating a review record.. Add a new class named ReviewDTO which will represent the Data Transfer Object for Reviews.

Now add a Wep API controller named ReviewsController selecting the Review Model class and the MovieEntities for the DbContext. Change the GET action method for retrieving Reviews in the way we want, as follow.

As you can see we pass to the view a list of Transfer Objects rather than a list of Review Model list. Also the string property Genre of the Transfer Object has been populated with the Genre’s “Name” property. Let’s retrieve the reviews now. Add the following html code in the Index page.

I highlighted the 6th line cause it’s the most important. You need to assign a value for the foreign key property GenreId property when adding a Review record in the database. So we will pass through the Transfer Object the Genre’s name and then, inside the POST action of the Reviews controller, we will search and assign the respective GenreId. Change the POST action method of the Reviews controller to accept ReviewDTO object as follow.

Take a look at the highlighted lines 6, 13. We retrieved the Genre record by Genre’s name and then assign that genre’s id to the Review.GenreId property. The javascript code for the addReview() method is the following.

Now let’s try something else. Let’s display the results of a database View. If you run the SQL script existed in the App_Data folder, you will find a View named vWGetReviewsByGenre. This View exist in your model as well with the same name.

The result..
It’s time to see the most interesting thing now. How to implement live search with Web API. Assume that we want to search Reviews by Title. When we start typing in the textbox we want all Reviews with their Title property containing the Textbox’s text, populate a listbox. Then, when we select a review, we want it’s data to be displayed automatically. Add the following html code to support that functionality.

We want to retrieve Review details by title, so we will use again the Transfer Object we created before and we will use the ReviewsController as well. This time we want a GET method that accepts as a parameter a string title property. We need to annotate this action with the [HttpGet] attribute. This action will be available through the following URL.

Next we need to use the jquery-ui library to make the txtReviewTitle autocomplete.

<script type="text/javascript">
var titles = new Array();
$.getJSON('api/reviews',
function (data) {
$.each(data, function (key, val) {
titles.push(val.Title);
});
});
$("#txtReviewTitle").autocomplete({
source: titles,
minLength: 2,
select: function (event, ui) {
searchReviewByTitle(ui.item.value);
// the above will start a search for the title you have selected
// if you want you can pass an empty parameter and search for what
// you have enter in input text - you will have to change the following
// function respectively
}
});
</script>

So here’s what we do. We populate the titles javascript array with the Review names calling the default GET action method of the ReviewsController. We use the titles array to fill the autocomplete input text when two or more characters are pressed. When the user makes a selection we call the searchReviewByTitle() method which in turns will call the previous action method we wrote.

The result is amazing:
That’s it, we saw many interesting things in this post including how to use Web API for live search. I hope you enjoyed the post. Download the project we created from here and make sure you follow this blog to get notified for new posts.

The purpose of this blog is to broaden my education, promote experimentation and enhance my professional development. Albert Einstein once said that “If you can’t explain it simply, you don’t understand it well enough” and I strongly believe him!