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:

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.

public static class WebApiConfig
    {
        public static void Register(HttpConfiguration config)
        {
            config.Routes.MapHttpRoute(
                name: "DefaultApi",
                routeTemplate: "api/{controller}/{id}",
                defaults: new { id = RouteParameter.Optional }
            );

            var json = config.Formatters.JsonFormatter;
            json.SerializerSettings.PreserveReferencesHandling = Newtonsoft.Json.PreserveReferencesHandling.Objects;
            config.Formatters.Remove(config.Formatters.XmlFormatter);
            
            //config.EnableQuerySupport();

            config.EnableSystemDiagnosticsTracing();
        }
    }

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.

<table id="tblGenres">
                <thead>
                    <tr>
                        <th>ID</th>
                        <th>Genre</th>
                        <th>Order</th>
                    </tr>
                </thead>
                <tbody id="tbodyGenres">
                </tbody>
            </table>

When it’s time to call the Web API Get method, you are going to populate the tbodyGenres element. Now your GenresController GET method should be the following.

namespace DbContext.Controllers
{
    public class GenresController : ApiController
    {
        private MovieStoreEntities db = new MovieStoreEntities();

        // GET api/Genres
        public IEnumerable<Genre> GetGenres()
        {
            return db.Genres.AsEnumerable();
        }
//Code ommitted

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.

$(document).ready(function () {
    $(document).ready(function () {
        GetGenres();
    });
});

function GetGenres() {
    $.getJSON('api/genres',
            function (data) {
                $('#tbodyGenres').empty();
                $.each(data, function (key, val) {
                    $('<tr><td>' + val.Id + '</td>' +
                        '<td>' + val.Name + '</td>' +
                        '<td>' + val.SortOrder + '</td>' +
                        '<td><input type=button value="Delete" onclick="deleteGenre('+val.Id+')"</td>' +
                        '</tr>').appendTo('#tbodyGenres');
                });
            }
        );
}

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.

<div id="divAddGenre">
                <span id="spanAddNew">Add New:</span><br />
                <span id="lblGenreName">Name</span>
                <input type="text" id="txtNewGenreName" /><br />
                <span id="lblGenreOrder">Order</span>
                <input type="text" id="txtNewGenreOrder" />
                <input type="button" value="Add" id="btnAddGenre" onclick="addGenre()" />
            </div>

The addGenre javascript method will have to make a POST HTTP request to our POST GenresController action method which in turns, should look like this.

// POST api/Genres
        public HttpResponseMessage PostGenre(Genre genre)
        {
            if (ModelState.IsValid)
            {
                db.Genres.Add(genre);
                db.SaveChanges();

                HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, genre);
                response.Headers.Location = new Uri(Url.Link("DefaultApi", new { id = genre.Id }));
                return response;
            }
            else
            {
                return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
            }
        }

And the addGenre() javascript method..

function addGenre() {
    var genre = {
        //Id: 0,
        Name: $('#txtNewGenreName').val(),
        SortOrder: $('#txtNewGenreOrder').val()
    };
    $.ajax({
        url: 'api/genres',
        type: 'POST',
        data: JSON.stringify(genre),
        contentType: "application/json;charset=utf-8",
        success: function (data) {
            $('#txtNewGenreName').val('');
            $('#txtNewGenreOrder').val('');
            alert(data.Name + ' added!');
            GetGenres();
        },
        error: function (x, y, z) {
            alert(x + '\n' + y + '\n' + z);
        }
    });    
}

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.

<div id="divEditGenre">
                <span id="spanEditGenre">Edit Genre:</span><br />
                <span id="lblEdigGenreId">Id</span>
                <input type="text" id="txtEditGenreId" /><br />
                <span id="lblEdigGenreName">Name</span>
                <input type="text" id="txtEditGenreName" /><br />
                <span id="lblEditGenreOrder">Order</span>
                <input type="text" id="txtEditGenreOrder" />
                <input type="button" value="Update" id="btnUpdateGenre" onclick="updateGenre()" />
            </div>

The updateGenre() javascript method will have to call the PUT action method of your GenresController..

// PUT api/Genres/5
        public HttpResponseMessage PutGenre(int id, Genre genre)
        {
            if (!ModelState.IsValid)
            {
                return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
            }

            if (id != genre.Id)
            {
                return Request.CreateResponse(HttpStatusCode.BadRequest);
            }

            db.Entry(genre).State = EntityState.Modified;

            try
            {
                db.SaveChanges();
            }
            catch (DbUpdateConcurrencyException ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
            }

            return Request.CreateResponse(HttpStatusCode.OK,genre);
        }

The updateGenre() ajax call will have to pass the Genre Id in the url and the update Genre object as well.

function updateGenre() {
    var id = $('#txtEditGenreId').val();
    var newName = $('#txtEditGenreName').val();
    var newOrder = $('#txtEditGenreOrder').val();
    var genre = {
        Id : id,
        Name : newName,
        SortOrder : newOrder
    };
    $.ajax({
        url: 'api/genres/' + id,
        type: 'PUT',
        data: JSON.stringify(genre),
        contentType: "application/json;charset=utf-8",
        success: function (data) {
            $('#txtEditGenreId').val('');
            $('#txtEditGenreName').val('');
            $('#txtEditGenreOrder').val('');
            alert(data.Name + ' updated..');
            GetGenres();
        },
        error: function (x, y, z) {
            alert(x + '\n' + y + '\n' + z);
        }
    });
}

Now the delete functionality. You don’t need any html code since you have bound the Delete button to a deleteGenre(id) method. Yet, your controller’s delete action should be the following.

// DELETE api/Genres/5
        public HttpResponseMessage DeleteGenre(int id)
        {
            Genre genre = db.Genres.Find(id);
            if (genre == null)
            {
                return Request.CreateResponse(HttpStatusCode.NotFound);
            }

            db.Entry(genre).Collection(g => g.Reviews).Load();
            var reviewsToRemoved = (from r in db.Reviews
                                    where r.GenreId == id
                                    select r).ToList();
            foreach (var review in reviewsToRemoved)
                db.Reviews.Remove(review);

            db.Genres.Remove(genre);

            try
            {
                db.SaveChanges();
            }
            catch (DbUpdateConcurrencyException ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
            }

            return Request.CreateResponse(HttpStatusCode.OK, genre);
        }

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.

function deleteGenre(id) {
    $.ajax({
        url: 'api/genres/'+id,
        type: 'DELETE',
        success: function (data) {
            alert(data.Name + ' removed..');
            GetGenres();
        },
        error: function (x, y, z) {
            alert(x + '\n' + y + '\n' + z);
        }
    });
}

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:
Id
Title
Summary
Genre
Authorized
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.

namespace DbContext.Models
{
    public class ReviewDTO
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Summary { get; set; }
        public string Body { get; set; }
        public string Genre { get; set; }
        public bool Authorized { get; set; }
        public System.DateTime CreateDateTime { get; set; }
        public System.DateTime UpdateDateTime { get; set; }

    }
}

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.

public class ReviewsController : ApiController
    {
        private MovieStoreEntities db = new MovieStoreEntities();

        // GET api/Reviews
        public IEnumerable<ReviewDTO> GetReviews()
        {
            List<ReviewDTO> reviewsDTO = new List<ReviewDTO>();
            var reviews = db.Reviews.Include(r => r.Genre);
            foreach (var review in reviews)
            {
                ReviewDTO dto = new ReviewDTO
                {
                    Id = review.Id,
                    Title = review.Title,
                    Summary = review.Summary,
                    Body = review.Body,
                    Authorized = review.Authorized,
                    Genre = review.Genre.Name,
                    CreateDateTime = review.CreateDateTime,
                    UpdateDateTime = review.UpdateDateTime
                };
                reviewsDTO.Add(dto);
            }
            return reviewsDTO.AsEnumerable();
        }
//Code ommitted

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.

<table id="tblReviews">
                <thead>
                    <tr>
                        <th>Id</th>
                        <th>Title</th>
                        <th>Summary</th>
                        <th>Genre</th>
                        <th>Authorized</th>
                    </tr>
                </thead>
                <tbody id="tbodyReviews"></tbody>
            </table>

The javascript code to retrieve the Reviews should look like this.

$(document).ready(function () {
    $(document).ready(function () {
        GetReviews();
    });
});

function GetReviews() {
    $.getJSON('api/reviews',
        function (data) {
            $('#tbodyReviews').empty();
            $.each(data, function (key, val) {
                $('<tr><td>'+ val.Id + '</td>' +
                    '<td>' + val.Title + '</td>' +
                    '<td>' + val.Summary + '</td>' +
                    '<td>' + val.Genre + '</td>' +
                    '<td>' + val.Authorized + '</td>' +
                    '<td><input type=button value="Delete" onclick="deleteReview(' + val.Id + ')"</td>' +
                    '</tr>').appendTo('#tbodyReviews');
            });

        });
}

If you want to create a new Review you can use again the Transfer Object. For html all you need is the following.

<div id="divAddReview">
                <span id="spanAddReview">Add Review</span><br />
                <span id="spanReviewTitle">Title:</span>
                <input type="text" id="txtNewReviewTitle" />
                <span id="spanReviewGenre">Genre:</span>
                <input type="text" id="txtNewReviewGenre" />
                <span id="spanReviewSummary">Summary</span>
                <input type="text" id="txtNewReviewSummary" />
                <span id="spanReviewBody">Body</span>
                <input type="text" id="txtNewReviewBody" />
                <span id="spanAuthorized">Authorized</span>
                <input type="checkbox" id="chkReviewAuthorized" value="Authorized" />
                <input type="button" value="Add" onclick="addReview()" />
            </div>
        </div>

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.

// POST api/Reviews
        public HttpResponseMessage PostReview(ReviewDTO reviewDTO)
        {
            if (ModelState.IsValid)
            {
                Genre genre = db.Genres.Where(g => g.Name == reviewDTO.Genre).Single();
                Review review = new Review
                {
                    Title = reviewDTO.Title,
                    Summary = reviewDTO.Summary,
                    Body = reviewDTO.Body,
                    Authorized = reviewDTO.Authorized,
                    GenreId = genre.Id,
                    CreateDateTime = DateTime.Now,
                    UpdateDateTime = DateTime.Now
                };
                db.Reviews.Add(review);
                db.SaveChanges();

                HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, reviewDTO);
                response.Headers.Location = new Uri(Url.Link("DefaultApi", new { id = review.Id }));
                return response;
            }
            else
            {
                return Request.CreateErrorResponse(HttpStatusCode.BadRequest, ModelState);
            }
        }

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.

function addReview() {

    var review = {
        Title: $('#txtNewReviewTitle').val(),
        Genre: $('#txtNewReviewGenre').val(),
        Summary: $('#txtNewReviewSummary').val(),
        Body: $('#txtNewReviewBody').val(),
        Authorized: $('#chkReviewAuthorized').is(":checked")
    };
    $.ajax({
        url: 'api/reviews',
        type: 'POST',
        data: JSON.stringify(review),
        contentType: "application/json;charset=utf-8",
        success: function (data) {
            alert(data.Title + ' added');
            $('#txtNewReviewTitle').val('');
            $('#txtNewReviewGenre').val('');
            $('#txtNewReviewSummary').val('');
            $('#txtNewReviewBody').val('');
            GetReviews();
        },
        error: function (x, y, z) {
            alert(x + '\n' + y + '\n' + z);
        }
    });
}

When we retrieved the Reviews we also added a Delete button. Let’s see the Controller’s Delete action and the respective deleteReview() javascript method.

// DELETE api/Reviews/5
        public HttpResponseMessage DeleteReview(int id)
        {
            Review review = db.Reviews.Find(id);
            if (review == null)
            {
                return Request.CreateResponse(HttpStatusCode.NotFound);
            }

            db.Reviews.Remove(review);

            try
            {
                db.SaveChanges();
            }
            catch (DbUpdateConcurrencyException ex)
            {
                return Request.CreateErrorResponse(HttpStatusCode.NotFound, ex);
            }

            return Request.CreateResponse(HttpStatusCode.OK, review.Title);
        }

function deleteReview(id) {
    $.ajax({
        url: 'api/reviews/' + id,
        type: 'DELETE',
        success: function (data) {
            alert(data + ' deleted..');
            GetReviews();
        },
        error: function (x, y, z) {
            alert(x + '\n' + y + '\n' + z);
        }
    });
}

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.

CREATE VIEW [dbo].[vWGetReviewsByGenre]
AS
select g.Name, r.Title, r.Summary
from Genre as g
join Review as r
on g.Id = r.GenreId
GO

namespace DbContext.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class vWGetReviewsByGenre
    {
        public string Name { get; set; }
        public string Title { get; set; }
        public string Summary { get; set; }
    }
}

Let’s say we get all reviews, displaying the Name of it’s Genre, it’s Title property and the Summary as well. The html code we want is a simple table:

<table id="tblReviewsByGenre">
                <thead>
                    <tr>
                        <th>Genre</th>
                        <th>Title</th>
                        <th>Summary</th>
                    </tr>
                </thead>
                <tbody id="tbodyReviewsByGenre"></tbody>
            </table>

Add a new Web API controller named ReviewsByGenreController selecting the vWGetReviewsByGenre for the model class and the MovieEntities for the DbContext. Change the Get action method as follow:

public class ReviewsByGenreController : ApiController
    {
        private MovieStoreEntities db = new MovieStoreEntities();

        // GET api/ReviewsByGenre
        public IEnumerable<vWGetReviewsByGenre> GetvWGetReviewsByGenres()
        {
            var reviewsByGenre = from vW in db.vWGetReviewsByGenres
                                 orderby vW.Name
                                 select vW;
            return reviewsByGenre.AsEnumerable();
        }
//code ommitted

As you saw we return the results ordered by Genre’s name. The javascript code you need to populate the table is the following.

$(document).ready(function () {
    GetViewsByGenre();
});

function GetViewsByGenre() {
    $.getJSON('api/reviewsbygenre',
        function (data) {
            $('#tbodyReviewsByGenre').empty();
            $.each(data, function (key, val) {
                $('<tr><td>' + val.Name + '</td>' +
                    '<td>' + val.Title + '</td>' +
                    '<td>' + val.Summary + '</td>' +
                    '</tr>').appendTo('#tbodyReviewsByGenre');
            });
        });
}

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.

<h1>Search a Review by Title</h1>
            <span id="spanSearchReviewTitle">Title</span>
            <input type="text" id="txtReviewTitle" />
            <input type="button" id="btnSearchReviewByTitle" onclick="searchReviewByTitle()"
                value="Search" /><br />
            <div id="divSearchReviewsByTitle">
            </div>

In order to make the “txtReviewTitle” input text autocomplete, you need to add reference to the jquery-ui reference..

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/smoothness/jquery-ui.css" />
<script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

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.

'api/reviews?title=titleValue'

Add the following action to the ReviewsController.

[HttpGet]
        public IEnumerable<ReviewDTO> SearchReviewByTitle(string title)
        {
            List<ReviewDTO> reviewsDTO = new List<ReviewDTO>();
            var reviews = from r in db.Reviews.Include(r => r.Genre)
                          where r.Title.Contains(title)
                          select new ReviewDTO
                          {
                              Title = r.Title,
                              Summary = r.Summary,
                              Genre = r.Genre.Name
                          };
            foreach (var review in reviews)
            {
                reviewsDTO.Add(review);
            }
            return reviewsDTO.AsEnumerable();
        }

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.

function searchReviewByTitle(title) {
    //var title = $('#txtReviewTitle').val();
    // uncomment the above if you want to search for what you have entered in input text
    $.getJSON('api/reviews?title=' + title,
        function (data) {
            $('#divSearchReviewsByTitle').empty();
            $.each(data, function (key, val) {
                $('<span style="color:crimson">Title: </span><span>' + val.Title + '</<span><br/>' +
                    '<span style="color:crimson">Summary: </span><span>' + val.Summary + '</span><br/>' +
                    '<span style="color:crimson">Genre</span><span>' + val.Genre + '</span><br/>' +
                    '<div style="border-bottom:1px solid white"></div>' +
                    '<br/>').appendTo('#divSearchReviewsByTitle');
            });
        });
}

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.

Categories: ADO.NET, ASP.NET

Tags: ADO.NET Entity Framework, Live Search, Web API