Building an Entity Framework 7 MVC Web App using Visual Studio 2013 – Part 2

In this post we will display data from our database on a web page, then we will walk through a modification of our initial database structure using Entity Framework 7 (EF7).

Make sure you walk through the first part of this post series, Part 1.

Hopefully you were able to enter the data without an issue in part 1. Let’s quickly test our ability to retrieve the data in SQL Server and display it using MVC 5.

Right click on the Controllers folder and select Add -> Controller -> MVC 5 Controller – Empty. Name the controller HomeController.

addhomecontroller

Modify your the HomeController.cs file to match this.

using SightingsWebApp.DAL;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SightingsWebApp.Controllers
{
    public class HomeController : Controller
    {

        // GET: Home
        public ActionResult Index()
        {
            ViewBag.Message = "Zoo animals in da house!";
            using (var _db = new ZooSightingDemoDb())
            {
                var animals =
                    _db.Animals
                        .Where(a => a.Weight > 1.0 
                            && a.Group == AnimalGroup.Fish)
                        .OrderBy(a => a.Name)
                        .ThenBy(a => a.Weight)
                        .Skip(1)
                        .Take(5)
                        .ToList();

                return View(animals);
            }
        }
    }
}

Right click on the Index action method above and Add View named Index leaving all Options unchecked and Template Empty (without model).

addemtpyview

Write the view to look like this.
writeview

Compile and and run the app (F5), your brower should look like this. I’m using IE11.

zoodisplay

Here is what the T-SQL looks like that was sent to the database.

SELECT [a].[Id], [a].[Group], [a].[Name], [a].[Weight], [a].[ZooId]
FROM [Animal] AS [a]
WHERE ([a].[Weight] > 1 AND [a].[Group] = 2)
ORDER BY [a].[Name], [a].[Weight] OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY

You’re feeling real good right now but then your data modeler tells you she changed the data model slightly. Consequently the ZooAnimalSighting class in the Models folder needs to look like this.

    public class ZooAnimalSighting
    {
        public int Id { get; set; }
        public string Sighter { get; set; }
        public DateTime SightingDate { get; set; }
        public string Comment { get; set; }
        public bool? DoYouWearGlasses { get; set; }

        // Navigation
        public virtual Animal Animal { get; set; }
    }

Let’s go ahead and make those changes, but didn’t we already create our database? Yes, but now we can add another migration and apply it.
After the code changes are made compile the app.
In the Package Manager Console run the following.
Add-Migration -Name ModifyZooAnimalSighting -Context ZooSightingDemoDb -Verbose -Project SightingsWebApp
We now have another file in our Migrations folder.
Build your program making sure there are no errors.

Now apply the new migration in Package Manager Console
Apply-Migration -Context ZooSightingDemoDb -Verbose -Project SightingsWebApp

Here is the T-SQL sent to the database:

ALTER TABLE [ZooAnimalSighting] DROP COLUMN [IsSightingReliable];
ALTER TABLE [Animal] ALTER COLUMN [Id] int NOT NULL IDENTITY;
ALTER TABLE [Zoo] ALTER COLUMN [Id] int NOT NULL IDENTITY;
ALTER TABLE [ZooAnimalSighting] ALTER COLUMN [Id] int NOT NULL IDENTITY;
ALTER TABLE [ZooAnimalSighting] ADD [Comment] nvarchar(max);
ALTER TABLE [ZooAnimalSighting] ADD [DoYouWearGlasses] bit;
INSERT INTO [dbo].[__MigrationHistory] ([MigrationId], [ContextKey], [ProductVersion])
VALUES ('20150425165707_ModifyZooAnimalSighting', 'SightingsWebAppDemo.DAL.ZooSightingDemoDb', '7.0.0-beta5-13012');
;

The alter id column entries seem not needed and produce sql errors but this is beta code so we will ignore for now. I’ll submit an issue on GitHub for this. Issue number is #2070.

I ran the changes above without the ALTER COLUMN [Id] statements and it ran successfully.

Let’s check the database and make sure the changes were made that we wanted.
dbchanges

 

Ok, the ZooAnimalSighting table looks good. Now we know if they change the database model we can accommodate them by adding a new migration and applying it.