Home > DeveloperSection > Blogs > LINQ Joins in C#

LINQ Joins in C#


.NET .NET  LINQ 
Ratings:
0 Comment(s)
 648  View(s)
Rate this:

LINQ Joins in C#

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();

 

        }

    }

}

 

 

 

 

 


Don't want to miss updates? Please click the below button!

Follow MindStick