CRUD Operations in ASP.NET Core MVC using Entity Framework Core

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

CRUD Operations in ASP.NET Core MVC using Entity Framework Core

In this article, I will discuss How to Perform Database CRUD Operations in ASP.NET Core MVC Web Application using Entity Framework Core (EF Core Code First) Approach with Multiple Database tables. Please read our Entity Framework Basics article series before proceeding to this article.

CRUD Operations in ASP.NET Core MVC using EF Core

CRUD (Create, Read, Update, Delete) operations are fundamental for most web applications, and ASP.NET Core MVC with Entity Framework Core provides a streamlined approach for implementing these operations.

Let us see one real-time example of performing database CRUD operations in an ASP.NET Core MVC Application using Entity Framework Core with multiple database tables. Let us create one complete example of managing employees and departments in an organization. In this example, we will use ASP.NET Core MVC, Entity Framework Core Code First Approach, and SQL Server Database. 

Step 1: Project Setup

Create a New ASP.NET Core MVC Project: Open Visual Studio. Create a new project (CRUDinCoreMVC) and select the ASP.NET Core Web App (Model-View-Controller) template.

Install The Packages: Once you have created the Project, as we are going to work with the SQL Server Database, we need to install the following two NuGet Packages, which are required for Entity Framework Core:

Microsoft.EntityFrameworkCore.SqlServer:

This package is the Entity Framework Core database provider for Microsoft SQL Server and Azure SQL Database. It is necessary for any ASP.NET Core application that intends to use these databases. The primary functions of this package include:

• Database Connection: It allows EF Core to establish connections to a SQL Server database.
• SQL Generation: It translates the LINQ queries from your application into SQL queries that the SQL Server can understand.
• Schema Generation: It is responsible for translating the entity data models into SQL Server database schemas. This is particularly useful when creating and migrating databases.
• Optimizing Performance: The package includes SQL Server-specific optimizations, enhancing the performance of data access operations.

Microsoft.EntityFrameworkCore.Tools:

This package provides additional tools for working with Entity Framework Core, which is especially useful during development. These tools enhance the development experience and simplify many database-related tasks. Key features include:

• Migrations: Commands for creating and managing migrations are included, which help evolve the database schema over time without losing data.
• Database Update: This tool allows applying migrations to update the database schema directly from the command line or via the Package Manager Console in Visual Studio.
• Database Scaffolding: It can reverse engineer a database schema to create entity model classes and a DbContext based on an existing database, which is particularly useful in database-first scenarios.
• Script Generation: Generate SQL scripts from migrations, which can be useful for manual reviews or when deploying databases through scripts.

You can install the above two Packages using NuGet Package Manager for Solution or by using Package Manager Console. Please execute the following command using the Package Manager Console to install the Packages.

Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer

Your project structure should look like the one shown below after Step 1. However, your package might be different when you read this article.

Step 2: Define Models

Next, we need to define the models according to our application’s data structure. As we will manage the Employee and Department data, let’s define two models: Employee and Department. So, create a class file named Department.cs within the Models folder and then copy and paste the following code:

namespace CRUDinCoreMVC.Models
{
    public class Department
    {
        public int DepartmentId { get; set; }
        public string Name { get; set; }

        public List<Employee> Employees { get; set; }
    }
}

Create another class file named Employee.cs within the Models folder, and then copy and paste the following code:

namespace CRUDinCoreMVC.Models
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Position { get; set; }
        public int DepartmentId { get; set; }

        public Department? Department { get; set; }
    }
}

By default, we have implemented one-to-many relationships between Employees and Departments. An employee belongs to a single department, and one department can have many employees.

Step 3: Configure the Database Connection

Instead of hard-coding the connection string with the DbContext class, we will store the connection string in the appsettings.json file. So, add your database connection string in the appsettings.json file as follows:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },

  "AllowedHosts": "*",
  "ConnectionStrings": {
    "EFCoreDBConnection": "Server=LAPTOP-6P5NK25R\\SQLSERVER2022DEV;Database=EFCoreMVCDB;Trusted_Connection=True;TrustServerCertificate=True;"
  }
}

Step 4: Configure DbContext

Create a DbContext class for the application to manage the database. In Entity Framework Core (EF Core), the DbContext class is the component that serves as a bridge between your application’s code and the database. It plays an important role in managing the interactions with the underlying database in an efficient and performance-oriented manner. So, add a class named EFCoreDBContext.cs and then copy and paste the following code.

using Microsoft.EntityFrameworkCore;
using System.Diagnostics.Metrics;

namespace CRUDinCoreMVC.Models
{
    public class EFCoreDbContext : DbContext
    {
        //Constructor calling the Base DbContext Class Constructor
        public EFCoreDbContext(DbContextOptions<EFCoreDbContext> options) : base(options)
        {
        }

        //OnConfiguring() method is used to select and configure the data source
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
        }

        //Adding Domain Classes as DbSet Properties
        public DbSet<Employee> Employees { get; set; }
        public DbSet<Department> Departments { get; set; }
    }
}

Role of DbContext in EF Core:

• Querying: DbContext provides the necessary methods and properties to query the database. It converts LINQ (Language Integrated Query) expressions into SQL queries that the database can understand.
• Saving Changes: It tracks changes made to objects and applies them to the database when SaveChanges() or SaveChangesAsync() is called. This includes translating the changes into insert, update, or delete commands.
• Model Mapping: It maps classes to database tables and properties to table columns through the ModelBuilder class used in the OnModelCreating method. 
• Change Tracking: DbContext tracks entities’ states during their lifecycle. This tracking ensures that only actual changes are updated in the database during a save operation, which helps optimize database access and improve performance.

Step 5: Configure the Database Connection:

Next, we need to configure the connection string with the DbContex class. Please add the following code to the Program class. The following code configures the DbContext in an ASP.NET Core application using Entity Framework Core (EF Core).

//Configure the ConnectionString and DbContext class
builder.Services.AddDbContext<EFCoreDbContext>(options =>
{
        options.UseSqlServer(builder.Configuration.GetConnectionString("EFCoreDBConnection"));
});

Code Explanation:

• builder.Services: builder.Services refer to the IServiceCollection provided by the ASP.NET Core host builder. This collection registers services that the application will use, including platform features like MVC, logging, DI containers, and more.
• AddDbContext<EFCoreDbContext>: The AddDbContext is an extension method provided by EF Core that registers the DbContext as a service in the DI (Dependency Injection) container. In this case, it’s registering EFCoreDbContext. This method also ensures that the lifecycle of the DbContext is managed correctly, typically as a scoped service. This means a new instance of the DbContext is created for each request.
• Lambda Configuration (options => …): The lambda expression is used to configure options for the DbContext. These options control how the DbContext behaves and interacts with the underlying database.
• options.UseSqlServer: UseSqlServer is a method that specifies SQL Server as the database provider for EF Core. This method also tells EF Core to translate the LINQ queries and other data operations into SQL that is compatible with SQL Server.
• builder.Configuration.GetConnectionString(“EFCoreDBConnection”): builder.Configuration provides access to the application’s configuration, typically including settings from files like appsettings.json, environment variables, and other configuration sources. GetConnectionString is a method that retrieves a connection string by its key (“EFCoreDBConnection” in this case) from the application’s configuration. This connection string contains the necessary information for connecting to the SQL Server database (like server address, database name, credentials, etc.).

Step 6: Database Migration

Next, we need to generate the EF Core migrations and update the database schema. Open the Package Manager Console and Execute the add-migration and update-database commands as follows. You can give your migration any name. Here, I am giving it EFCoreDBMig1. The name you are giving it should not be given earlier.

With this, our Database with Departments and Employees table is created, as shown in the below image:

Before proceeding and performing the database CRUD Operations, let us first insert some master data into the Departments database table by executing the following INSERT SQL statements, which we will use while performing the Employee CRUD operation.

INSERT INTO Departments VALUES ('IT');
INSERT INTO Departments VALUES ('HR');
INSERT INTO Departments VALUES ('Payroll');

Step 7: Creating EmployeesController to Perform CRUD Operations Using EF Core:

Next, create an Empty MVC Controller named EmployeesController within the Controllers folder. Here, I am going to Scaffold Controllers and Views, which will automatically generate the Actions and Views using the Entity Framework Core for us to perform the CRUD Operations. Later, we will modify the auto-generated actions and views as per our requirements. Please follow the below steps to Scaffold Controllers and Views.

Right-click on the Controllers folder and then select Add => Controller from the context menu, which will open the following Add New Scaffold Item window. Here, please select MVC Controller with views, using Entity Framework option and then click on the Add button as shown in the image below:

Once you click on the Add button, the following window will open. Here, provide the Model Class as Employee, provide the DbContext Class as EFCoreDBContext, Keep the rest of the setting for Views as it is, provide the Controller name as EmployeesController, and then click on the Add button as shown in the below image:

Once you click the Add button, it will take some time to create the controller, all the action methods to perform the database CRUD Operations, and the corresponding views for us. The following is the auto-generated Employees Controller class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
using Microsoft.EntityFrameworkCore;
using CRUDinCoreMVC.Models;

namespace CRUDinCoreMVC.Controllers
{
    public class EmployeesController : Controller
    {
        private readonly EFCoreDbContext _context;

        public EmployeesController(EFCoreDbContext context)
        {
            _context = context;
        }

        // GET: Employees
        public async Task<IActionResult> Index()
        {
            var eFCoreDbContext = _context.Employees.Include(e => e.Department);
            return View(await eFCoreDbContext.ToListAsync());
        }

        // GET: Employees/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null || _context.Employees == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees
                .Include(e => e.Department)
                .FirstOrDefaultAsync(m => m.EmployeeId == id);
            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }

        // GET: Employees/Create
        public IActionResult Create()
        {
            ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "DepartmentId");
            return View();
        }

        // POST: Employees/Create
        // To protect from overposting attacks, enable the specific properties you want to bind to.
        // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("EmployeeId,Name,Email,Position,DepartmentId")] Employee employee)
        {
            if (ModelState.IsValid)
            {
                _context.Add(employee);
                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "DepartmentId", employee.DepartmentId);
            return View(employee);
        }

        // GET: Employees/Edit/5
        public async Task<IActionResult> Edit(int? id)
        {
            if (id == null || _context.Employees == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees.FindAsync(id);
            if (employee == null)
            {
                return NotFound();
            }
            ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "DepartmentId", employee.DepartmentId);
            return View(employee);
        }

        // POST: Employees/Edit/5
        // To protect from overposting attacks, enable the specific properties you want to bind to.
        // For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, [Bind("EmployeeId,Name,Email,Position,DepartmentId")] Employee employee)
        {
            if (id != employee.EmployeeId)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                try
                {
                    _context.Update(employee);
                    await _context.SaveChangesAsync();
                }
                catch (DbUpdateConcurrencyException)
                {
                    if (!EmployeeExists(employee.EmployeeId))
                    {
                        return NotFound();
                    }
                    else
                    {
                        throw;
                    }
                }
                return RedirectToAction(nameof(Index));
            }
            ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "DepartmentId", employee.DepartmentId);
            return View(employee);
        }

        // GET: Employees/Delete/5
        public async Task<IActionResult> Delete(int? id)
        {
            if (id == null || _context.Employees == null)
            {
                return NotFound();
            }

            var employee = await _context.Employees
                .Include(e => e.Department)
                .FirstOrDefaultAsync(m => m.EmployeeId == id);
            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }

        // POST: Employees/Delete/5
        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> DeleteConfirmed(int id)
        {
            if (_context.Employees == null)
            {
                return Problem("Entity set 'EFCoreDbContext.Employees'  is null.");
            }
            var employee = await _context.Employees.FindAsync(id);
            if (employee != null)
            {
                _context.Employees.Remove(employee);
            }
            
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }

        private bool EmployeeExists(int id)
        {
          return (_context.Employees?.Any(e => e.EmployeeId == id)).GetValueOrDefault();
        }
    }
}

Now, if you verify the Views folder, then you will see the views for the Employees controller as shown in the below image:

Note: The Scaffolded Controllers will contain methods for CRUD operations. As we progress, we will customize these methods and views, as per our application requirements.

Creating Department Controller:

The way we have created the EmployeesController, in the same way, we can also create the DepartmentsController. So, please follow the same steps and create the Departments Controller. While creating the Controller, you must provide the Model class as Department.

Testing 

Run the application and test all CRUD operations for both employees and departments. Ensure that the department selection works correctly when creating or editing an employee. Before testing, first, modify the Default controller and action to Employee and Index in the Program class as follows:

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

Now, if you run the application and go to the Employees/Create URL, you will see it displays the Department ID in the Drop-Down List instead of the Department name, as shown in the image below.

To display the Department name instead of ID, modify the Create Action Method (both Get and Post) of the EmployeesController as follows:

// GET: Employees/Create
public IActionResult Create()
{
    ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "Name");
    return View();
}

// POST: Employees/Create
// To protect from overposting attacks, enable the specific properties you want to bind to.
// For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create([Bind("EmployeeId,Name,Email,Position,DepartmentId")] Employee employee)
{
    if (ModelState.IsValid)
    {
        _context.Add(employee);
        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(Index));
    }
    ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "Name", employee.DepartmentId);
    return View(employee);
}

Now, run the application and navigate to the Employees/Create URL, and you should see it showing the Department name in the dropdown list. Let us create one employee and click the Create button, as shown in the image below.

Once you click on the Create button, the new employee will be added to the database. Then, it will redirect you to the Index page, which will display all the Employees, as shown in the image below. We have created only one employee, and that employee’s information will be displayed here.

If you look at the Index view, it is showing the Department as 2. So, instead of showing the Department ID, we need to show the Department name. To do so, modify the Index view of the Employees controller as follows:

@model IEnumerable<CRUDinCoreMVC.Models.Employee>

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

<h1>Index</h1>

<p>
    <a asp-action="Create">Create New</a>
</p>
<table class="table">
    <thead>
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.Name)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Email)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Position)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Department)
            </th>
            <th></th>
        </tr>
    </thead>
    <tbody>
        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.Name)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Email)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Position)
                </td>
                <td>
                    @if (item.Department != null)
                    {
                        @Html.DisplayFor(modelItem => item.Department.Name)
                    }
                </td>
                <td>
                    <a asp-action="Edit" asp-route-id="@item.EmployeeId">Edit</a> |
                    <a asp-action="Details" asp-route-id="@item.EmployeeId">Details</a> |
                    <a asp-action="Delete" asp-route-id="@item.EmployeeId">Delete</a>
                </td>
            </tr>
        }
    </tbody>
</table>

Now, run the application, and it should display the Department name in the Index view as shown in the below image:

To see the Employee details, click the Details button as shown in the above image. Once you click on the Details button, it will open the following Details view.

As you can see, the Department ID is also displayed here. To show the Department name instead of the Department ID, please modify the Details view of the Employee controller as follows:

@model CRUDinCoreMVC.Models.Employee

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

<h1>Details</h1>

<div>
    <h4>Employee</h4>
    <hr />
    <dl class="row">
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Email)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Email)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Position)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Position)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Department)
        </dt>
        <dd class = "col-sm-10">
            @if (Model.Department != null)
            {
                @Html.DisplayFor(model => model.Department.Name)
            }
        </dd>
    </dl>
</div>
<div>
    <a asp-action="Edit" asp-route-id="@Model?.EmployeeId">Edit</a> |
    <a asp-action="Index">Back to List</a>
</div>

Now, run the application and see the Details of the Employee and it should show the Department name as expected, as shown in the image below:

Now, click the Edit button either from the Details view or Index view to edit an employee. Once you click the Edit button, the following view with prepopulated employee information will open.

Further, if you notice, it shows the Department ID in the Dropdown List. Instead of showing ID, if you want to show the Name of the Department, then please modify the Edit action method (both Get and Post) of the Employees controller as follows:

// GET: Employees/Edit/5
public async Task<IActionResult> Edit(int? id)
{
    if (id == null || _context.Employees == null)
    {
        return NotFound();
    }

    var employee = await _context.Employees.FindAsync(id);
    if (employee == null)
    {
        return NotFound();
    }
    ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "Name", employee.DepartmentId);
    return View(employee);
}

// POST: Employees/Edit/5
// To protect from overposting attacks, enable the specific properties you want to bind to.
// For more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(int id, [Bind("EmployeeId,Name,Email,Position,DepartmentId")] Employee employee)
{
    if (id != employee.EmployeeId)
    {
        return NotFound();
    }

    if (ModelState.IsValid)
    {
        try
        {
            _context.Update(employee);
            await _context.SaveChangesAsync();
        }
        catch (DbUpdateConcurrencyException)
        {
            if (!EmployeeExists(employee.EmployeeId))
            {
                return NotFound();
            }
            else
            {
                throw;
            }
        }
        return RedirectToAction(nameof(Index));
    }
    ViewData["DepartmentId"] = new SelectList(_context.Departments, "DepartmentId", "Name", employee.DepartmentId);
    return View(employee);
}

Now, it should display the Department Name in the dropdown list. Let us modify the Employee Department to IT and Position to DBA and click the Save button, as shown in the image below.

Once you update the data and click on the Save button, it will save the data into the database and redirect to the Index view, where you can see the updated data, as shown in the image below.

Now, click the Delete button, as shown in the above image, to Remove the Employee from the database. Once you click the Delete button, the following Delete View will open.

As you can see in the above image, it is showing the Department ID value instead of the Department Name. To display the Department Name, modify the Delete view of the Employees controller as follows:

@model CRUDinCoreMVC.Models.Employee

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

<h1>Delete</h1>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Employee</h4>
    <hr />
    <dl class="row">
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Name)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Name)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Email)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Email)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Position)
        </dt>
        <dd class = "col-sm-10">
            @Html.DisplayFor(model => model.Position)
        </dd>
        <dt class = "col-sm-2">
            @Html.DisplayNameFor(model => model.Department)
        </dt>
        <dd class = "col-sm-10">
            @if(Model.Department != null)
            {
                @Html.DisplayFor(model => model.Department.Name)
            }
        </dd>
    </dl>
    
    <form asp-action="Delete">
        <input type="hidden" asp-for="EmployeeId" />
        <input type="submit" value="Delete" class="btn btn-danger" /> |
        <a asp-action="Index">Back to List</a>
    </form>
</div>

With the above changes, run the application, go to the Index View, and click the Delete button. This time, it should display the Department Name instead of the Department ID, as shown in the image below. Click on the Delete button to delete the Employee from the database.

Once you click the Delete button, it will delete the employee and then navigate to the Index view.

While creating and updating an employee, the dropdown list name is displayed as DepartmentId. If you want to display Department Name instead of DepartmentId, modify the Employee model as follows. Here, you can see we are decorating the DepartmentId property with a Display Attribute and setting the Name Property as Department Name.

using System.ComponentModel.DataAnnotations;

namespace CRUDinCoreMVC.Models
{
    public class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Email { get; set; }
        public string Position { get; set; }
        [Display(Name ="Department Name")]
        public int DepartmentId { get; set; }

        public Department? Department { get; set; }
    }
}

Note: Similarly, you can test the Department Controller and Views and Perform the database CRUD Operations.

Enhancements

As per your business requirements, you can make the following enhancements:

• Validation: Implement data annotations for model validation.
• Exception Handling: Include proper error handling in your application.
• User Interface: Use CSS and JavaScript to improve the UI.
• Advanced Features: Consider adding features like search, sorting, and pagination.

In the next article, I will discuss How to Implement the Repository Design Pattern in ASP.NET Core MVC with Entity Framework Core. In this article, I explain How to Implement Database CRUD Operations in ASP.NET Core MVC Application using Entity Framework Core. I hope you enjoy this ASP.NET Core MVC Web Application using EF Core article.