Working With Multiple Tables Using LINQ Join In MVC

Today, in this article, we will see the step-by-step process of joining multiple tables using LINQ Join and displaying records in a View. So, let's start.

Step 1

Open SQL server and create a database and 3 tables. I have created three tables as - Employee, Department, and Incentive respectively.

 

Employee Table

CREATE TABLE [dbo].[Employee](
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Gender] [char](10) NULL,
    [Age] [int] NULL,
    [Position] [nvarchar](50) NULL,
    [Salary] [int] NULL,
    [HireDate] [datetime] NULL,
    [Department_Id] [int] NULL,
    [Incentive_Id] [int] NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Department Table

CREATE TABLE [dbo].[Department](
    [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
    [DepartmentName] [nvarchar](50) NULL,
PRIMARY KEY CLUSTERED
(
    [DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Incentive Table

CREATE TABLE [dbo].[Incentive](
    [IncentiveId] [int] IDENTITY(1,1) NOT NULL,
    [IncentiveAmount] [int] NULL,
PRIMARY KEY CLUSTERED
(
    [IncentiveId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Step 2

Open Visual Studio 2015, click on "New Project", and create an empty web application project.

 

After clicking on the "New Project", one window will appear. Select Web from the left panel, choose ASP.NET Web Application, give a meaningful name to your project, and then click on "OK", as shown in the below screenshot.

After clicking on OK, one more window will appear; choose "Empty", check on "MVC" checkbox, and click OK, as shown in the below screenshot.

After clicking OK, the project will be created with the name of WorkingWithMultipleDataTable_Demo.

Step 3

Add Entity Framework now. For that, right-click on Models folder, select Add, then select New Item.

 

You will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory, you can give any name) and click "Add".

After you click on "Add a window", the wizard will open. Choose EF Designer from the database and click "Next".

After clicking on "Next", a window will appear. Choose New Connection. Another window will appear. Add your server name - if it is local, then enter dot (.). Choose your database and click "OK".

The connection will be added. If you wish, save the connection name as you want. You can change the name of your connection below. It will save the connection in the web config. 

Now, click "Next".

After clicking on NEXT, another window will appear. Choose the database table name as shown in the below screenshot and click "Finish".

Entity Framework gets added and the respective class gets generated under the Models folder.

Step 4

Right-click the Controllers folder, select Add, then choose Controller, as shown in below screenshot.

A window will appear. Choose MVC5 Controller-Empty and click "Add".

After clicking on "Add", another window will appear with DefaultController. Change the name to HomeController and click "Add". The HomeController will be added under the Controllers folder. Don’t change the Controller suffix for all controllers, change only the highlight, and instead of Default, just change Home; as shown in the below screenshot.

Complete code for Home Controller

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WorkingWithMultipleDataTable_Demo.Models;

namespace WorkingWithMultipleDataTable_Demo.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            using (DBEntities db=new DBEntities())
            {
                List<Employee> employees = db.Employees.ToList();
                List<Department> departments = db.Departments.ToList();
                List<Incentive> incentives = db.Incentives.ToList();

                var employeeRecord = from e in employees
                                     join d in departments on e.Department_Id equals d.DepartmentId into table1
                                     from d in table1.ToList()
                                     join i in incentives on e.Incentive_Id equals i.IncentiveId into table2
                                     from i in table2.ToList()
                                     select new ViewModel
                                     {
                                         employee=e,
                                         department=d,
                                         incentive=i
                                     };
                return View(employeeRecord);
            }
        }
    }
}

Step 5

Create a ViewModel Class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WorkingWithMultipleDataTable_Demo.Models
{
    public class ViewModel
    {
        public Employee employee { get; set; }
        public Department department { get; set; }
        public Incentive incentive { get; set; }
    }
}

Step 6

Click Tools >> NuGet Package Manager and choose "Manage NuGet Packages for Solution".

Step 7

Right-click on the index action method in Controller. The "Add View" window will appear with default index name unchecked (use a Layout page), and click on "Add", as shown in the below screenshot. The View will be added in the Views folder under Home folder with the name index.

Step 8

Design the View with HTML, .cshtml, and bootstrap 4 classes.

Complete Index View code

@model IEnumerable<WorkingWithMultipleDataTable_Demo.Models.ViewModel>
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <script type="text/javascript" src="@Url.Action("~/scripts/jquery-3.3.1.min.js")"></script>
    <script type="text/javascript" src="@Url.Action("~/scripts/bootstrap.min.js")"></script>
</head>
<body>
    <div class="container-fluid py-4">
        <h4 class="text-center text-uppercase">Employee List</h4>
        <table class="table table-striped table-bordered">
            <thead class="bg-dark text-white">
                <tr>
                    <th>Name</th>
                    <th>Position</th>
                    <th>Salary</th>
                    <th>Hire Date</th>
                    <th>Department</th>
                    <th>Incentive</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model)
                {
                    <tr>
                        <td>@item.employee.Name</td>
                        <td>@item.employee.Position</td>
                        <td>@item.employee.Salary</td>
                        <td>@item.employee.HireDate</td>
                        <td>@item.department.DepartmentName</td>
                        <td>@item.incentive.IncentiveAmount</td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</body>
</html>

Output