Working with Multiple tables in MVC

Working with multiple tables in MVC

In this article, we will discuss how to working with multiple tables in MVC application using entity framework. We are going to work with the below two tables

1. Department 2. Employee 

Use the below SQL script to create and populate Department and Employee tables

-- Create Department Table
Create table Department
(
  Id int primary key identity,
  Name nvarchar(50)
)
GO

-- Insert some test data into Department table
Insert into Department values('IT')
Insert into Department values('HR')
Insert into Department values('Marketing')
GO

-- Create Employee Table
Create table Employee
(
  EmployeeId int Primary Key Identity(1,1),
  Name nvarchar(50),
  Gender nvarchar(10),
  City nvarchar(50),
  DepartmentId int
)
GO


-- Add Foreign Key into Employee Table Reference to the Department Table
Alter table Employee
add foreign key (DepartmentId)
references Department(Id)
GO

-- Insert Some Test data into Employee Table
Insert into Employee values('Pranaya','Male','Mumbai',1)
Insert into Employee values('Anurag','Male','Hyderabad',3)
Insert into Employee values('Priyanka','Female','Bangalore',3)
Insert into Employee values('Subrat','Male','Hyderabad',2)
Insert into Employee values('Sudhanshu','Male','Mumbai',1)
Insert into Employee values('Preety','Female','Bangalore',2)
Insert into Employee values('Sandeep','Male','Hyderabad',1)
Insert into Employee values('Sambit','Male','Bangalore',2)
Insert into Employee values('Hina','Female','Mumbai',1)
GO
Let’s Understand our business Requirements:

Display all the departments from the Department table. The Department names should be rendered as hyperlinks.

On clicking the department name link all the employees in that particular department should be displayed. The employee names should be rendered as hyperlinks.

When the user clicks on the employee name link the full details of the employee should be displayed. 

A link should also be provided on the employee full details page to navigate back to the Employee list page. Along the same lines, a link should also be provided on the employee list page to navigate back to the Departments list page. 

The below image gives you the overall workflow of our requirement 

multiple tables in MVC application using entity framework

  Note: We are going to work with the same example that we started in our previous two articles that are

Entity Framework in ASP.NET MVC

Generating hyperlinks using Action Link HTML helper

So please read the above two articles before proceeding to this article.

To implement the above example first we need to update the EmployeeDataModel.edmx file.

Update the EDMX file

Double click on the EmployeeDataModel.edmx file which is in the Models folder. Once you click on the edmx file the following screen will open

multiple tables in MVC application using entity framework

Right-click anywhere in the edmx file and click on update model from the database as shown below

multiple tables in MVC application using entity framework

Then select the add button and then select the Department table as shown below.

multiple tables in MVC application using entity framework

Next, select the Refresh button and select the Employee table and click on Finish as shown below

multiple tables in MVC application using entity framework

At that time if it will give you an error that Salary Property is not mapped then just select the salary property from the Employee Model of the edmx file, right click on it and then click on delete from the model as shown below

multiple tables in MVC application using entity framework

That’s it. Save the edmx file and build the solution.

Let’s have a look of the files that are generated and modified by Entity Framework.

multiple tables in MVC application using entity framework

Department.cs (This file is added by Entity Framework)
namespace CRUD_OperationsInMVC.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Department
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Department()
        {
            this.Employees = new HashSet<Employee>();
        }
    
        public int Id { get; set; }
        public string Name { get; set; }
    
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<Employee> Employees { get; set; }
    }
}
Employee.cs (This file is modified by Entity Framework)
namespace CRUD_OperationsInMVC.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class Employee
    {
        public int EmployeeId { get; set; }
        public string Name { get; set; }
        public string Gender { get; set; }
        public string City { get; set; }
        public Nullable<int> DepartmentId { get; set; }
    
        public virtual Department Department { get; set; }
    }
}
EmployeeDataModel.Context.cs (This file is modified by Entity framework)
namespace CRUD_OperationsInMVC.Models
{
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    
    public partial class EmployeeDBContext : DbContext
    {
        public EmployeeDBContext()
            : base("name=EmployeeDBContext")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<Employee> Employees { get; set; }
        public virtual DbSet<Department> Departments { get; set; }
    }
}

These are the changes done by entity framework.

Add Department Controller

Right click on the “Controllers” folder and add a MVC5 Empty Controller with name = DepartmentController. Copy and paste the following code.

namespace CRUD_OperationsInMVC.Controllers
{
    public class DepartmentController : Controller
    {
        public ActionResult Index()
        {
            EmployeeDBContext dbContext = new EmployeeDBContext();
            List<Department> listDepartments = dbContext.Departments.ToList();
            return View(listDepartments);
        }
    }
}
Adding Index View Of Department Controller:

 Right-click on the Index() action method in DepartmentController class and select “Add View” from the context menu. Set all the default values as it is.

Copy and paste the following code in Index.cshtml view file in Department folder

@using CRUD_OperationsInMVC.Models;
@model IEnumerable<Department>
<div style="font-family:Arial">
    @{
        ViewBag.Title = "Departments List";
    }

    <h2>Departments List</h2>
    <ul>
        @foreach (Department department in @Model)
        {
            <li>@Html.ActionLink(department.Name, "Index", "Employee", new { departmentId = department.Id }, null)</li>
        }
    </ul>
</div>

Add “departmentId” parameter to Index() action method in “EmployeeController” class that is present in “EmployeeController.cs” file in “Controllers” folder. Use the “departmentId” parameter to filter the list of employees as shown below.

After changes Employee Controller looks as shown below.
namespace CRUD_OperationsInMVC.Controllers
{
    public class EmployeeController : Controller
    {
        public ActionResult Index(int departmentId)
        {
            EmployeeDBContext dbContext = new EmployeeDBContext();
            List<Employee> employees = dbContext.Employees.Where(emp => emp.DepartmentId == departmentId).ToList();
            return View(employees);
        }
        public ActionResult Details(int id)
        {
            EmployeeDBContext dbContext = new EmployeeDBContext();
            Employee employee = dbContext.Employees.Single(x => x.EmployeeId == id);
            return View(employee);
        }
    }
}

Copy and paste the following line in “Index.cshtml” that is present in “Employee” folder in “Views” folder. With this change we are able to generate an action link to redirect the user to a different controller action method.

@Html.ActionLink(“Back to Department List”, “Index”, “Department”) 

Modify the Details,cshtml file that is present in Employee Folder.

@model CRUD_OperationsInMVC.Models.Employee
@{

    ViewBag.Title = "Employee Details";
}
<h2>Employee Details</h2>
<table style="font-family:Arial">
    <tr>
        <td>Employee ID:</td>
        <td>@Model.EmployeeId </td>
    </tr>
    <tr>
        <td>Name:</td>
        <td>@Model.Name</td>
    </tr>
    <tr>
        <td>Gender:</td>
        <td>@Model.Gender</td>
    </tr>
    <tr>
        <td>City:</td>
        <td>@Model.City</td>
    </tr>
</table>

<p>
   @Html.ActionLink("Back to Employee List", "Index", new { departmentId = @Model.DepartmentId })  
</p>

Change the RouteConfig file as shown below where we provide the default Route as Index Action Method of Department Controller.

namespace CRUD_OperationsInMVC
{
    public class RouteConfig
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");

            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Department", action = "Index", id = UrlParameter.Optional }
            );
        }
    }
}

That’s it we have done with our implementation. Now run the application and see everything is working as expected or not.

In the next article, I will discuss how to use Business Object as Model in ASP.NET MVC application.

SUMMARY In this article, I try to explain how to use multiple tables in MVC application using entity framework step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.