C# , MVC and querying db help required.

  • Thread starter Thread starter Deleted member 66701
  • Start date Start date

Deleted member 66701

D

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.:-

Capture_zpszzjotmqt.png


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

My DB is set up as follows:-

Capture1_zpsqiwsl3dn.png


The DBVontroller.cs class is as follows:-

Code:
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:-

Code:
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:-

Code:
@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 ;-)
 
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.
 
Try updating your Book class as below to link the Book class to the Author class:

Code:
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; }
    }
 
Try updating your Book class as below to link the Book class to the Author class:

Code:
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?
 
Last edited by a moderator:
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.
 
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...
 
Back
Top Bottom