C# , MVC and querying db help required.

[Deleted member 66701]

Hi All.

I've been learning C# and MVC over the weekend and I've hit a stumbling block. I want to query my DB and return a list of books. I've nearly got it working, i.e.:-

However, as you can see the Author isn't returned.

My DB is set up as follows:-

The DBVontroller.cs class is as follows:-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Book_Database.Models;

namespace Book_Database.Controllers
{
    public class DBController : Controller
    {

        BookDBEntities BookDB = new BookDBEntities();

        //
        // GET: /DB/Title
        public ActionResult Title(
            )
        {
            var books = BookDB.Books.ToList();
            return View(books);
        }
      }
}

My Model class is as follows:-

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

namespace Book_Database.Models
{
    public class Book
    {
        public int      BookId      { get; set; }
        public int      GenreId     { get; set; }
        public int      AuthorId    { get; set; }
        public string   Title       { get; set; }
        public decimal  Price       { get; set; }
        public string   BookArtUrl  { get; set; }
        public int      Year        { get; set; }
        public Genre    Genre       { get; set; }
        public Author   Author      { get; set; }
    }
}

and the view code is:-

@model IEnumerable<Book_Database.Models.Book>

@{
    ViewBag.Title = "Browse";
}

<div class="col-sm-9 col-sm-offset-3 col-md-10 col-md-offset-2 main">
    <h1 class="page-header">Dashboard</h1>

    <div class="row placeholders">
        <div class="col-xs-6 col-sm-3 placeholder">
            <a href="Title"><img src="~/images/dashboard_blue.png" class="img-responsive" alt="Generic placeholder thumbnail"></a>
            <h4>@Model.Count()</h4>
            <span class="text-muted">Books</span>
        </div>
        <div class="col-xs-6 col-sm-3 placeholder">
            <a href="Author"><img src="~/images/dashboard_turquoise.png" class="img-responsive" alt="Generic placeholder thumbnail"></a>
            <h4>29</h4>
            <span class="text-muted">Authors</span>
        </div>
        <div class="col-xs-6 col-sm-3 placeholder">
            <a href="Genre"><img src="~/images/dashboard_blue.png" class="img-responsive" alt="Generic placeholder thumbnail"></a>
            <h4>16</h4>
            <span class="text-muted">Genres</span>
        </div>
        <div class="col-xs-6 col-sm-3 placeholder">
            <a href="#"><img src="~/images/dashboard_turquoise.png" class="img-responsive" alt="Generic placeholder thumbnail"></a>
            <h4>14</h4>
            <span class="text-muted">Publishers</span>
        </div>
    </div>

    <h2 class="sub-header">Results by Title</h2>
    <div class="table-responsive">
        <table class="table table-striped">
            <thead>
                <tr>
                    <th>#</th>
                    <th>Title</th>
                    <th>Author</th>
                    <th>Price</th>
                    <th>Year</th>
                    <th>Publisher</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var book in Model)                
                {
                    <tr>
                        <td>@book.BookId</td>
                        <td>@Html.ActionLink(book.Title,"Details", new { id = book.BookId })</td>
                        <td>[B][U]WANT AUTHOR HERE[/U][/B]</td>
                        <td>@book.Price</td>
                        <td>@book.Year</td>
                        <td></td>
                    </tr>
                }

            </tbody>
        </table>
    </div>
</div>

So how to I return the author value that I "think" I have in my model?


As I said, I've only started a couple of days ago, so please be gentle

 

[Craig321]

so @book.Author.Name is blank?

 

[Deleted member 66701]

so @book.Author.Name is blank?

I tried that but I get the following on runtime:-

 

[dazzerd]

now not having a web background I may be far off the mark here; but in my head your author name is defined in another table and linked to it via FK on author table in your main table.

I think you are making the assumption that your application knows this. In my head you need to be either exposing a view or stored procedure in your db, to which you then call and populate your class or you use EF and do some linq to sql.

 

[john_s]

Try updating your Book class as below to link the Book class to the Author class:

public class Book
    {
        public int      BookId      { get; set; }
        public int      GenreId     { get; set; }
        public int      AuthorId    { get; set; }
        public string   Title       { get; set; }
        public decimal  Price       { get; set; }
        public string   BookArtUrl  { get; set; }
        public int      Year        { get; set; }
        public Genre    Genre       { get; set; }
        public virtual Author   Author      { get; set; }
    }

 

[Deleted member 66701]

Try updating your Book class as below to link the Book class to the Author class:

public class Book
    {
        public int      BookId      { get; set; }
        public int      GenreId     { get; set; }
        public int      AuthorId    { get; set; }
        public string   Title       { get; set; }
        public decimal  Price       { get; set; }
        public string   BookArtUrl  { get; set; }
        public int      Year        { get; set; }
        public Genre    Genre       { get; set; }
        public virtual Author   Author      { get; set; }
    }

You absolute beauty! Works a treat.

So what's the significance of the "virtual" bit then?

 

[Craig321]

Have a read regarding lazy loading etc. https://msdn.microsoft.com/en-gb/data/jj574232.aspx

 

[Deleted member 66701]

Have a read regarding lazy loading etc. https://msdn.microsoft.com/en-gb/data/jj574232.aspx

Thanks!

And I thought there was a lot to learn with PHP! I'm always so daunted by new stuff - so much to learn - seems like you'll never get to know everything about it!

 

[john_s]

You absolute beauty! Works a treat.

So what's the significance of the "virtual" bit then?

As above, it tells EF that there's a link between a book and an author so that the author details can be lazy loaded (ie loaded when you access it) when you use book.Author.

I don't know if you've already found them or not, but I found Microsoft's MVC tutorials a huge help when I first started using MVC: http://www.asp.net/mvc.

 

[philldutton]

They need to be virtual so that Entity Framework to override them when creating the proxy classes (which are derived from your classes) that it uses to enable lazy loading, change tracking etc...