Posted on 2/12/2021 10:33:52 AM by Admin

Inner Join in Linq

In SQL Server when we perform an inner join, then only the matching elements from the 2 Tables are included in the result set and Non - Matching elements are simply excluded from the result set as shown in the below diagram.

Inner Join in Sql Server
Inner Join in SQL Server

But with Linq we don't query a database tables directly , rather we query the Collection of Objects .The Entity Framework converts the tables into collection of objects , and we simply query those collections (DbSets) and then the Linq provider translates our query to corresponding T-SQL.

How to think
How to think when working with LINQ

The entity Framework will generate following domain model on the basis of Employee and Department table mentioned above.

Entity Model generated by EF
Entity Model generated by EF

In Linq we have Join operator to perform inner join , If we have 2 collections, and we perform an inner join, then only the matching elements from the 2 collections are included in the result set. Non - Matching elements are excluded.

Join in Method Syntax

Entity Model generated by EF
Entity Model generated by EF

      public static void Main()
        {
            OrganizationContext db = new OrganizationContext();
           var result= db.Departments.Join(db.Employees, x => x.Id, x => x.DepartmentId, (d, e) => new
            {
                departmentName = d.Name,
                EmployeeName = e.Name,
                Salary = e.Salary
            });

           foreach (var item in result)
           {
               Console.WriteLine("{0}|{1}|{2}",item.departmentName,item.EmployeeName,item.Salary);
           }
            Console.ReadLine();
        }

Like all other Linq Operators Join is an extension method on IQueryalbe<T> , which means we can apply Join operator an any Collection type.In the above example of join query,
  • Department collection is outer sequence because query starts from it.
  • First parameter in the Join method is used to specify the inner sequence which is Departments in the above example.
  • Second and third parameter of Join method is used to specify a field whose value should be matched using lambda expression in order to include an element in the result. The key selector for the outer sequence x =>x.Id indicates that the Id field of each element of Department should be matched with the key of inner sequence x=>x.DepartmentId. If the value of both the key field is matched then include that element in the result.
  • The last parameter in the Join method is an expression to formulate the result. In the above example, the result selector includes the Department's Name, Employee's Name, and Employee's Salary property of both the sequence.

Output: Notice that, in the output we don't have any record of Department's Id 3 (BA Department) . This is because the BA department does not have a matching Employee in the Employee collection. In other words, there is no Employee in the BA department so the BA department has been excluded from the result. So this is effectively an inner join.

Join in Query Syntax

Join operator in query syntax works a little differently than method syntax. It requires four parameters namely outer sequence, inner sequence, key selector, and result selector. 'on' keyword is used for key selector where left side of 'equals' operator becomes the outerKeySelector and right side of 'equals' becomes the innerKeySelector.

Example 2 : Convert Example1 to Sql-Like syntax


      public static void Main()
        {
            OrganizationContext db = new OrganizationContext();
            var result = from d in db.Departments
                         join e in db.Employees
                         on d.Id equals e.DepartmentId
                         select new
                         {
                             departmentName = d.Name,
                             EmployeeName = e.Name,
                             Salary = e.Salary
                         };

           foreach (var item in result)
           {
               Console.WriteLine("{0}|{1}|{2}",item.departmentName,item.EmployeeName,item.Salary);
           }
            Console.ReadLine();

        }