How to use inner Join in Linq c#

By HH Team | Views: 280

Join is required when we need to extract the data from those data sources which has no direct relation between them, but both data sources share some common property on behalf of them we extract the data from those data sources. These data sources could be a list of object or table of any database.

In LINQ, there is two way of implementing inner join i.e using Query expression or Extension methods that exist in System.Linq namespace.

Inner join compares the specified key from both data source for equality by using equals keyword. And, if a match found it shows that element into the result otherwise not.
Here we have three entity EmployeeDetails, Transport and EmployeeRouteDetail. EmployeeDetails contain the basic information of employees where the transport class contains details about the vehicle and their route detail. EmployeeRouteDetail class, we going to use this to generate the desired data to show the user after applying the join clause.

Using the Query expression:-
Here is the example of the Query expression.

The following example shows a simple inner equijoin. This query produces the sequence of the EmployeeRouteDetail element. This will show a list of those employees with vehicle number whose zipcode match with the zipcode of the vehicle data source.

 

            //Query expression
            var vehicleList = from vehicle in transportVehicle
                              join emp in employeeDetails on vehicle.Zipcode equals emp.Zipcode
                              select new EmployeeRouteDetail
                              {
                                  FirstName = emp.FirstName,
                                  LastName = emp.LastName,
                                  City = emp.City,
                                  VehicleNumber = vehicle.VehicleNumber
                              };

Using the Extension Method:-

Syntax of the Join method is given below:

public static System.Linq.IQueryable<TResult> Join<TOuter,TInner,TKey,TResult> 
(this System.Linq.IQueryable<TOuter> outer, System.Collections.Generic.IEnumerable<TInner> inner,
System.Linq.Expressions.Expression<Func<TOuter,TKey>> outerKeySelector, 
System.Linq.Expressions.Expression<Func<TInner,TKey>> innerKeySelector, 
System.Linq.Expressions.Expression<Func<TOuter,TInner,TResult>> resultSelector);

This method takes 5 parameters.
outer IQueryable<TOuter>
The first sequence to join.
inner IEnumerable<TInner>
The sequence to join to the first sequence.
outerKeySelector Expression<Func<TOuter,TKey>>
A function to extract the join key from each element of the first sequence.
innerKeySelector Expression<Func<TInner,TKey>>
A function to extract the join key from each element of the second sequence.
resultSelector Expression<Func<TOuter,TInner,TResult>>
A function to create a result element from two matching elements.

Here is an example of the Extension Method.
//Extension Method
            var vehicleList = employeeDetails.Join(transportVehicle,
                emp => emp.Zipcode,
                vehicle => vehicle.Zipcode,
                (emp, vehicle) => new EmployeeRouteDetail
                {
                    FirstName = emp.FirstName,
                    LastName = emp.LastName,
                    City = emp.City,
                    VehicleNumber = vehicle.VehicleNumber
                });

In this example, we have used the Join method instead of a query expression to achieve the same result as previously.
Below is the complete Implementation of inner join in MVC and bind the result data in a view.

using System.Web.Mvc;
using System;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using System.Linq;

namespace TutorialHack.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            //Here we are generating the first Data source
            //List of employees
            List<EmployeeDetails> employeeDetails = new List<EmployeeDetails>
            {
                new EmployeeDetails{FirstName="Mikal", LastName="Hack", EmpCode="HH001", City="Ash", Zipcode="CT3", Mobile="7911423456"},
                new EmployeeDetails{FirstName="Axal", LastName="Decan", EmpCode="HH023", City="Ab Kettleby", Zipcode="LE14", Mobile="7913344234"},
                new EmployeeDetails{FirstName="Olivia", LastName="Smith", EmpCode="HH062", City="Abberley", Zipcode="WR6", Mobile="7913344542"},
                new EmployeeDetails{FirstName="Lucas", LastName="Allen", EmpCode="HH012", City="Abbess Roding", Zipcode="CM5", Mobile="791346366"},
                new EmployeeDetails{FirstName="Alexander", LastName="Henderson", EmpCode="HH112", City="Abbey Hulton", Zipcode="DET2", Mobile="7913545546"},
                new EmployeeDetails{FirstName="Daniel", LastName="Brown", EmpCode="HH122", City="Abbots Bromley", Zipcode="WS15", Mobile="853545546"},
                new EmployeeDetails{FirstName="Matthew", LastName="Henderson", EmpCode="HH132", City="Abbots Bromley", Zipcode="WS15", Mobile="675545546"},
            };

            //Here we are generating the second Data Source
            //List of Transport Routes
            List<Transport> transportVehicle = new List<Transport>
            {
                new Transport{City="Ash", Zipcode="CT3", VehicleNumber="VH01"},
                new Transport{City="Abbey Hulton", Zipcode="DET2", VehicleNumber="VH05"},
                new Transport{City="Abbots Bromley", Zipcode="WS15", VehicleNumber="VH06"},
                new Transport{City="Aberford", Zipcode="LS25", VehicleNumber="VH07"},
                new Transport{City="Abinger Hammer", Zipcode="RH5", VehicleNumber="VH08"},
                new Transport{City="Abingworth", Zipcode="RH20", VehicleNumber="VH09"},
            };

            //Here we have two lists, and there is no relation between them.
            //But both Data source share two common property "City" And "ZipCode".

            //So to get the list of those employees for which location transport is available we use Inner join.

            //Query expression
            var vehicleList = from vehicle in transportVehicle
                              join emp in employeeDetails on vehicle.Zipcode equals emp.Zipcode
                              select new EmployeeRouteDetail
                              {
                                  FirstName = emp.FirstName,
                                  LastName = emp.LastName,
                                  City = emp.City,
                                  VehicleNumber = vehicle.VehicleNumber
                              };


            //Extension Method
            var vehicleList = employeeDetails.Join(transportVehicle,
                emp => emp.Zipcode,
                vehicle => vehicle.Zipcode,
                (emp, vehicle) => new EmployeeRouteDetail
                {
                    FirstName = emp.FirstName,
                    LastName = emp.LastName,
                    City = emp.City,
                    VehicleNumber = vehicle.VehicleNumber
                });
            return View(vehicleList.ToList());
        }
    }

    public class EmployeeRouteDetail
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string City { get; set; }
        public string VehicleNumber { get; set; }
    }

    public class EmployeeDetails
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string EmpCode { get; set; }
        public string City { get; set; }
        public string Zipcode { get; set; }
        public string Mobile { get; set; }
    }

    public class Transport
    {
        public string City { get; set; }
        public string Zipcode { get; set; }
        public string VehicleNumber { get; set; }
    }
}

The view contains the following code to display data in the table.

@model List<TutorialHack.Controllers.EmployeeRouteDetail>
@{
    ViewBag.Title = "Home Page";
}
<style>
    table {
        font-family: arial, sans-serif;
        border-collapse: collapse;
        width: 100%;
    }

    td, th {
        border: 1px solid #dddddd;
        text-align: left;
        padding: 8px;
    }

    tr:nth-child(even) {
        background-color: #dddddd;
    }
</style>

<div>
    @{
        <table>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>City</th>
                <th>Vehicle Number</th>
            </tr>
            @foreach (var item in Model)
            {
                <tr>
                    <td>@item.FirstName</td>
                    <td>@item.LastName</td>
                    <td>@item.City</td>
                    <td>@item.VehicleNumber</td>
                </tr>
            }
        </table>
    }
</div>

By using both ways we get the same output-

data

Thank you for your feedback!