Library Management using ASP.Net MVC (Part 5) Eager Loading and Projection

In this part you will learn how to get the data from the related entities using eager loading. We will also use projection to get the data that we need for the frontend.

In the index page of our books, we want to see the list of books and if they are still available or not, but our existing Book model doesn’t have that functionality, but we can do that if we check the BorrowHistory table.

We need a way to join the Book and BookHistory tables using entity framework. Though we can actually create a join query using entity framework, but instead of creating a join query, entity framework give us ability to load the related entity.

The first thing we need to do is to allow the Book to load the related data from BorrowHistory. We need to add BorrowHistories property which is a collection of BorrowHistory in our Book model .

using System.Collections;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;

namespace LibraryManagement.Models
{
    public class Book
    {
        public int BookId { get; set; }

        [Required]
        public string Title { get; set; }

        [Required]
        [Display(Name = "Serial Number")]
        public string SerialNumber { get; set; }

        public string Author { get; set; }

        public string Publisher { get; set; }

        public ICollection<BorrowHistory> BorrowHistories { get; set; }
    }
}

Then now that we have the ability to access the related data in BorrowHistories, we will add another class that contains the properties that we need for displaying the books. In Models folder create a new class name BookViewModel then copy paste this code.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace LibraryManagement.Models
{
    public class BookViewModel
    {
        public int BookId { get; set; }  

        public string Title { get; set; }

        [Display(Name = "Serial Number")]
        public string SerialNumber { get; set; }

        public string Author { get; set; }

        public string Publisher { get; set; }

        public bool IsAvailable { get; set; }
    }
}

In our BooksController inside the Controllers folder. Update the Index method to look like the code below.

public ActionResult Index()
        {
            var books = db.Books.Include(h => h.BorrowHistories)
                .Select(b => new BookViewModel
                {
                    BookId = b.BookId,
                    Author = b.Author,
                    Publisher = b.Publisher,
                    SerialNumber = b.SerialNumber,
                    Title = b.Title,
                    IsAvailable = !b.BorrowHistories.Any(h => h.ReturnDate == null)
                }).ToList();
            return View(books);
        }

In the above code, we use eager loading to load the related data from BorrowHistories using the Include method. It’s basically like the join query, but entity framework makes things easier for us. Instead of writing a join query, we can just use .Include function to  load the related entities. Look how we get the value for IsAvailable, we are able to access the data from BorrowHistories and check if there is a record which have the value of ReturnDate is equal to null.

The Select method is like the projection query. The result of the join table have some properties that we don’t really need for the index page. Using the Select method we can choose the properties that we want and therefore it slim down the query.

Now let’s update our index view to use the BookViewModel class.

@model IEnumerable<LibraryManagement.Models.BookViewModel>

@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
@Html.ActionLink("Create New", "Create")
<table class="table">
<tr>
<th>
            @Html.DisplayNameFor(model => model.Title)</th>
<th>
            @Html.DisplayNameFor(model => model.SerialNumber)</th>
<th>
            @Html.DisplayNameFor(model => model.Author)</th>
<th>
            @Html.DisplayNameFor(model => model.Publisher)</th>
<th></th>
</tr>
@foreach (var item in Model)
    {
<tr>
<td>
                @Html.ActionLink(item.Title, "Details", new { id = item.BookId })</td>
<td>
                @Html.DisplayFor(modelItem => item.SerialNumber)</td>
<td>
                @Html.DisplayFor(modelItem => item.Author)</td>
<td>
                @Html.DisplayFor(modelItem => item.Publisher)</td>
<td>
                @if (item.IsAvailable)
                {
                    <span>
                        @Html.ActionLink("Assign", "Create", "BorrowHistories", new { id = item.BookId }, new { @class = "btn btn-primary" })
                    </span>
                }
                else
                {
                    <span>
                        @Html.ActionLink("Return", "Edit", "BorrowHistories", new { id = item.BookId }, new { @class = "btn btn-success" })
                    </span>
                }</td>
</tr>
}</table>

In the above code we add the links for Create and Edit of BorrowHistory. If the book is available it will link to create otherwise it will link to edit.

If you run your code now, you should have something like this.

books index page

In the next part we will update or code for borrowhistory to manage the assigning and returning the book.

Related Articles

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