Loading

ASP.NET MVC

How to Work with Multiple Tables in ASP.NET MVC using Entity Framework?. The Complete ASP.NET MVC Developer Course 2023 [Videos].

In this Video, I am going to discuss Working with Multiple tables in ASP.NET MVC applications using Entity Framework. Please read our previous Video where we discussed Action Link HTML Helper in ASP.NET MVC application. At the end of this Video, you will understand how to work with multiple tables in ASP.NET MVC Application using Entity Framework.

Database tables used in this demo:

We are going to work with the below two tables i.e. Department and Employee.

Working with Multiple Tables in MVC using Entity Framework

Please use the below SQL script to create and populate Department and Employee tables with the required test data.

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





<br>-- First Drop the Existing Employee table<br>DROP Table Employee;<br>
-- 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
Example to understand working with multiple tables in MVC Application:

Following is our business requirement.

  1. We need to display all the departments from the Department table. The Department names should be rendered as hyperlinks. This is going to be our department list page.
  2. On clicking the department name link, all the employees in that particular department should be displayed. The employee names also here going to be rendered as hyperlinks. This is going to be our employee list page.
  3. When the user clicks on the employee name link the full details of that employee should be displayed in the browser. This is going to be our employee details page.
  4. 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 Departmentss list page. 
The following image gives you the overall workflow of our requirement 

Working with Multiple Tables in ASP.NET MVC using Entity Framework

Note: We are going to work with the same example that we started in our previous two Videos. So please read the below two Videos before proceeding to this Video.

Entity Framework in ASP.NET MVC


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.

EDMX File in Entity Framework

Right-click anywhere in the edmx file and then click on the “update model from the database” option as shown in the below image.

Updating Entity Data Model in MVC Application

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

Adding Tables in EDMX File

Next, choose the Refresh button and select the Employee table and click on the Finish button as shown in the below image.

Refresh Employee Table in EDMX File

Once you click on the Finish button, you will get an error saying Salary Property is not mapped. Simply 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 in the below image.

Deleting Property From Entity Model in EDMX File

 

Thats it. Save the edmx file and build the solution. Lets have a look of the files that are generated and modified by 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 the name DepartmentController and then Copy and paste the following code into it.

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>

In the above code, we are using the following ActionLink HTML helper extension which takes five parameters.

public static MvcHtmlString ActionLink(this HtmlHelper htmlHelper, string linkText, string actionName, string controllerName, object routeValues, object htmlAttributes);

Modify Employee Controller:

Add “departmentId” parameter to Index() action method in “EmployeeController” class. Use the “departmentId” parameter to filter the list of employees. 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.FirstOrDefault(x => x.EmployeeId == id);
return View(employee);
}
}
}

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

@model IEnumerable<CRUD_OperationsInMVC.Models.Employee>
@using CRUD_OperationsInMVC.Models;
<div style="font-family:Arial">
@{
ViewBag.Title = "Employee List";
}
<h2>Employee List</h2>
<ul>
@foreach (Employee employee in @Model)
{
<li>@Html.ActionLink(employee.Name, "Details", new { id = employee.EmployeeId })</li>
}
</ul>
@Html.ActionLink("Back to Department List", "Index", "Department")
</div>
Modify the Details.cshtml file that is present in Employee Folder.

Here, we are just removing the Salary property.

@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 }
);
}
}
}

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

See All

Comments (278 Comments)

Submit Your Comment

See All Posts

Related Posts

ASP.NET MVC / Youtube

What is MVC?

MVC is an architectural software design pattern that is used for developing interactive applications where their user interaction is involved and based on the user interaction some event handling has occurred. It is not only used for web-based applications but it can also be used for Desktop or mobile-based applications where there are user interactions involved.
28-jan-2022 /28 /278

ASP.NET MVC / Youtube

How to Creat First ASP.NET MVC Application using Visual Studio?

In this article, I am going to discuss how to create the first ASP.NET MVC Application step by step from scratch using Visual Studio 2015. You can use any version as per your choice but the step will remain the same. Please read our previous article before proceeding to this article where we gave a brief introduction to ASP.NET MVC Framework.
28-jan-2022 /28 /278

ASP.NET MVC / Youtube

What is ASP.NET MVC File and Folder Structure?

In this article, I am going to discuss the auto-generated ASP.NET MVC File and File Structure when we create a new ASP.NET MVC application. Please read our previous article before proceeding to this article where we discussed how to create ASP.NET MVC 5 application step by step from scratch.
28-jan-2022 /28 /278