Click here to Skip to main content
14,546,581 members

ASP.NET Core Razor Pages Using EntityFramework Core with Enums as Strings - Part I

Rate this:
5.00 (3 votes)
Please Sign up or sign in to vote.
5.00 (3 votes)
30 Oct 2018CPOL
Create initial object model and database and display first Razor Page

Introduction

This multi-part article demonstrates the mapping of C# enum values to string values in database tables via EntityFramework Core 2.1 (EF). It addresses the mapping of enum values in one-to-many and many-to-many relationships with application entities. It does this in the context of an ASP.NET Core Razor Page application.

EF is an Object-Relational Mapper (ORM). In applications such as this sample, there are two "worlds". One is the object world that exists as an object model in C#. The other is the relational world that exists in a relational database, like Microsoft SQL Server. These two worlds are not consistent with each other. The function of an ORM, like EntityFramework, is the bridge these two worlds and facilitates the transfer of data between them.

In Part I, we will review the following:

  • The conceptual and working object models for the sample application
  • Creation of the Visual Studio 2017 (VS2017) solution containing an ASP.NET Core Razor Page project
  • The initial creation of the application object model in C#, the Customer class
  • Setting up the application user interface (UI) layout and home page
  • Scaffolding the Customer CRUD (Create-Read-Update-Delete) pages with EF initialization via the QuantumDbContext class. This includes the creation of the application database in a localdb SQL Server instance.
  • Introduction of the configuration of object model classes for interaction with EF in separate classes to facilitate the design of database tables
  • Implement the Customers/Index page

Part II. Complete CRUD functions for Customers

Part III. Create Project and ProjectState entities and implement a one-to-many relationship between ProjectState and Projects

Part IV. Add Skill entities (Skill enum, SkillTitle and ProjectSkill) and implement a many-to-many relationship between Projects and Skills

Background

An enumeration is a data type consisting of a set of named constants. The names of the constants tend to be mnemonics. A mnemonic is defined as a device to assist in memory. However, there is a tendency to use mnemonics as constant names in enumerations. As a developer, the name that I might use would be cryptic to users. Part of the motivation to move from command line interfaces to graphical user interfaces (GUI) derives from the use of mnemonics as commands. Furthermore, the default mapping in an enum value is to an integer. The meaning of the integer value defeats the concept of a mnemonic.

There are some advantages to using an enum. The first is the set of named constants and is relatively small and facilitates self validation. If we attempt to use a value that is not defined in the enum, we generate an error.

The sample application implemented here is for a fictitious engineering and technology company, Quantum Engineering Technologies. Quantum primarily serves the oil, gas and chemical industries. The key entity is a Customer. Quantum serves a number of Customers. Each Customer, in turn, has a number of Projects. Each Project has a state defined in an enum, ProjectState. There is a one-to-many relationship between ProjectState and Projects. The ProjectState enum permits the definition of a State Machine to assist in managing the Project workflow. The Project State Machine is beyond the scope of this article.

A project requires a number of Skills. This application defines a Skill enum. There is a many-to-many relationship between Projects and Skills. These relationships are shown in the following figure representing the conceptual object model in C#.

Conceptual Object Model

Image 1

The enumerations contain essentially static data. The values are mnemonics, especially in the Skill enum. Most users do not readily recognize that a "CtrlE" is a control engineer and that a "DataSci" is a data scientist. One objective here is to implement a scheme that presents these values in the user interface (UI) in a way users can more easily understand. To accomplish this, we use the working object model as shown below.

Working Object Model

Image 2

The ProjectStateDescription entity maps the enum values from ProjectState to a Description string describing the Project state. The SkillTitle entity maps the enum values from Skill to a Title string in SkillTitle. The many-to-many relationship between Project and Skill is expressed via the ProjectSkill entity. We will discuss the significance of this object model later.

Development Environment

The sample solution was created in the following environment:

  • .Visual Studio 2017 (VS2017)
  • ASP.NET Core 2.1
  • Entity Framework Core v.2.1.4
  • NuGet Package Manager v.4.6.0
  • SQL Server LocalDb
  • SQL Server Management Studio

Using the Code

We begin by creating an empty VS2017 solution, QuantumEngSolution. There is an option to create a project, but a solution will be created anyway. As a mater of personal style, I prefer to create and name the solution explicitly.

Create QuantumEngSolution, an Empty Vs2017 Solution

Image 3

Click "OK" to continue. Next, add an ASP.NET Core 2.1 Web Application, QuantumWeb.

Add QuantumWeb ASP.NET Core Web Application to QuantumEngSolution

Image 4

Configure Project as an ASP.NET Core 2.1 Web Application

Image 5

This configures the application without authentication and to use HTTPS. If you encounter problems in debugging and testing, you will need to enable HTTPS in your VS2017 installation. (See this link.)

After clicking "OK", the initial Solution Explorer is shown below:

Initial Solution Explorer

Image 6

Next, we add a new folder, Model, to the QuantumWeb project. This folder will contain the files in our object model. For a larger application, we would create a Class Library project to hold the object model. Doing so would be consistent with the practice of separation of concerns and would facilitate the use of the object model in multiple applications. However, since this is a sample demonstration, we will keep the object model in the same project.

Now, we will create the model classes in the Model folder starting with the Customer class.

Create ~QuantumWeb\Model\Customer.cs

Image 7

Initial Customer Class
namespace QuantumWeb.Model
{
    /// <summary>
    /// Customer Class
    /// </summary>
    public class Customer
    {
        #region Constructors

        /// <summary>
        /// Parameter-less Constructor
        /// </summary>
        /// <remarks>
        /// Required for scaffolding the UI
        /// </remarks>
        public Customer()
        {
        } // end public Customer()

        #endregion // Constructors

        /// <summary>
        /// Customer Identifier, primary key
        /// </summary>
        public int CustomerId { get; set; }
        /// <summary>
        /// Customer Name
        /// </summary>
        public string CustomerName { get; set; }
        /// <summary>
        /// Primary Customer Contact
        /// </summary>
        public string CustomerContact { get; set; }
        /// <summary>
        /// Customer Contact Phone Number
        /// </summary>
        public string CustomerPhone { get; set; }
        /// <summary>
        /// Customer Contact Email Address
        /// </summary>
        public string CustomerEmail { get; set; }

    } // end public class Customer

} // end namespace QuantumWeb.Model

Start Building UI and Data Access

Now that we have the beginnings of an object model, we can begin building the user interface (UI) and data access functionality. ASP.NET Razor Pages can be configured to use layout pages. The primary layout page in the Solution Explorer is shown below.

Edit the Primary Layout Page

Image 8

Initial _Layout.cshtml
<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>@ViewData["Title"] - Quantum Application</title>

    <environment include="Development">
        <link rel="stylesheet" href="~/lib/bootstrap/dist/css/bootstrap.css" />
        <link rel="stylesheet" href="~/css/site.css" />
    </environment>
    <environment exclude="Development">
        <link rel="stylesheet" 
         href="https://ajax.aspnetcdn.com/ajax/bootstrap/3.3.7/css/bootstrap.min.css"
              asp-fallback-href="~/lib/bootstrap/dist/css/bootstrap.min.css"
              asp-fallback-test-class="sr-only" asp-fallback-test-property="position" 
              asp-fallback-test-value="absolute" />
        <link rel="stylesheet" href="~/css/site.min.css" asp-append-version="true" />
    </environment>
</head>
<body>
    <nav class="navbar navbar-inverse navbar-fixed-top">
        <div class="container">
            <div class="navbar-header">
                <button type="button" class="navbar-toggle" data-toggle="collapse" 
                 data-target=".navbar-collapse">
                    <span class="sr-only">Toggle navigation</span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                    <span class="icon-bar"></span>
                </button>
                <a asp-page="/Index" class="navbar-brand">Quantum Web Application</a>
            </div>
            <div class="navbar-collapse collapse">
                <ul class="nav navbar-nav">
                    <li><a asp-page="/Index">Home</a></li>
                    <li><a asp-page="/About">About</a></li>
                    <li><a asp-page="/Contact">Contact</a></li>
                    <li><a asp-page="/Customers">
                    Customers</a></li> <!-- Link to Customer pages -->
                </ul>
            </div>
        </div>
    </nav>

    <partial name="_CookieConsentPartial" />

    <div class="container body-content">
        @RenderBody()
        <hr />
        <footer>
            <p>&copy; 2018 - Quantum Engineering &amp; Technologies</p>
        </footer>
    </div>

    <environment include="Development">
        <script src="~/lib/jquery/dist/jquery.js"></script>
        <script src="~/lib/bootstrap/dist/js/bootstrap.js"></script>
        <script src="~/js/site.js" asp-append-version="true"></script>
    </environment>
    <environment exclude="Development">
        <script src="https://ajax.aspnetcdn.com/ajax/jquery/jquery-3.3.1.min.js"

                asp-fallback-src="~/lib/jquery/dist/jquery.min.js"

                asp-fallback-test="window.jQuery"

                crossorigin="anonymous"

                integrity="sha384-tsQFqpEReu7ZLhBV2VZlAu7zcOV+rXbYlF2cqB8txI/8aZajjp4Bqd+V6D5IgvKT">
        </script>
        <script src="https://ajax.aspnetcdn.com/ajax/bootstrap/3.3.7/bootstrap.min.js"

                asp-fallback-src="~/lib/bootstrap/dist/js/bootstrap.min.js"

                asp-fallback-test="window.jQuery && window.jQuery.fn && window.jQuery.fn.modal"

                crossorigin="anonymous"

                integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa">
        </script>
        <script src="~/js/site.min.js" asp-append-version="true"></script>
    </environment>

    @RenderSection("Scripts", required: false)
</body>
</html>

Next, we create the Customers Razor Pages and also setup data access. VS2017 provides a way to combine the scaffolding and setup of the UI for Customers and initialization of the data access. Right-click the Pages\Customers folder, then select "Add" from the popup menu then "New Scaffolded Item..." on the next popup.

Scaffold Customer Razor Pages

Image 9

A Razor Page scaffold option dialog is displayed. Select the "Razor Pages using Entity Framework (CRUD)" option.

Choose "Razor Pages using Entity Framework (CRUD)"

Image 10

This configures a set of Razor pages to Create, Read, Update and Delete (CRUD) Customers in a database via EF.

Configure Customer Razor Pages - Create QuantumDbContext

Image 11

Click "Add" on each dialog to build the solution and configure the Razor pages and EF. The _Viewstart.cshtml file specifies the use of _Layout.cshtml as the default layout.

_Viewstart.cshtml
@{
    Layout = "_Layout";
}

The scaffolding using the CRUD pages option produces a number of changes in the project.

Solution Explorer After Scaffolding Customer Pages

Image 12

We first discuss the modified and created files. There are some problems with the generated code that we will discuss shortly.

Modified appsettings.json
{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "QuantumDbContext": "Server=(localdb)\\mssqllocaldb;
        Database=QuantumDbContext-268e3954-a42e-460a-97a2-cff0a2cb9dd3;
        Trusted_Connection=True;MultipleActiveResultSets=true"

  }
}

The scaffolding process added a "ConnectionStrings" property to the application settings. This shows a database, QuantumDbContext-268e3954-a42e-460a-97a2-cff0a2cb9dd3, will be created on the localdb SQL Server instance. The scaffolding generates a default name from the name of the data context, QuantumDbContext, that was specified. This database will be used for initial development and testing. It is not intended for production.

Modified Startup.cs
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using QuantumWeb.Models;

namespace QuantumWeb
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a
                // given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });

            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

            // The application startup specifies SQL Server and the database specified in the 
            // appsettings.json file
            services.AddDbContext<QuantumDbContext>(options =>
                    options.UseSqlServer(Configuration.GetConnectionString("QuantumDbContext")));
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request
        // pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Error");
                app.UseHsts();
            }

            app.UseHttpsRedirection();
            app.UseStaticFiles();
            app.UseCookiePolicy();

            app.UseMvc();
        }
    }
}

The Startup class contains code that is executed on application startup. Its details are beyond the scope of this article. A detailed discussion can be found in Microsoft documentation here. The significant lines are shown in bold above. Here, the startup references the ConnectionString in the appsetting.json file and tells Entity Framework how to find the database.

Generated Pages\Index.cshtml
@page
@model IndexModel
@{
    ViewData["Title"] = "Quantum Engineering & Technologies";
}

<div class="jumbotron">
    <h1>Quantum Engineering &amp; Technologies</h1>
    <p>
        This is a sample application for a fictitious engineering and technology company.
    </p>
</div>

<div class="row">
    <div>
        <h2>Application illustrates</h2>
        <ul>
<span style="display: inline !important; float: none; background-color: rgb(251, 237, 187); 
color: rgb(0, 0, 0); font-family: Consolas,"Courier New",Courier,mono; font-size: 12px; 

font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; 

letter-spacing: normal; line-height: normal; orphans: 2; text-align: left; text-decoration: none; 

text-indent: 0px; text-shadow: none; text-transform: none; -webkit-text-stroke-width: 0px; 

white-space: pre; word-spacing: 0px;">            
<li>Sample pages using ASP.NET Core Razor Pages</li></span>
            <li>Entity Framework Core (v. 2.1.4 used here)</li>
            <li>
                Use of C# enumerations with members expressed in database as 
                strings as opposed to numbers
            </li>
            <li>One-to-many and many-to-many mapping between entities and enumerations</li>
            <li>Use of the Fluent API and IEntityTypeConfiguration classes for configuration</li>
            <li>
                Theming using <a href="https://go.microsoft.com/fwlink/?LinkID=398939">Bootstrap</a>
            </li>
        </ul>
    </div>
</div>

We replaced the Pages\Index page with the HTML show above. It gives some information about the sample demonstration. The key item is the "@page" string in the first line. This specifics that this is a Razor page.

Generated Pages\Index.cshtml.cs
using Microsoft.AspNetCore.Mvc.RazorPages;

namespace QuantumWeb.Pages
{
    public class IndexModel : PageModel
    {
        public void OnGet()
        {

        }
    }
}

Each Razor page consists of a .cshtml file, which defines its UI that is rendered in the layout and a .cshtml.cs file, which contains C# code that has the handlers for HTTP commands, GET and POST, primarily. The .cshtml.cs file is analogous to the WebForm code-behind. However, a Razor page has behavior that is more like the Model-View-Controller (MVC) pattern. You can read more about comparing Razor pages to MVC here.

Generated QuantumDbContext Class
using Microsoft.EntityFrameworkCore;
using QuantumWeb.Model;

namespace QuantumWeb.Models // Error!!!
{
    public class QuantumDbContext : DbContext
    {
        public QuantumDbContext (DbContextOptions<QuantumDbContext> options)
            : base(options)
        {
        } // end public QuantumDbContext (DbContextOptions<QuantumDbContext> options)

        #region DbSets

        public DbSet<Customer> Customer { get; set; }

        #endregion // DbSets

    } // end public class QuantumDbContext : DbContext
} // end namespace QuantumWeb.Models

This shows the initial data context class, QuantumDbContext, generated in the scaffolding process with some commenting added to aid readability and to avoid editing errors as the file becomes more complex later. Also, notice the "Error!!!" comment for the namespace. We will address this later. In EF Core, we are required to have a class derived from Microsoft.EntityFrameworkCore.DbContext.

With previous versions of EntityFramework, this was easily done in a Class Library. As we proceed, we will use EntityFramework Migrations to create and update the database. As of this writing, there is a problem with using EntityFramework Core migrations in a Class Library. If we are to easily separate the data access functions into a separate project and use migrations, it might best be done using a data access service. For now, we will forego that option.

The DbContext base class contains methods to facilitate interaction with the database and the associated conversions between the relational and object models. We will add to this class as we proceed in development.

As this data context currently stands, the properties of the object, Customer, in this case are converted to database columns using default transformations. In most cases, the default transformations do not meet the requirements of the application. To remedy this situation, EF defines two techniques to define the desired transformations, Data Annotations (not used here) and the Fluent API. Data Annotations use attributes in the model classes to define the transformations. There are two issues with Data Annotations. First, they are limited and cannot specify transformations available in Fluent API. Secondly, they violate the principle of separation of concerns between object or domain model classes and data access. The Fluent API uses a method, OnModelCreating, in the DbContext class to perform the transformations. The transformations can be defined via lambda expressions in this method. However, we can also define transformation classes for the model entities and reference these classes in the OnModelCreating method as we will now show.

Create ~\Data\CustomerConfiguration.cs

Image 13

Initial Data\CustomerConfiguration.cs

[Note: The namespace is QuantumWeb.Data. (See below.)]

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using QuantumWeb.Model;

namespace QuantumWeb.Data
{
    public class CustomerConfiguration : IEntityTypeConfiguration<Customer>
    {
        public void Configure(EntityTypeBuilder<Customer> builder)
        {
            builder.ToTable("Customers");
            builder.HasKey(c => c.CustomerId);
            builder.Property(c => c.CustomerId)
            .HasColumnType("int");
            builder.Property(c => c.CustomerName)
            .IsRequired()
            .HasColumnType("nvarchar(50)")
            .HasMaxLength(50);
            builder.Property(c => c.CustomerContact)
            .IsRequired()
            .HasColumnType("nvarchar(50)")
            .HasMaxLength(50);
            builder.Property(c => c.CustomerPhone)
            .IsRequired()
            .HasColumnType("nvarchar(15)")
            .HasMaxLength(15);
            builder.Property(c => c.CustomerEmail)
            .IsRequired()
            .HasColumnType("nvarchar(50)")
            .HasMaxLength(50);
        } // end public void Configure(EntityTypeBuilder<Customer> builder)

    } // end public class CustomerConfiguration : IEntityTypeConfiguration<Customer>

} // end namespace QuantumWeb.Data

The mappings between the Customer class and the database are presented in the following table:

Item Name C# type DB type IsRequired Comment
Customer Class Mappings
Table Customers        
CustomerId   int int Yes Primary Key
CustomerName   string nvarchar(50) Yes  
CustomerContact   string nvarchar(50) Yes  
CustomerPhone   string nvarchar(15) Yes  
CustomerEmail   string nvarchar(50) Yes
 
First Update to Data\QuantumDbContext.cs

[Note: The namespace is QuantumWeb.Data. (See below.)]

using Microsoft.EntityFrameworkCore;
using QuantumWeb.Model;

namespace QuantumWeb.Data
{
    public class QuantumDbContext : DbContext
    {
        public QuantumDbContext (DbContextOptions<QuantumDbContext> options)
            : base(options)
        {
        } // end public QuantumDbContext (DbContextOptions<QuantumDbContext> options)

        #region DbSets

        public DbSet<Customer> Customer { get; set; }

        #endregion // DbSets

        /// <summary>
        /// Data Model Creation Method
        /// </summary>
        /// <param name="modelBuilder">ModelBuilder instance</param>
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.ApplyConfiguration(new CustomerConfiguration());
        } // end  protected override void OnModelCreating(ModelBuilder modelBuilder)

    } // end public class QuantumDbContext : DbContext

} // end namespace QuantumWeb.Data

The modelBuilder.ApplyConfiguration() call injects an instance of CustomerConfiguration into the data conversion logic.

Generated Pages\Customers\Index.cshtml
@page
@model QuantumWeb.Pages.Customers.IndexModel

@{
    ViewData["Title"] = "Index";
}

<h2>Index</h2>

<p>
    <a asp-page="Create">Create New</a>
    <!-- A link to the Pages/Customers/Create page to create a new Customer -->
</p>
<!-- An HTML table to display existing Customers -->
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Customer[0].CustomerName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customer[0].CustomerContact)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customer[0].CustomerPhone)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Customer[0].CustomerEmail)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
@foreach (var item in Model.Customer) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.CustomerName)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CustomerContact)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CustomerPhone)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.CustomerEmail)
            </td>
            <td>
                <a asp-page="./Edit" asp-route-id="@item.CustomerId">Edit</a> |
                <!-- A link to the Pages/Customers/Edit page to edit an existing Customer -->
                <a asp-page="./Details" asp-route-id="@item.CustomerId">Details</a> |
                <!-- A link to the Pages/Customers/Details page to display 
                  the details for an existing Customer -->
                <a asp-page="./Delete" asp-route-id="@item.CustomerId">Delete</a>
                <!-- A link to the Pages/Customers/Delete page to delete an existing Customer -->
            </td>
        </tr>
}
    </tbody>
</table>

The Pages/Customers/Index.cshtml page lists existing Customers and has links to create new ones and to edit, display details and delete Customer records in the database.

Generated Pages\Customers\Index.cshtml.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.AspNetCore.Mvc.Rendering;
// No reference to QuantumWeb.Data namespace.  Error!!!
using QuantumWeb.Model;
using QuantumWeb.Models;  // Error!!!

namespace QuantumWeb.Pages.Customers
{
    public class IndexModel : PageModel
    {
        private readonly QuantumWeb.Models.QuantumDbContext _context; // Error!!!

        public CreateModel(QuantumWeb.Models.QuantumDbContext context) // Error!!!
        {
            _context = context;
        }

        public IList<Customer> Customer { get; set; }

        public async Task OnGetAsync()
        {
            Customer = await _context.Customer.ToListAsync();
        }
    }
}

This is the handler for the Customers/Index page. Notice the lines flagged with the comment, "// Error!!". This reflects the problems mentioned above following the scaffolding process.

  1. Notice the line, using QuantumWeb.Models. This namespace does not exist. The correct namespace for the model is QuantumWeb.Model. This line should be deleted from all .cshtml.cs files that have been generated.
  2. The QuantumDbContext class is referenced from the QuantumWeb.Models namespace. The file is located in the QuantumWeb\Data folder. We changed this namespace to QuantumWeb.Data. The reason is to implement the separation of concerns best practice to separate the data access entities, like QuantumDbContext, from the object model entities. To enforce this concept, we add a reference to the QuantumWeb.Data namespace in all .cshtml.cs files that use the QuantumDbContext. Note the namespaces in the First Update to Data\QuantumDbContext.cs and Initial Data\CustomerConfiguration.cs above.

First Modification to Pages\Customers\Index.cshtml.cs
using System.Collections.Generic;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using QuantumWeb.Data;
using QuantumWeb.Model;

namespace QuantumWeb.Pages.Customers
{
    public class IndexModel : PageModel
    {
        private readonly QuantumDbContext _context;

        public IndexModel(QuantumDbContext context)
        {
            _context = context;
        } // end public IndexModel(QuantumDbContext context)

        public IList<Customer> Customer { get;set; }

        public async Task OnGetAsync()
        {
            Customer = await _context.Customer.ToListAsync();
        } // end public async Task OnGetAsync()

    } // end public class IndexModel : PageModel

} // end namespace QuantumWeb.Pages.Customers

We made similar changes to the Pages\Customers\ .cshtml.cs files.

EF Migrations & Database Creation

At this point, we can address EF migrations and create our demonstration database. First, we need to install a NuGet package, Microsoft.EntityFrameworkCore.Tools v.2.1.4. This package allows us to use certain commands in the Package Manager Console. Normally, the Package Manager Console is displayed in the VS2017 IDE as a tab in the Output Window.

Package Manager Console in VS2017 IDE

Image 14

If the Package Manager Console is not visible, you can open it using the Tools > NuGet Package Manager > Package Manager Console menu command.

Opening the Package Manager Console in VS2017 IDE

Image 15

Much of what is done here uses the Package Manager Console. You can explore details about the Package Manager Console here.

There are two ways to install NuGet packages. One is to use a command in the Package Console Manager.

[Caution: In a solution with multiple projects, make sure the correct project is referenced in the Package Manager Console.]

Install-Package Microsoft.EntityFrameworkCore.Tools -Version 2.1.4

The other is done using the Solution Explorer.

Installing NuGet Package, Microsoft.EntityFrameworkCore v.2.1.4 in the VS2017 IDE

Image 16

Click "Install" to install the package.

Now we can create the initial migration. This will take the code in the QuantumDbContext and CustomerConfiguration classes to generate a Migration to handle the transfer of data between the application and the connected database.

Add-Migration Initial-Migration

This command is executed from the Package Manager Console. This will modify the Solution Explorer as shown.

Solution Explorer after Initial-Migration

Image 17

Generated 20181019171417_Initial-Migration.cs
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;

namespace QuantumWeb.Migrations
{
    public partial class InitialMigration : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Customers",
                columns: table => new
                {
                    CustomerId = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:ValueGenerationStrategy",

                        SqlServerValueGenerationStrategy.IdentityColumn),
                    CustomerName = table.Column<string>(type: "nvarchar(50)", 
                         maxLength: 50, nullable: false),
                    CustomerContact = table.Column<string>(type: "nvarchar(50)", 
                         maxLength: 50, nullable: false),
                    CustomerPhone = table.Column<string>(type: "nvarchar(15)", 
                         maxLength: 15, nullable: false),
                    CustomerEmail = table.Column<string>(type: "nvarchar(50)", 
                         maxLength: 50, nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Customers", x => x.CustomerId);
                });
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Customers");
        }
    }
}

The Migrations class defines two methods, Up, which handles transformations from the application to the database and Down which handles the reversal of those changes. It is a good idea to examine the generated code for the migration before taking the next step.

Update-Database

Again, the Update-Database command is executed in the Package Manager Console.

If these commands succeed, changes are propagated to the database. If this is the first migration against the database and the database does not exist, the database is created. This assumes you have permission on the database server to create a database. We can now create a connection to the database in the VS2017 Server Explorer.

Create a Database Connection in the VS2017 Server Explorer

Image 18

Simply right-click on the "Data Connections" node and select "Add Selection...".

Specify Server & Select Database

Image 19

Once this is done, you can click the "test" button to confirm that the database exists and that you can connect to it from VS2017.

Test & Create Connection

Image 20

Click the "OK" buttons to create the connection. Once the connection is there, you can click on its node in the Server Explorer open it. Then, open the "Tables" node and right-click on the Customers table and select "Open Table Definition". You should then see the table definition in the database like below.

Customers Table definition in QuantumDbContext Database

Image 21

At this point, we can build and execute the application in the debugger.

QuantumWeb Application Home Page: https//localhost: 44306/

Image 22

Click the "Customers" link to see the Customers pages.

QuantumWeb Application Customers Page: https//localhost: 44306/Customers

Image 23

There are no Customer records, so, our next task is to complete the Customer UI.

Summary

At this point, we have initially created an ASP.NET Core Razor Page application, QuantumWeb, which displays an Index page for Customers. We have completed the initial data access function via the QuantumDbContext class and created the application database in the localdb instance.

Points of Interest

In Part II of this article, we will complete the CRUD functions for Customers.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Leonard E. James
President Lionhart Technologies, LLC
United States United States
Technical professional with experience as Software Architect, IT Consultant, Developer, Engineer and Research Chemist. Current areas of emphasis are .NET, Entity Framework, application design and analysis.

Comments and Discussions

 
QuestionOops! There's a missing file Pin
Jeff Bowman5-Nov-18 10:17
professionalJeff Bowman5-Nov-18 10:17 
AnswerRe: Oops! There's a missing file Pin
Leonard E. James9-Nov-18 5:43
MemberLeonard E. James9-Nov-18 5:43 
GeneralRe: Oops! There's a missing file Pin
Jeff Bowman9-Nov-18 7:36
professionalJeff Bowman9-Nov-18 7:36 
GeneralRe: Oops! There's a missing file Pin
Jeff Bowman22-Nov-18 14:27
professionalJeff Bowman22-Nov-18 14:27 
GeneralRe: Oops! There's a missing file Pin
Leonard E. James23-Nov-18 6:46
MemberLeonard E. James23-Nov-18 6:46 
GeneralRe: Oops! There's a missing file Pin
Jeff Bowman23-Nov-18 7:42
professionalJeff Bowman23-Nov-18 7:42 
GeneralRe: Oops! There's a missing file Pin
Leonard E. James23-Nov-18 10:20
MemberLeonard E. James23-Nov-18 10:20 
GeneralRe: Oops! There's a missing file Pin
Jeff Bowman23-Nov-18 10:27
professionalJeff Bowman23-Nov-18 10:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Article
Posted 25 Oct 2018

Stats

12.4K views
244 downloads
13 bookmarked