In this blog, I’m explaining about joins in LINQ in .Net

There are different types of LINQ joins.

1.      Inner join

2.      Left join

3.      Cross join

4.      Group join

Inner join

Inner join returns only those records or rows that match or exists in both the tables.

Example:

var innerjoin = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId             select new { d.id, d.FirstName, rl.RoleName, rl.RoleId }).ToList();

Left join

Left join  returns all  records or rows from left table from  right table  returns  only match  records. If there are no columns matching  in the right  table it returns NULL values. It is mandatory to use “INTO” keyword  and “DefaultIFEmpty()” method.

Example:

var emp = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId into t from lj in t.DefaultIfEmpty()                                     select new { d.id, d.FirstName, roleid = (int?)d.RoleId, lj.RoleName }).ToList();

Cross Join

Cross join is a Cartesian join means Cartesian product of both the tables.

This join does not need any condition to join two tables. This join returns

records or rows that are multiplication of record number from both the

tables means each row on left table will related to each row of right table.

Example:

var cjoin = (from d in db.EmployeeDetails from o in db.RoleTables select new { d.id, d.FirstName, o.RoleId, o.RoleName }).ToList();

Group Join

When a join use a “INTO” expression, then it is called a group join.

Example:

var gjoin = (from d in db.RoleTables join o in db.EmployeeDetails on d.RoleId equals o.RoleId group d by new { d.RoleId, d.RoleName } into grp orderby grp.Count() select new { grp.Key.RoleId, grp.Key.RoleName, count = grp.Count() }).ToList();

 Example

namespace LINQTask
{
    class Program
    {
        static void Main(string[] args)
        {
         DataClasses1DataContext db = new DataClasses1DataContext();
          //inner joining
            Console.WriteLine("Inner joining");
            var innerjoin = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId select new { d.id, d.FirstName, rl.RoleName, rl.RoleId }).ToList();
            foreach (var d in innerjoin)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", d.id, d.FirstName.Trim(), d.RoleId, d.RoleName);
            }
            //Left joining
            Console.WriteLine("Left Joining");
            var emp = (from d in db.EmployeeDetails join rl in db.RoleTables on d.RoleId equals rl.RoleId into t from lj in t.DefaultIfEmpty() select new { d.id, d.FirstName, roleid = (int?)d.RoleId, lj.RoleName }).ToList();
            foreach (var d in emp)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", d.id, d.FirstName.Trim(), d.roleid, d.RoleName);
            }
            //Cross joining
            Console.WriteLine("Cross Joining");
            var cjoin = (from d in db.EmployeeDetails from o in db.RoleTables select new { d.id, d.FirstName, o.RoleId, o.RoleName }).ToList();
            foreach (var cobj in cjoin)
            {
                Console.WriteLine("Employee Id:{0},Name:{1},Role Id:{2},Role Name:{3}", cobj.id, cobj.FirstName.Trim(), cobj.RoleId, cobj.RoleName);
            }
 
            //Group join
            Console.WriteLine("Group joining");
            var gjoin = (from d in db.RoleTables join o in db.EmployeeDetails on d.RoleId equals o.RoleId group d by new { d.RoleId, d.RoleName } into grp orderby grp.Count() select new { grp.Key.RoleId, grp.Key.RoleName, count = grp.Count() }).ToList(); ;
            foreach (var cobj in gjoin)
                Console.WriteLine("Role Id:{0},Role Name:{1},count:{2}", cobj.RoleId, cobj.RoleName.Trim(), cobj.count);
 
            Console.ReadKey();
 
        }
    }
}