Working With Multiple Tables Using LINQ Join In MVC

Farhan Ahmed

7y287.4k20100Article

MVCLINQ JoinLINQ Join In MVCMultiple Table Using LINQ Join

20 Comments

Join the conversation! Your thoughts help the community grow.

Mark Tabor6y ago
How to use left join as i saw the above linq query and it gives null if one of my reference table is having no related record in main table
Marius Vasile6y ago
I wish I saw this article two days ago, it clears a lot. Thank you
- Farhan Ahmed6y ago
  Thank you Marius vasile
Ademola Afees6y ago
Good, it works for me........ but i want use "where" condition,
- Marius Vasile6y ago
  You can use where condition, for example "from e in employees.WHERE(e => e.condition == value)".
Dominik Narożyński6y ago
You are great ! You helped me in my thesis !!
Chittaranjan Swain7y ago
Nice article....
Rajagopal Anbalagan7y ago
How to write CRUD operation with this method? Can you explain in detail
- Rajagopal Anbalagan7y ago
  Instead of using Model at @foreach (var item in Model) in view, we have to use ViewModel? @foreach (var item in ViewModel) is that correct way.
- Farhan Ahmed7y ago
  Using ViewModel u can do it.
ajay prajapati7y ago
Hi sir table 1 and 2 Where name of table
Win Thura Kyaw Kyaw7y ago
How to write CRUD operation with this method?
- Farhan Ahmed7y ago
  Use ViewModel
- Win Thura Kyaw Kyaw7y ago
  Any solution?
Paul Milleson7y ago
Hello! What did we gain with step 6, please explain. Also, when building: CS0246 C# (In HomeController) The type or namespace name DBEntities could not be found (are you missing a using directive or an assembly reference?) Where was the type: DBEntities created ? (or supposed to have been created)
- Farhan Ahmed7y ago
  Hi Paul step 6 for adding bootstrap 4 for design and DBEntities could not error mean you did not add namespace just put cursor on DBEntities press ctr and dot find namespace hit enter.
Jeff Simons7y ago
Thank you Farhan! This was very helpful for me.
Fanambinantsoa Andriniaina7y ago
Hello! I need explanation for step 6 please. I get an error launching my application: Compiler Error Message: CS0234: The type or namespace name 'ViewModel' does not exist in the namespace 'aeronautical_tax.Models' (are you missing an assembly reference?)
- Farhan Ahmed7y ago
  Hold ctn and dot it will show namespace choose and hit enter.
Mohammad Ullah7y ago
How to insert data in it

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.

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.

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