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
  1. CREATE TABLE [dbo].[Employee](
  2. [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
  3. [Name] [nvarchar](50) NULL,
  4. [Gender] [char](10) NULL,
  5. [Age] [int] NULL,
  6. [Position] [nvarchar](50) NULL,
  7. [Salary] [int] NULL,
  8. [HireDate] [datetime] NULL,
  9. [Department_Id] [int] NULL,
  10. [Incentive_Id] [int] NULL,
  11. CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
  12. (
  13. [EmployeeId] ASC
  14. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  15. ) ON [PRIMARY]
  17. GO

Department Table

  1. CREATE TABLE [dbo].[Department](
  2. [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
  3. [DepartmentName] [nvarchar](50) NULL,
  4. PRIMARY KEY CLUSTERED
  5. (
  6. [DepartmentId] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  10. GO

Incentive Table

  1. CREATE TABLE [dbo].[Incentive](
  2. [IncentiveId] [int] IDENTITY(1,1) NOT NULL,
  3. [IncentiveAmount] [int] NULL,
  4. PRIMARY KEY CLUSTERED
  5. (
  6. [IncentiveId] ASC
  7. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  8. ) ON [PRIMARY]
  10. GO

Step 2

Open Visual Studio 2015, click on "New Project", and create an empty web application project.
Working With Multiple Table Using LINQ Join In MVC

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.

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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.
Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

Working With Multiple Table Using LINQ Join In MVC

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

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.Mvc;
  6. using WorkingWithMultipleDataTable_Demo.Models;
  8. namespace WorkingWithMultipleDataTable_Demo.Controllers
  9. {
  10. public class HomeController : Controller
  11. {
  12. public ActionResult Index()
  13. {
  14. using (DBEntities db=new DBEntities())
  15. {
  16. List<Employee> employees = db.Employees.ToList();
  17. List<Department> departments = db.Departments.ToList();
  18. List<Incentive> incentives = db.Incentives.ToList();
  20. var employeeRecord = from e in employees
  21. join d in departments on e.Department_Id equals d.DepartmentId into table1
  22. from d in table1.ToList()
  23. join i in incentives on e.Incentive_Id equals i.IncentiveId into table2
  24. from i in table2.ToList()
  25. select new ViewModel
  26. {
  27. employee=e,
  28. department=d,
  29. incentive=i
  30. };
  31. return View(employeeRecord);
  32. }
  33. }
  34. }
  35. }

Step 5

Create a ViewModel Class.
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  6. namespace WorkingWithMultipleDataTable_Demo.Models
  7. {
  8. public class ViewModel
  9. {
  10. public Employee employee { get; set; }
  11. public Department department { get; set; }
  12. public Incentive incentive { get; set; }
  13. }
  14. }

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

  1. @model IEnumerable<WorkingWithMultipleDataTable_Demo.Models.ViewModel>
  2. @{
  3. Layout = null;
  4. }
  6. <!DOCTYPE html>
  8. <html>
  9. <head>
  10. <meta name="viewport" content="width=device-width" />
  11. <title>Index</title>
  12. <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
  13. <script type="text/javascript" src="@Url.Action("~/scripts/jquery-3.3.1.min.js")"></script>
  14. <script type="text/javascript" src="@Url.Action("~/scripts/bootstrap.min.js")"></script>
  15. </head>
  16. <body>
  17. <div class="container-fluid py-4">
  18. <h4 class="text-center text-uppercase">Employee List</h4>
  19. <table class="table table-striped table-bordered">
  20. <thead class="bg-dark text-white">
  21. <tr>
  22. <th>Name</th>
  23. <th>Position</th>
  24. <th>Salary</th>
  25. <th>Hire Date</th>
  26. <th>Department</th>
  27. <th>Incentive</th>
  28. </tr>
  29. </thead>
  30. <tbody>
  31. @foreach (var item in Model)
  32. {
  33. <tr>
  34. <td>@item.employee.Name</td>
  35. <td>@item.employee.Position</td>
  36. <td>@item.employee.Salary</td>
  37. <td>@item.employee.HireDate</td>
  38. <td>@item.department.DepartmentName</td>
  39. <td>@item.incentive.IncentiveAmount</td>
  40. </tr>
  41. }
  42. </tbody>
  43. </table>
  44. </div>
  45. </body>
  46. </html>

Output

Working With Multiple Table Using LINQ Join In MVC