ASP.NET Core MVC with EF Core DB First Approach

Back to: ASP.NET Core Tutorials For Beginners and Professionals

ASP.NET Core MVC with EF Core DB First Approach

In this article, I will discuss ASP.NET Core MVC Web Application using Entity Framework Core Database First (EF Core DB First) Approach to Perform CRUD Operations. Please read our previous article discussing the ASP.NET Core MVC Request Life Cycle. Here, I will use the .NET 6 MVC Application, Visual Studio 2022, and SQL Server 2022 database.

ASP.NET Core MVC with EF Core DB First Approach

ASP.NET Core is a Web Development framework from Microsoft, and Entity Framework Core is an Object-Relational Mapping (ORM) tool that enables developers to work with databases using .NET objects. EF Core is designed to work with .NET Core and .NET 5+.

Entity Framework Core (EF Core) architecture is designed to provide a flexible and extensible framework for working with databases in ASP.NET Core Applications. It consists of several key components that enable data access and object-relational mapping. Here’s an overview of the main components of EF Core’s architecture:

1. DbContext: In EF Core, you work with a class called DbContext, which represents a session with the database and acts as a bridge between your application and the database. The DbContext contains DbSet properties for each entity, and you use these properties to interact with the corresponding database tables.
2. Entity Classes (POCOs): Entity Framework Core uses Plain Old CLR Objects (POCOs) as data models. These POCO classes represent your database’s entities (tables), with each property mapping to a column in the database table.
3. Migrations: EF Core supports database migrations, which are used to create, update, and maintain the database schema based on your data model changes. Migrations allow you to evolve the database schema and your application’s data model.
4. LINQ (Language-Integrated Query): EF Core enables you to use LINQ to perform queries against the database. This makes it easy to retrieve, filter, and manipulate data in your application.
5. Database Providers: EF Core supports multiple database providers, including Microsoft SQL Server, SQLite, PostgreSQL, MySQL, Azure Cosmos database, Oracle database, and others. You can switch between these providers easily to work with different databases.
6. Change Tracking: EF Core keeps track of changes made to your entity objects and automatically generates the appropriate SQL statements to persist those changes to the database when you call the SaveChanges method.
7. Transactions: EF Core allows you to work with transactions to ensure that multiple database operations are performed by applying the principle do everything or do nothing.
8. Database Seeding: EF Core allows you to seed the database with initial data when the application starts.
9. Dependency Injection: EF Core is designed to work seamlessly with the built-in dependency injection (DI) container in .NET Core and .NET 5+. This allows you to inject instances of DbContext into your application’s services and controllers, promoting testability and loose coupling.

In this article, we will create one ASP.NET Core MVC Web Application that will perform CRUD operations on the database table using the Entity Framework Core Database First (EF Core DB First) Approach. We will be using the Microsoft SQL Server database. Let us proceed and implement the application step by step:

Creating the Database and Required Tables in SQL Server Database:

As we will use the Entity Framework Core Database First Approach, we first need to prepare the database, and based on the database, the model classes and DbContext will be generated. In this demo, we will perform the database CRUD Operations on the following Employee table.

So, please execute the following SQL Script on the SQL Server Database to Create the Company Database and Employee table and Populate the Employee table with the required test data.

CREATE DATABASE Company
GO

USE Company
GO

CREATE TABLE Employee(
 Id int IDENTITY(1,1) PRIMARY KEY,
 FirstName nvarchar(50) NOT NULL,
 LastName nvarchar(50),
 Salary numeric(18, 0),
 Department nvarchar(50)
)
GO

INSERT INTO Employee VALUES('Pranaya', 'Rout', 10000, 'IT');
INSERT INTO Employee VALUES('Priyanka', 'Dewangan', 20000, 'HR');
INSERT INTO Employee VALUES('Preety', 'Tiwari', 30000, 'HR');
INSERT INTO Employee VALUES('Tarun', 'Mallick', 40000, 'IT');
INSERT INTO Employee VALUES('Anurag', 'Mohanty', 50000, 'IT');
GO

Creating ASP.NET Core MVC Web Application

I am creating a new ASP.NET Core Web Application using Model-View-Controller Project Template targeting .NET 6. To create an ASP.NET Core Web Application with the MVC Project template. First, open Visual Studio 2022 and click the Create a new project tab, as shown in the image below.

Once you click on the Create a new Project tab, it will open the following Create a new Project window. From this window, select C#, All platforms, and Web from respective dropdowns as highlighted below. Then select the ASP.NET Core Web App (Model-View-Controller) project template and click the Next button, as shown in the image below.

Once you click on the Next button, it will open the Configure Your New Project window. Here, you must provide the necessary information to create a new ASP.NET Core project. First, give an appropriate name for your project (MVCusingEFCoreDBFirst), set the location where you want to create this project, and the solution name for the ASP.NET Core Web application. And finally, click on the Create button, as shown in the image below.

Once you click on the Create button, it will open the following Additional Information window. Here, you must select Framework – .NET 6.0 (Long-term support), Authentication type – None. You also need to check the Configure for HTTPS and Do not use top-level statements check boxes, and finally, click on the Create button as shown in the image below.

Once you click the Create Button, the project will be created with the Web Application (Model-View-Controller), i.e., MVC template with the following folder and file structure.

Once you create the ASP.NET Core MVC Project, we need to Install Entity Framework Core in our Application from NuGet.

Installing Entity Framework Core in ASP.NET Core MVC Application

Entity Framework Core is not part of the .NET Core Framework. So, we need to install Entity Framework Core using NuGet packages to use Entity Framework. To Install Entity Framework Core using NuGet packages, right-click on the Project and then click the Manage NuGet Packages option from the context menu, as shown in the image below.

Once you select Manage NuGet Packages, it will open the “NuGet Package Manager” window, as shown in the image below. Select the Browse Tab, then search for Microsoft.EntityFrameworkCore.SqlServer and then select Microsoft.EntityFrameworkCore.SqlServer Package and select the Framework Version that you want to Install. By default, the latest version will be selected, and currently, the latest version of Microsoft.EntityFrameworkCore.SqlServer package is 7.0.9, and finally, click on the Install button as shown in the below image.

Once you click on the Install Button, a preview window will pop up showing the list of dependent packages that will be installed. Click on the OK button as shown in the below image.

Once you click on the OK button, it will open a License Acceptance pop-up, and you need to click on the “I Accept” button, as shown in the below image.

Once you click on the I Accept button, it will install Microsoft.EntityFrameworkCore.SqlServer Package within the Packages folder, which you can find inside the Dependencies folder of your project, as shown in the below image. That means Microsoft.EntityFrameworkCore.SqlServer Package is installed.

It will also add Package Reference in the application project file. Next, we need to install Microsoft.EntityFrameworkCore.Tools package in the same way. Go to the NuGet Package Manager window and search for Microsoft.EntityFrameworkCore.Tools package, then select Microsoft.EntityFrameworkCore.Tools package. Select the latest stable version and click the Install button, as shown in the image below. The latest stable package at the time of creating this content is 7.0.9.

Once you click on the Install Button, a preview window will pop up showing the list of dependent packages that will be installed. Click on the OK button as shown in the below image.

Once you click on the OK button, it will open the License Acceptance pop-up, and you need to click on the “I Accept” button, as shown in the below image.

Once you click on the I Accept button, it will install Microsoft.EntityFrameworkCore.Tools Package within the Packages folder, which you can find inside the Dependencies folder of your project. After successfully installing the packages, they can be verified from Solution Explorer under the Dependencies => Packages, as shown in the image below.

So, we have installed the Required Entity Framework Core Packages into our ASP.NET Core MVC Web Applications. Next, we need to create the DbContext class.

Creating and Implementing DB Context

Now, we will see how to create the Context and Entity classes for an existing database in Entity Framework Core. Creating Context and Entity classes for an existing database is called Database-First Approach.

Entity Framework Core does not support Visual Designer for DB Model and Wizard to create the entity and context classes similar to Entity Framework 6. So, we need to do reverse engineering using the Scaffold-DbContext command. Reverse engineering is the process of scaffolding DbContext class and entity type classes based on a database schema.

This Scaffold-DbContext command creates entity and context classes based on the schema of the existing database, and we need to do this using Package Manager Console (PMC) tools.

Scaffold-DbContext Command:

Entity Framework Core uses Scaffold-DbContext Command to create Context and Entities based on your existing database. The following parameters can be specified with Scaffold-DbContext in the Package Manager Console:

Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>] [-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]

Let’s use the scaffold command to create the DBContext class and entity type classes for the Company Database and Employee table. We are not going to use all the parameters. The following three parameters are going to be used.

[-Connection]:

Using this parameter, we need to set the database connection string. Let’s first get the database’s connection string, which we require to run the scaffold command. In our case, the following is the connection string.

“Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=Company; Trusted_Connection=True; TrustServerCertificate=True; “

[-Provider]:

As we will communicate with the SQL Server database, the provider for the SQL Server database is Microsoft.EntityFrameworkCore.SqlServer.

[-OutputDir]:

The -OutputDir parameter specifies the directory where we want to generate all the classes. In our example, we want to generate all classes (Entity and DBContext class) within the Models folder.

So, our Scaffold-DbContext Command is the following:

Scaffold-Dbcontext “Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=Company; Trusted_Connection=True; TrustServerCertificate=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Now, let us proceed and execute the above Scaffold-DbContext Command using the Package Manager Console. To launch Package Manager Console, select Tools => NuGet Package Manager => Package Manager Console from the menu below.

This will open the Package Manager Console. Now type the Scaffold-Dbcontext “Server=LAPTOP-6P5NK25R\SQLSERVER2022DEV; Database=Company; Trusted_Connection=True; TrustServerCertificate=True;” Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models command, select the project where you want to execute this command, then press the enter button as shown below.

Once you press the Enter button, if everything is provided correctly, you will get the following message.

The entity class (Employee.cs) and DBContext class (CompanyContext.cs) will be created in the Models folder, as shown in the image below.

Employee.cs

Let’s look at the entity class (Employee.cs). The name of the class (Employee.cs) is the same as the database table (Employee). It has created respective C# properties for every column in the Employee table. The column’s data type matches the data type of the respective property of the Employee model.

using System;
using System.Collections.Generic;

namespace MVCusingEFCoreDBFirst.Models;

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

    public string FirstName { get; set; } = null!;

    public string? LastName { get; set; }

    public decimal? Salary { get; set; }

    public string? Department { get; set; }
}

CompanyContext.cs

Let’s look at the DBContext (CompanyContext) class.

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace MVCusingEFCoreDBFirst.Models;

public partial class CompanyContext : DbContext
{
    public CompanyContext()
    {
    }

    public CompanyContext(DbContextOptions<CompanyContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Employee> Employees { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlServer("Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV; Database=Company; Trusted_Connection=True; TrustServerCertificate=True;");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Employee>(entity =>
        {
            entity.HasKey(e => e.Id).HasName("PK__Employee__3214EC07E4671ABD");

            entity.ToTable("Employee");

            entity.Property(e => e.Department).HasMaxLength(50);
            entity.Property(e => e.FirstName).HasMaxLength(50);
            entity.Property(e => e.LastName).HasMaxLength(50);
            entity.Property(e => e.Salary).HasColumnType("numeric(18, 0)");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

If we look carefully at the DBContext class, there is a warning, as shown below.

#warning: To protect potentially sensitive information in your connection string, move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from the configuration – see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.

We are getting the above warning because of the connection string in the Context class. Having the connection in the code file is not a good programming practice. Let’s move the connection string to the appsettings.json file. So, open the appsettings.json file and copy and paste the following code. You can see, additionally, we have added the connection string.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV; Database=Company; Trusted_Connection=True; TrustServerCertificate=True;"
  }
}

Then modify the OnConfiguring (DbContextOptionsBuilder optionsBuilder) method of the CompanyContext.cs file as shown below. Here, we have removed everything and made it an empty method.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{ 
}

Adding DBContext Service

As discussed in .NET 6, we need to add services and middleware components within the Main method only. We need to add the following DBContext Service

builder.Services.AddDbContext<CompanyContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString(“DBConnection”)));

So, modify the Main method of the Program.cs class as shown below to add the DBContext service.

using Microsoft.EntityFrameworkCore;
using MVCusingEFCoreDBFirst.Models;

namespace MVCusingEFCoreDBFirst
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var builder = WebApplication.CreateBuilder(args);

            // Add services to the container.
            builder.Services.AddControllersWithViews();

            //Adding DBContext Service
            builder.Services.AddDbContext<CompanyContext>(options => options.UseSqlServer(builder.Configuration.GetConnectionString("DBConnection")));

            var app = builder.Build();

            // Configure the HTTP request pipeline.
            if (!app.Environment.IsDevelopment())
            {
                app.UseExceptionHandler("/Home/Error");
                // The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
                app.UseHsts();
            }

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

            app.UseRouting();

            app.UseAuthorization();

            app.MapControllerRoute(
                name: "default",
                pattern: "{controller=Home}/{action=Index}/{id?}");

            app.Run();
        }
    }
}

Display the Employee List on the Page.

It’s time to use the Employee Model, CompanyContext class in our ASP.NET Core MVC Web Application using Entity Framework Code First Approach. So, let us first add one MVC Controller with views, using Entity Framework with the name Employee. To do so, right-click on the Controllers folder and then select Add => Controller from the context menu, as shown in the below image.

This will open the following Add New Scaffolded Item window. Here, you need to select MVC Controller with views, using the Entity Framework template, and then click the Add button as shown in the image below.

Once you click the Add button, it will open the “Add MVC Controller with views, using Entity Framework” window. Here, from the Model class dropdown, select the “Employee” class. From the Data context class, select CompanyContext. Keep all other selections as they are, i.e., Generate views, Reference script libraries, and Use a layout page options checked. Provide the Controller name as Employees and then click the Add button, as shown in the image below.

Once you click the Add button, EmployeeController.cs controller will be created with its action methods and views. Here, the Employees folder will be created within the Views folder. Create, Delete, Details, Edit, and Index views will be created within the Employees folder, as shown in the image below.

EmployeeController.cs:

The Employee Controller will be created with the required action methods to perform the database CRUD Operations against the Employee table. The EmployeesController constructor takes the CompanyContext object as a parameter and then initializes the _context object, and then, using that _context object, we perform the database CRUD Operations. If you look at the Index action method, it is using that _context object to fetch all the Employees from the Employee table.

Now, build the solution and then navigate to the Employees/Index URL as shown in the below image, and you should see the list of employees.

You can now click the Create New, Edit, Details, and Delete buttons to perform the operations on the Employee database table.

Performing the CRUD Operations

Add

Click the “Create New” button on the Employees Index Page, as shown in the image below.

Once you click the Create New button, it will navigate to the Employees/Create URL to Create a new Employee, as shown below. Enter the Employee details and click the Create button, as shown in the image below.

Once you click on the Create button, it will navigate back to the Employee Index page, and there you can see the newly created Employee also listed as shown in the below image.

Verify that the new employee has been added to the Employee table of the Company database by executing the select query as shown in the image below.

Similarly, you can perform the rest of the database operations. That’s it. We have completed the ASP.NET Core MVC Web Application using EF Core DB First Approach to perform Database CRUD Operations.

In the next article, I will discuss Bundling and Minification in ASP.NET Core MVC Applications with Examples. In this article, we learned about the Entity Framework Core Database First Approach. We created a sample application to use the Entity Framework Core Database First (EF Core DB First) Approach in the ASP.NET Core MVC Web Application. We performed the database CRUD operations on a table. I hope you enjoy this ASP.NET Core MVC Web Application using EF Core DB First Approach article.