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

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

Creating a Real-Time Example to Perform Database CRUD operation in ASP.NET Core MVC using EF Core with multiple database tables involves several components. We will go through a simplified example to illustrate the process. This example assumes you understand ASP.NET Core, Entity Framework Core, and SQL databases.

Creating a CRUD application for managing Employees and Departments in ASP.NET Core MVC using EF Core involves building a model to reflect the relationship between employees and their departments and implementing the necessary views and controllers to handle the CRUD operations. Let’s dive into an example project:

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, please install the following two NuGet Packages:

  • Microsoft.EntityFrameworkCore.Tools
  • Microsoft.EntityFrameworkCore.SqlServer

With Step1, your project structure should look as shown below:

Project Setup

Step 2: Define Models

Let’s define two models: Employee and Department.

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 the Department. 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. 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; }
    }
}

Step 5: Registering the Connection String and DbContext Class:

Next, we must configure the connection string and register the context class in the Program class. So, modify the Program class as follows:

using CRUDinCoreMVC.Models;
using Microsoft.EntityFrameworkCore;

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

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

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

            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();
        }
    }
}
Step 6: Database Migration

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

Database Migration

With this, our Database with Students database table is created, as shown in the below image:

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

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: CRUD Operations in ASP.NET Core MVC Using EF Core:
EmployeesController

Create a controller named EmployeesController. Here, I am going to Scaffold Controllers and Views, which will automatically generate the Actions and views using 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 View.

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:

MVC Controller with views, using Entity Framework

Once you click on the Add button, it will open the following window. 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:

MVC Controller with views, using Entity Framework Core

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:

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

Note: The scaffolded controllers will contain methods for CRUD operations. As we progress, we will customize these methods and views as per our application’s 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.

Create Operation in ASP.NET Core MVC using Entity Framework Core

To display the Department name instead of ID, modify the Create action method (both Get and Post) of the Home Controller 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.

Create Operation in ASP.NET Core MVC using EF Core

Once you click on the Create button, it will Add the new employee to the database, and then it will redirect you to the Index page, where it will display all the Employees as shown in the below image. We have created only one employee. That employee information will be displayed here.

Index Operation in ASP.NET Core MVC using Entity Framework Core

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. 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:

Index Operation in ASP.NET Core MVC using EF Core

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

Details Operation in ASP.NET Core MVC using Entity Framework Core

As you can see, here it is also displaying the Department ID. 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:

Details Operation in ASP.NET Core MVC using EF Core

Now, click the Edit button either from the Details view or Index view to edit an employee. Once you click on the Edit button, you will open the following Edit view with prepopulated employee information. 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 on the Save button, as shown in the image below.

Edit Operation in ASP.NET Core MVC using Entity Framework Core

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.

Edit Operation in ASP.NET Core MVC using EF Core

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

Delete Operation in ASP.NET Core MVC using Entity Framework Core

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.

CRUD Operations in ASP.NET Core MVC using EF Core

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

While creating the Employee and updating an employee, it displays the dropdown list name as DepartmentId. Instead of DepartmentId, if you want to display Department Name, modify the Employee model as follows. Here, you can see we are decorating the DepartmentId property with 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 do 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.

Leave a Reply

Your email address will not be published. Required fields are marked *