Implement a Relational Database With ASP.NET Core

Design the Model

You've learned about the MVC design pattern, and specifically how the model portion of that pattern can relate to a database. A data model is comprised of C# classes and a database context object that represents a connection or session with the database. The context object is what gives you access to the database, and the ability to read and write, data.

I mentioned in the video above that it was possible to create a database from your model using code-first migrations as well as to generate a model from an existing database using database-first integration. In this chapter, we’re going to use EF Core and code-first migrations as a means of creating a database, so we’ll need to construct a model.

I don't want you to get lost in a model that's too large, so I thought we would design one for a simple recipe catalog. We need to ask some questions:

I’ve designed a preliminary model; however, there are other ways to create models and improve on this one. The purpose of this model is to provide a context for our discussion of code-first migrations as a means to build a relational database and to provide a foundation for all the examples and code exercises we’ll do throughout the rest of the course. You’ll need to use this model to complete all the exercises, but you are also welcome to build another one if you wish, just use a separate application. You can adapt the exercises to fit another, but you’ll need to create the data yourself.

Here’s the model we’re going to start with:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<RecipeIngredient>()
        .HasKey(t => new { t.RecipeId, t.IngredientId });
}

Code the Model

Given the design above, you need the following classes:

• Recipe

• Ingredient

• RecipeIngredient

• FoodType

• FoodEthnicity

• UnitOfMeasure

So, let’s start with these and see where to go from there:

Create a new MVC project in Visual Studio and call it MyRecipes.

Assuming you’ll want to keep your recipes private, set the authentication type to Individual User Accounts.

Next, add the  Recipe  class to the models folder. Add the class, and then add the attributes according to the design in Section 1.

You should end up with something like this:

public class Recipe
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int BakeTemperature { get; set; }
    public int BakeTime { get; set; }
    public string Instructions { get; set; }
    public int FoodTypeId { get; set; }
    public int FoodEthnicityId { get; set; }
 
    public virtual FoodType FoodType { get; set; }
    public virtual FoodEthnicity FoodEthnicity { get; set; }
    public virtual ICollection<RecipeIngredient> RecipeIngredients {
        get; set; }
}

Coding Exercise: Complete the Model

Go ahead and try to code the rest of the classes on your own. Once you’re done adding those classes, you’ll be ready to get on with the migration process.

Perform the Migration

Before you perform the migration, you need to make sure the connection string for the database is pointing to the right place. If you’ve already installed SQL Server Management Studio (SSMS), then you’ve already set up your LocalDB server, so you just need to make sure to change the server reference in the configuration string to match your server name. I’ll show you where this is in a moment.

If you haven’t installed SSMS yet, you’ll need to do that now before proceeding with the rest of the chapter. When you install it, you’ll be asked to give your local instance of SQL Server a name that you'll reference in the configuration string.

In VS 2017 and EF 6, your database configuration strings were found in the Web.config file in the root folder of your project. This has changed with EF Core and VS 2019. You’ll now find the connection strings in the appsettings.json file, still in the root folder of your project.

The connection strings section of this file looks like this (I’ve broken up the string for clarity here. Do NOT do this in your code, as it will invalidate the string):

"ConnectionStrings": {
    "DefaultConnection": 
        "Server=(localdb)\\mssqllocaldb;
        Database=aspnet-MyRecipes-05FBD0B2-D66C-44FE-8A91-BB54D9A51A51;
        Trusted_Connection=True;
        MultipleActiveResultSets=true"
},

The two pieces I want to call your attention to are the server and database elements. For server, you can leave it as the LocalDB if you wish, or you can change it to match another local named server instance that you may have set up during SQL Server installation.

"ConnectionStrings": {
    "DefaultConnection": 
        "Server=(localdb)\\mssqllocaldb;
        Database=MyRecipesDB;
        Trusted_Connection=True;
        MultipleActiveResultSets=true"
  },

Notice that I’ve changed the database name to MyRecipesDB. The default was too long for my tastes.

In VS 2017 and EF 6, it is necessary to manually enable migrations before you can perform a code-first migration. This was done with the enable-migrations command in the Package Manager Console window.

However, with EF Core and VS 2019, the  enable-migrations  command is obsolete. Migrations are enabled by default, so all you have to do is add a migration.

If you type  add-migration  in the Package Manager Console and hit Enter, you’ll get some additional instructions. Each migration requires a name. This keeps track of your migrations, in the event you have to revert to a previous error. It’s version control for your model.

You can add migrations this way, or you can include the name of the migration in the command:  add-migration InitialMigration. If you received errors during your migration, you likely have errors in your model code. See if you can find and correct them. If you get stumped, refer to the code in Section 4 at the end of this chapter. 

I received no errors in my initial migration, so I have a new database that matches my model. If you want to check your database in Visual Studio, you can do so by opening the SQL Server Object Explorer window. To do this, select View > SQL Server Object Explorer. Locate your server and expand its contents. You should see your newly created recipes database beneath the Databases folder. 

Beneath the Tables folder, you’ll find all of the tables created from your data model. You’ll find tables matching the classes you designed and coded as well as tables you did not design. These have the prefix AspNet. These are all created by .NET Identity using Entity Framework to manage individual user accounts, which is the option you selected when you created the project. Don’t need to worry about those right now. They’re for your use later if you choose.

Look at each of the tables that match the classes in your data model and make sure the column names match the properties. You should see a direct correlation!

Let's Recap!

In this chapter, your goal was to build a new database for your recipe app using code-first migrations, which you used previously in your MVC class. The migration scans the C# classes of your data model and, in conjunction with your ORM (Entity Framework), uses them to construct the corresponding tables and relationships in your database. 

To do this, you completed the following tasks:

1. Constructed a data model to store, search, and retrieve recipes.

2. Configured the database connection string in the appsettings.json file so that your application can find and connect safely to the database.

3. Modified the database context class,  ApplicationDbContext, with  DbSet  definition objects that indicate how the database tables should be constructed and mapped using the model classes.

4. Added an initial migration using the Package Manager Console (PMC) window.

5. Used the update-database  command in the PMC to perform the migration.

6. Used the SQL Server Object Explorer in Visual Studio to connect to the LocalDB, located your new database, and validated its creation from the model.

That’s quite a bit of work you’ve done so far, but we’re just getting started. Next, in Chapter 4, you’ll learn how to execute SQL queries on your new database, using both raw SQL as well as LINQ from within the C# code of your MVC app. 

Source Code for Coding Exercise

After completing the exercise in the middle of the chapter above, you can compare your work with the example classes I've written below:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;
 
namespace MyRecipes.Models
{
    public class Recipe
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public int BakeTemperature { get; set; }
        public int BakeTime { get; set; }
        public string Instructions { get; set; }
        public int FoodTypeId { get; set; }
        public int FoodEthnicityId { get; set; }
 
        public virtual FoodType FoodType { get; set; }
        public virtual FoodEthnicity FoodEthnicity { get; set; }
        public virtual ICollection<RecipeIngredient> RecipeIngredients 
            { get; set; }
    }
    public class Ingredient
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public double Amount { get; set; }
        public int? UnitOfMeasureId { get; set; }
 
        public virtual UnitOfMeasure UnitOfMeasure { get; set; }
        public virtual ICollection<RecipeIngredient> IngredientRecipes 
            { get; set; }
    }
 
    public class RecipeIngredient
    {
        public int RecipeId { get; set; }
        public int IngredientId { get; set; }
 
        public virtual Recipe Recipe { get; set; }
        public virtual Ingredient Ingredient { get; set; }
    }
 
    public class FoodType
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
 
    public class FoodEthnicity
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
 
    public class UnitOfMeasure
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}