Asp.Net EF Core Joining multiple tables

In the previous post we creates a custom model class EmployeeDetail, in this post we shall see on how to use Entity Framework Core to JOIN the Employee, Department and Country tables and populate data into the custom Model class EmployeeDetail.

We have 3 tables, the Employee table is the main table which has related data in the Department and Country tables, DepartmentId and CountryId are the Foreign Keys in the employee table.

LINQ to SQL – INNER JOIN

LINQ to SQL – INNER JOIN

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

In practical situations we will have to join more than one table to get the details for reporting.

An INNER JOIN is used to fetch the matching details from 2 or more tables.

Assume we have the following data in the Employee and Department Tables

Table: Employee

ID	NAME	Phone	Email	DepartmentID	Salery
1	Tom	123-123-1234	tom@abcsoftware.com	1	5000
2	Harry	123-123-1234	harry@abcsoftware.com	2	6000
3	Peter	111-222-3333	peter@abcsoftware.com	3	6500
4	John	111-222-3333	john@abcsoftware.com	3	7500
5	Charles	666-444-2222	charles@abcsoftware.com	NULL	6500

Table: Department

ID	NAME
1	Finance
2	Human Resources
3	IT
4	Sales
5	Marketing

The INNER JOIN SQL Query to join the 2 tables is as follows

SELECT     E.ID, E.Name, E.Phone, D.Name AS Department

FROM         Employee AS E INNER JOIN

                      Department AS D ON E.DepartmentID = D.ID

This query will fetch the following data

ID	NAME	Phone	Department
1	Tom	123-123-1234	Finance
2	Harry	123-123-1234	Human Resources
3	Peter	111-222-3333	IT
4	John	111-222-3333	IT

Now let us see how the same can be achieved using a LINQ query.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

var emp = (from e in dbContext.Employees

                       join d in dbContext.Departments

                       on e.DepartmentID equals d.ID

                       select new { e.ID, e.Name, e.Phone, Department = (d.Name) });

Related Post

What is LINQ?
LINQ to SQL
LINQ To SQL Vs ADO.Net Entity Framework
LINQ to SQL Sample
LINQ to SQL Select Query with Specific columns
LINQ to SQL Select query with Filter (WHERE Clause)
LINQ to SQL Select Query with Alias Name
LINQ to SQL - Sub Queries
LINQ to SQL – Join 2 Tables
LINQ to SQL – Select DISTINCT Values
LINQ to SQL – Select with SORT (ORDER BY)
LINQ to SQL – Select with AGGREGATE (GROUP BY)
LINQ to SQL – INNER JOIN
LINQ to SQL – LEFT OUTER JOIN
LINQ to SQL – RIGHT OUTER JOIN
LINQ to SQL – INSERT
LINQ to SQL – UPDATE
LINQ to SQL – DELETE
LINQ to SQL – Using ExecuteCommand() to Execute SQL Statements

LINQ to SQL – Join 2 Tables

LINQ to SQL – Join 2 Tables

Before writing a LINQ to SQL query, we first need to create a DataContext using the LINQ to SQL Classes template, to know more on how to create the DataContext refer to the post LINQ to SQL Sample

Once the DataContext is created we can query the Object model using LINQ queries, let us consider the Employee table which has the following structure.

In practical situations we will have to join more than one table to get the details for reporting. Assume we have the following data in the Employee and Department Tables

Table: Employee

ID	NAME	DOB	DOJ	Phone	Email	DepartmentID	Salery
1	Tom	11/11/1967	05/01/2009	123-123-1234	tom@abcsoftware.com	1	5000
2	Harry	10/07/1973	05/01/2009	123-123-1234	harry@abcsoftware.com	2	6000
3	Peter	12/07/1975	05/01/2009	111-222-3333	peter@abcsoftware.com	3	6500
4	John	05/04/1981	05/01/2009	111-222-3333	john@abcsoftware.com	3	7500
5	Charles	12/12/1973	05/01/2009	666-444-2222	charles@abcsoftware.com	4	6500

Table: Department

ID	NAME
1	Finance
2	Human Resources
3	IT
4	Sales
5	Marketing

The SQL Query to join the 2 tables is as follows

SELECT     E.ID, E.Name, E.Phone, D.Name AS Department

FROM         Employee AS E INNER JOIN

                      Department AS D ON E.DepartmentID = D.ID

This query will fetch the following data

ID	NAME	Phone	Department
1	Tom	123-123-1234	Finance
2	Harry	123-123-1234	Human Resources
3	Peter	111-222-3333	IT
4	John	111-222-3333	IT
5	Charles	666-444-2222	Sales

Now let us see how the same can be achieved using a LINQ query.

EmployeeClassesDataContext dbContext = new EmployeeClassesDataContext();

  var emp = (from e in dbContext.Employees

            from d in dbContext.Departments

            where e.DepartmentID == d.ID

            select new { e.ID, e.Name, e.Phone, Department = (d.Name)});

Alternatively we can also use the join keyword to form the query as follows.

    var emp = (from e in dbContext.Employees 

                         join d in dbContext.Departments

                         on e.DepartmentID equals d.ID

            select new { e.ID, e.Name, e.Phone, Department = (d.Name)});

Related Posts

What is LINQ?

LINQ to SQL

LINQ to SQL Sample

LINQ to SQL Select Query with Specific columns

LINQ to SQL Select query with Filter (WHERE Clause)LINQ to SQL Select Query with Alias Name

LINQ to SQL - Sub Queries