Create a Database to Use with Code First

To create a database, there are various approaches you can take with Visual Studio. I’ll show you how to create one using the SQL Server Object Explorer in Visual Studio. You’ll create a simple database with products and categories similar to the one used at the beginning of the book so that you can easily follow along.

First, close any existing solutions that you have open in Visual Studio, and then open the SQL Server Object Explorer from the View menu. Next, open your local version of SQL Server (named (localdb)MSSQLLocalDB) and locate the database folder. Right-click this folder and choose Add New Database, as shown in Figure 11-1.

Figure 11-1. Adding a new database via SQL Server Object Explorer

In the Create Database window, add a new database named CodeFirstFromExistingDB and leave the location as the default. This will store the database under your current user folder in Windows.

Locate the new database in SQL Server Object Explorer and right-click on the Tables folder. Choose Add New Table, as shown in Figure 11-2.

Figure 11-2. Adding a new table to the CodeFirstFromExistingDB Database

The design window will now appear in Visual Studio. In the T-SQL pane, change the name of the Table from Table to Categories by updating the first line of the script as follows: CREATE TABLE [dbo].[Categories]. In the main design pane, add a new Column called Name with a Data Type of nvarchar(50) and uncheck the Allow Nulls column, as shown in Figure 11-3.

Figure 11-3. Adding a new Name column to the Categories table

Finally, we want the primary key to be auto-generated so right-click on the ID column, choose the Properties option from the menu, and change the (Is Identity) property to true in the Properties window (this is normally found in the bottom-right corner of Visual Studio), as shown in Figure 11-4.

Figure 11-4. Updating the Is Identity property of the ID column to true

Click on the Update button at the top-left of the screen. In the Preview Database Updates screen, click the Update Database button. The Data Tools Operations window should appear at the bottom of Visual Studio and inform you that the update completed successfully.

Next create a Products table by choosing Add New Table and performing the following changes:

• ID with Is Identity set to true

• Add a column called Name of data type nvarchar(50) with Allow Nulls unchecked

• Set the table name to Products in the T-SQL pane

• Set a foreign key field named CategoryID, which references the ID column of the categories table by doing the following:

  • Add a new column named CategoryID

  • Set the data type to int

  • Leave Allow Nulls checked

  • Right-click on the Foreign Keys item in the Design window and add a new foreign key named FK_Products_Categories (as shown in Figure 11-5). This will then add a new line to the T-SQL pane containing the new Foreign Key constraint. Alter this to reference the ID column of the categories table as follows:

    

    Figure 11-5. Adding a new foreign key to the Products table

    CONSTRAINT [FK_Products_Categories] FOREIGN KEY ([CategoryID]) REFERENCES [Categories]([Id])

When you have made these changes, the design window should appear as shown in Figure 11-6.

Figure 11-6. The complete set of changes required to create the Products table

Update the database; your changes should save successfully. You can now add some data to the database. First of all, add some categories by right-clicking the dbo.Categories table in SQL Server Object Explorer and choosing View Data. In the Data window, add three new categories named Toys, Sleeping, and Feeding. Use the Tab key to move between cells, as shown in Figure 11-7.

Figure 11-7. Adding data to the Categories table

Next, add the following data to the Products table:

• Ball with CategoryID of 1

• Rattle with CategoryID of 1

• Sleep Suit with CategoryID of 2

• Milk with CategoryID of 3

• Puree with CategoryID of 3

When you’re done, you should now have data in the Products table, as shown in Figure 11-8.

Figure 11-8. Adding data to the Products table

We now have a very simple database with products and categories. Finally, right-click on the database and copy the Connection String property so it can be used later.

Setting Up a New Project and Generating a Code First Data Model from the Database

Now we're going to create a new project to use with the CodeFirstFromExistingDB database. Create a new ASP.NET web application called CodeFirstExistingDB with the options shown in Figure 11-9.

Figure 11-9. Creating a new ASP.NET web application project named CodeFirstExistingDB

Click the OK button. In the next window, choose the ASP.NET 4.6.1 MVC Template with the Authentication type set to Individual User Accounts just to be consistent with the main BabyStore project. Figure 11-10 shows the options to select. Click the OK button to create the new project.

Figure 11-10. Template options for creating the CodeFirstExistingDB project

Once the new project is created, right-click on the project in Solution Explorer and choose to add a new item. Choose to add an ADO.NET Entity Data Model and name it StoreContext, as shown in Figure 11-11.

Figure 11-11. Adding a new ADO.NET entity data model

Click the Add button and then, in the Entity Data Model Wizard window, choose the Code First from Database option, as shown in Figure 11-12.

Figure 11-12. Choosing the Code First from Database model contents

Click the New Connection button. Now, in the Connection Properties window, set the server name to your local SQL server instance (likely to be (localdb)MSSQLLocalDB but you can verify this as it will be the first part of the connection string you copied earlier). Then, in the Connect to a Database section, choose the Select option or enter a database name and choose the CodeFirstFromExistingDB database from the list of available databases. Figure 11-13 shows the options to choose.

Figure 11-13. Choosing the connection properties for the data model

Next click the OK button. Then, from the Entity Data Model Wizard, accept the new options and click the Next button. Finally, in the Choose Your Database Objects and Settings pane, choose both database tables and check the Pluralize or Singularize Generated Object Names checkbox, as shown in Figure 11-14.

Figure 11-14. Choosing the options for database objects and settings

Click the Finish button. Visual Studio will now auto-generate the relevant code, as shown here. The new StoreContext class should appear in the main Visual Studio pane, as follows:

namespace CodeFirstExistingDB
{
    using System;
    using System.Data.Entity;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Linq;

    public partial class StoreContext : DbContext
    {
        public StoreContext()
            : base("name=StoreContext")
        {
        }

        public virtual DbSet<Category> Categories { get; set; }
        public virtual DbSet<Product> Products { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
        }
    }
}

The context class contains two DBSet entries, one for Categories and one for Products similar to the ones we created manually when modeling the BabyStore application. The OnModelCreating method is used for anything that cannot be modeled using attributes. In this example, everything can be covered by using attributes, so this is blank.

The project now also contains two new class files—one for Category and one for Products—representing the Product and Category entities and mapping to the Products and Categories database tables. These classes are contained in the root of the project rather than in the models folder. You can move them into the models folder and change their namespaces if desired.

The auto-generated Category class is as follows:

namespace CodeFirstExistingDB
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class Category
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
                 "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Category()
        {
            Products = new HashSet<Product>();
        }

        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage",
                 "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Product> Products { get; set; }
    }
}

The class contains properties for each database field and is similar to the one we created by hand when beginning the BabyStore project. It also contains an extra creation of a hashset in an auto-generated constructor and some attributes relating the diagnostic code analysis, which is outside the scope of this book. It also adds attributes to the Name field to control the field length and the fact that the field is required.

The generated Product class is as follows:

namespace CodeFirstExistingDB
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class Product
    {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        public int? CategoryID { get; set; }

        public virtual Category Category { get; set; }
    }
}

This class contains properties for each database field and a virtual property representing the relationship to the category entity. We now have a data model representing the database, which we can use with Entity Framework Code First in the same way as the BabyStore project.

Setting Up a Controller and Views to View the Data

To prove that everything works as expected, we'll set up a new controller and some views by using scaffolding. Build the solution and then right-click on the Controllers folder and choose Add Controller. Then add a new MVC 5 Controller with views, using Entity Framework. Set the Model class as Product and the Data context class as StoreContext. Check the Generate Views, Reference Script Libraries, and Use a Layout Page options, as shown in Figure 11-15. Then click the Add button.

Figure 11-15. Adding a ProductsController class with views

Visual Studio will now create a ProductsController class with Index and CRUD methods. It will also create a set of views under the Views/Products folder. To view the data in your web site, right-click on the Views/Products/Index.cshtml file and choose View in Browser. The index page should open as shown in Figure 11-16. It shows the products from the CodeFirstFromExistingDB database.

Figure 11-16. The Products Index page showing data from the Products table of the CodeFirstFromExistingDB database

Updating the Existing Database Using Code First Migrations

There is going to come a point where you will want to update your existing database model from your code. We've seen earlier in the book that the way to do this is to use migrations; however, using Code First with an existing database requires a little extra care to work correctly.

First of all, you must enable migrations for the project. In Package Manager Console, type the following command (we need to specify the context to use because we included authentication in the project and this uses its own context by default):

Enable-Migrations -ContextTypeName CodeFirstExistingDB.StoreContext

This will create a Migrations folder and add a Configuration.cs file to it. Next we want to create our migrations to run. This is where you need to add an extra step for an existing database. If we create a migration now, it will attempt to add all our entities to the database. This will not work because the products and categories tables already exist in the database, so we need to create an initial blank migration and then later we will be able to add a migration for any new changes. To create an initial blank migration for the database, type the following command into Package Manager Console:

Add-Migration InitialCreate -IgnoreChanges

The key part of this command is the -IgnoreChanges flag, which ensures that a migration is created that effectively does nothing. Running it will add an entry to the migrations table in the database, thus creating a snapshot of its original schema.

Next, run the update-database command in order to update the existing database with the initial migration. A new migrations table will now have been created in the CodeFirstFromExistingDB database.

Following this, add a new property named Description to the Product class, with a maximum allowed length of 50 characters, as follows:

namespace CodeFirstExistingDB
{
    using System;
    using System.Collections.Generic;
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    using System.Data.Entity.Spatial;

    public partial class Product
    {
        public int Id { get; set; }

        [Required]
        [StringLength(50)]
        public string Name { get; set; }

        [StringLength(50)]            
        public string Description { get; set; }            

        public int? CategoryID { get; set; }

        public virtual Category Category { get; set; }
    }
}

Now add a new migration for the product Description field so you can add it as a new column to the Products table. You do this by typing the following command in the Package Manager Console:

Add-Migration add_product_description

A new code file will be produced in the Migrations folder that will include code to add a description column to the Products table as follows:

namespace CodeFirstExistingDB.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class add_product_description : DbMigration
    {
        public override void Up()
        {
            AddColumn("dbo.Products", "Description", c => c.String(maxLength: 50));
        }

        public override void Down()
        {
            DropColumn("dbo.Products", "Description");
        }
    }
}

Now run the update-database command in Package Manager Console to update the database. The new description column will be added to the Products table, as shown in Figure 11-17.

Figure 11-17. The new Description column in the Products table of the CodeFirstFromExistingDB database

Now add some test data to the database. View the data of the Products table via SQL Server Object Explorer and enter some descriptions, as shown in Figure 11-18.

Figure 11-18. Adding test data to the Description column of the Products table

Next, modify the ViewsProductsIndex.cshtml file to add a Description field, as highlighted in the following code:

@model IEnumerable<CodeFirstExistingDB.Product>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.Category.Name)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.Name)
        </th>
        <th>            
            @Html.DisplayNameFor(model => model.Description)            
        </th>            
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.Category.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>            
            @Html.DisplayFor(modelItem => item.Description)            
        </td>            
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.Id }) |
            @Html.ActionLink("Details", "Details", new { id=item.Id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.Id })
        </td>
    </tr>
}

</table>

Now, right-click on the view and choose View in Browser. You should now see the new Description field with the test data, as shown in Figure 11-19.

Figure 11-19. The Product index page containing the new Description column and data

You can now work with your existing database as if you had created it using Code First and continue to update it using Code First as required.

Summary

This chapter started by showing you how to manually create a database with data via Visual Studio and then how to create a project containing classes based on this database. We then covered how to work with Code First Migrations in an existing database and update the database based on code changes.