Lesson Sunday

In this lesson, we'll begin refactoring our existing models and controllers, learning how to retrieve and display information from a database using Entity in the process. We'll also start using LINQ, which gives us access to a simple syntax for querying lists.

Setup


First, we need to set up our database to match Entity conventions.

Using MySQL Workbench, find the items table in the Navigator, right click and select Alter Table....

  • Change the name of the id column to ItemId.
  • Change the name of the description column to Description.

Click Apply.

As shown above, our column names have UpperCamelCase just like properties. We will follow this convention when setting up databases from now on.

Updating the Item Model

Next, let's replace all of the code in our Item class with the following:

Models/Item.cs
namespace ToDoList.Models
{
  public class Item
  {
    public int ItemId { get; set; }
    public string Description { get; set; }
  }
}

We've removed our constructor and all of our methods. We've also renamed our Id property to ItemId. This is because properties named ID or [ClassName]Id are interpreted by Entity as the primary key.

Our Item class is now complete. We no longer need to add verbose database-backed methods like Find() and Save(). Entity will take care of this for us!

Updating the Controllers

Next, we need to update our controllers to manage our to do list entries. Let's comment out all of the content of our CategoriesController.cs and begin refactoring ItemsController.

Let's replace all of the content of ItemsController.cs with the following:

Controllers/ItemsController.cs
using Microsoft.AspNetCore.Mvc;
using ToDoList.Models;
using System.Collections.Generic;
using System.Linq;

namespace ToDoList.Controllers
{
  public class ItemsController : Controller
  {
    private readonly ToDoListContext _db;

    public ItemsController(ToDoListContext db)
    {
      _db = db;
    }

    public ActionResult Index()
    {
      List<Item> model = _db.Items.ToList();
      return View(model);
    }
  }
}

We've added some new code here, including a property, a constructor, and an Index route.

  • We add a new using directive for System.Linq. This will allow us to use LINQ's ToList() method, which we'll cover in a moment.

  • The line private readonly ToDoListContext _db; declares a private and readonly field of type ToDoListContext.

  • The constructor above allows us to set the value of our new _db property to our ToDoListContext. This is achievable due to a dependency injection we set up in our AddDbContext method in the ConfigureServices method in our Startup.cs file. Don't stress out about the details of this particular implementation and focus more on the queries we are to run in our action methods.

  • Instead of using a verbose GetAll() method with raw SQL, we can instead access all our Items in List form by doing the following: _db.Items.ToList(). It's one of many ways in which LINQ can make our lives as developers much easier.

Language-Integrated Queries (LINQ)

LINQ is short for Language-Integrated Query. It's a form of standard data interpretation. We'll use LINQ to grab values from our dataset and display them in our views.

In the above example, our dataset is _db.Items. However, we can't actually use a dataset as our model for our view. To get around this, we run the LINQ method ToList() on our data set. This generates a list with elements of the Item data type. In other words, LINQ translates the dataset into a list we can use in the view. To learn more, read through the official documentation

Let's walk through how this works by breaking down the line _db.Items.ToList() further.

  • db is an instance of our DbContext class. It's holding a reference to our database.

  • Once there, it looks for an object named Items. This is the DbSet we declared in ToDoListContext.cs.

  • LINQ turns this DbSet into a list using the ToList() method, which comes from the System.Linq namespace.

  • This expression is what creates the model we'll use for the Index view.

Updating Views

Since we will be restructuring our models to account for our database, let's go ahead and remove all of the views in the Views/Items directory and remove the entire Views/Categories directory.

Let's create an Index.cshtml view in our now empty Views/Items directory to see if our database query is properly working:

Views/Items/Index.cshtml
@{
  Layout = "_Layout";
}

@using ToDoList.Models;

<h1>Items</h1>

@if (@Model.Count == 0)
{
  <h3>No items have been added yet!</h3>
} 

@foreach (Item item in Model)
{
  <li>@item.Description</li>
}

We can now start the server. Ignore the links in the homepage and instead navigate to localhost:5000/Items by explicitly typing in the url in the address bar. Notice that any items we may have had in our to do list are now showing. If there were no items in your to do list you will see the "No items have been added yet!" notice. We'll introduce functionality to add entries in the next lesson.

Repository Reference

Follow the link below to view how a sample version of the project should look at this point. Note that this is a link to a specific commit in the repository.

Example GitHub Repo for To Do List

Models/Item.cs

namespace ToDoList.Models
{
  public class Item
  {
    public int ItemId { get; set; }
    public string Description { get; set; }
  }
}

Controllers/ItemsController.cs

using Microsoft.AspNetCore.Mvc;
using ToDoList.Models;
using System.Collections.Generic;
using System.Linq;

namespace ToDoList.Controllers
{
  public class ItemsController : Controller
  {
    private readonly ToDoListContext _db;

    public ItemsController(ToDoListContext db)
    {
      _db = db;
    }

    public ActionResult Index()
    {
      List<Item> model = _db.Items.ToList();
      return View(model);
    }
  }
}

Controllers/HomeController.cs

using Microsoft.AspNetCore.Mvc;

namespace ToDoList.Controllers
{
  public class HomeController : Controller
  {
    [HttpGet("/")]
    public ActionResult Index()
    {
      return View();
    }
  }
}

Views/Items/Index.cshtml

@{
  Layout = "_Layout";
}

@using ToDoList.Models;

<h1>Items</h1>

@if (@Model.Count == 0)
{
  <h3>No items have been added yet!</h3>
} 

@foreach (Item item in Model)
{
  <li>@item.Description</li>
}

Lesson 23 of 36
Last updated more than 3 months ago.