Creating a Model and Database in ASP.NET Core Razor Pages Using Entity Framework Core

Introduction

 

In this article, we will understand the basics of how we can create a model and add connection string and packages to create migrations and connect to database using entity framework core.

 

Inside appsettings.json, we need to add connection string for our database. The connection string should be in JSON format. The name of the connection string can be DefaultConnection. Inside the connection string, we need to write the server name, as well as the name of the database that we want our application to connect with.

{

 "ConnectionStrings": {
  "DefaultConnection": "Server=DESKTOP-QGAEHNO\\SQLEXPRESS;Database=BookListRazor;Trusted_Connection=True;MultipleActiveResultSets=True"
 },


 "Logging": {
  "LogLevel": {
   "Default": "Information",
   "Microsoft": "Warning",
   "Microsoft.Hosting.Lifetime": "Information"
  }
 },
 "AllowedHosts": "*"
}

Now, we need to add this to our project configuration inside the Startup.cs file. For this, we have to make changes to the ConfigureServices method.

 

We need to add DbContext class using services. Inside DbContext class, we have to create a database object. By database object, I mean creating a Db Context that will hold all of the models for any particular application.

 

In order to create a database object, we need to create a separate DbContext class. This can be called ApplicationDbContext and will implement DbContext.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BookList.Model
{
    public class ApplicationDbContext:DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

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

So now, if we want to add a model, we can add it inside the ApplicationDbContext constructor.

 

Now, we need to add this to our project configuration inside the Startup.cs file. For this, we have to make changes to the ConfigureServices method.

public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(option=> option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddRazorPages();
        }

With this, we have added DbContext to our application.

 

The next step is to create a model.

 

The model represents any table that we want in our database. In this application, we want to manage a list of books, so for that we will add a model or a table called book.

In order to do that, let's create a new class file called Book. Inside of the book class, we need to add some properties, like ID, Name and Author.

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Threading.Tasks;

namespace BookList.Model
{
    public class Book
    {
        [Key]
        public int Id { get; set; }

        [Required]
        public string Name { get; set; }

        public string Author { get; set; }

    }
}

Once we created this book, we need to add this to database, add some packages for entity framework and also setup the connection string.

 

Now that we have added the book model inside our project, we need to set up the database. In order to set up the database, we need to install a few packages

We can install the required packages by clicking on Tools -> NuGet package manager -> Manage NuGet Packages for Solutions.

 

We need to install entity framework packages.These are required because we will be using entity framework to connect to and access the database.

 

The packages we need to install are Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools.

 

Microsoft.EntityFrameworkCore.Tools package will be required to run migrations to create table inside database and update the database as well.

 

So now, after all the packages have been installed, we need to set up a connection to sql server database. For this, we need to add a connection string inside appsettings.json.

Inside of the connection string, we need to add a server and database name. In order for the connection to be set up, we have to create a database.

{

  "ConnectionStrings": {
    "DefaultConnection": "Server=DESKTOP-QGAEHNO\\SQLEXPRESS;Database=BookListRazor;Trusted_Connection=True;MultipleActiveResultSets=True"
  },


  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

We need to make sure that database is not created inside SQL server. Rather, we have to create a database inside of Microsoft Visual Studio with the help of migrations.

Now, we need to configure the startup.cs file and run migrations to create a database and update it.

 

We need to configure the ConfigureServices method with entity framework. In order to configure this, we need ApplicationDbContext, or DbContext class. We have to create an ApplicationDbContext.cs class which will be inherit the DbContext class, available inside the Microsoft.EntityFrameworkCore package.

 

After this, we need to implement the constructor and pass DbContext options to the parent class, as shown below.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BookList.Model
{
    public class ApplicationDbContext:DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

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

This is an empty constructor, but a parameter is needed for dependency injection.

 

Now we need to add the book model that we added to the database. In order to add any model to the database inside DbContext, properties are needed as shown below.

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace BookList.Model
{
    public class ApplicationDbContext:DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
        {

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

Now we need to add DbContext inside of the startup.cs as follows.

public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<ApplicationDbContext>(option=> option.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddRazorPages();
        }

This is how entity framework is included inside of the  configuration pipeline. Now we need to push all this to database using migration command, as mentioned below

 

add-migration AddBookToDb

 

Then, we need to write this command inside package manager console and press enter. Adding migration will create a script that will execute across the database. The script will automatically create a table named Book and add column ID, Name and Author. It is also applying the required constraints on the table columns.

 

To create a database and table inside of SQL server, we need to use the command update-database.

 

Summary

 

In this article, we created a model and added connection string and packages to create migrations and connect to a database. We have used migration commands to create and update a database inside MS SQL Server.