Group join in LINQ
Group Join
A join clause with an into expression is called a group join.
A group join breaks up the joined table into sequences of objects each matching/corresponding to an object from the other table.
In my previous article I have explained how to use Simple Key Join in LINQ.
In this article we will discuss how we can use group join. In this join two or more tables/objects are joined with an into clause.
Lets take an example of Employee and project and discuss it.
Here I have declared 2 classes Employee and Project.
Using group I would like to show list of employees project wise.
Employee Class
public class Employee
{
public int EmployeeId { get; set; }
public string EmployeeName { get; set; }
public int ProjectId { get; set; }
public Employee()
{
}
public Employee(int EmployeeId, string EmployeeName, int ProjectId)
{
this.EmployeeId = EmployeeId;
this.EmployeeName = EmployeeName;
this.ProjectId = ProjectId;
}
public List<Employee> GetEmployee()
{
List<Employee> lstEmployee = new List<Employee>();
lstEmployee.Add(new Employee(1, "Kamal", 1));
lstEmployee.Add(new Employee(2, "Rohit", 2));
lstEmployee.Add(new Employee(3, "Sumit", 3));
lstEmployee.Add(new Employee(4, "Sanjay", 2));
lstEmployee.Add(new Employee(5, "Najmul", 2));
lstEmployee.Add(new Employee(6, "Smith", 1));
lstEmployee.Add(new Employee(7, "Kiran", 3));
lstEmployee.Add(new Employee(8, "Suzain", 1));
return lstEmployee;
}
}
Project Class
public class Project
{
public int ProjectId { get; set; }
public string ProjectName { get; set; }
public Project()
{
}
public Project(int ProjectId, string ProjectName)
{
this.ProjectId = ProjectId;
this.ProjectName = ProjectName;
}
public List<Project> GetProject()
{
List<Project> lstProject = new List<Project>();
lstProject.Add(new Project(1, "GoAirlines"));
lstProject.Add(new Project(2, "UniHomes"));
lstProject.Add(new Project(3, "TravAfrica"));
lstProject.Add(new Project(4, "MahindraMahindra"));
return lstProject;
}
}
Above I have declared 2 Methods GetEmployee() and GetProject(). GetEmployee() method will return a list of employee and GetProject() will return a list of Project.
In the method ShowGroupJoinExample() below I have added a join condition between Project and Employee on ProjectId key and grouped employees, project wise and using into put the result into g.
Here into key word is used for grouping the employee object by Project.
public static void ShowGroupJoinExample()
{
var objEmp = new Employee();
var objProj = new Project();
try
{
var employee = objEmp.GetEmployee();
var project = objProj.GetProject();
var Query = from e in employee
join p in project on e.ProjectId equals p.ProjectId
group e.EmployeeName by p.ProjectName into g
select new { Projects = g.Key, Employees = g };
foreach (var q in Query)
{
Console.WriteLine("Project : " + q.Projects + "\n");
Console.WriteLine("============================\n");
foreach (var e in q.Employees)
{
Console.WriteLine("Employee : " + e.ToString() + "\n");
}
Console.WriteLine("\n--------------------------------------------\n");
}
Console.ReadKey();
}
finally
{
if (objEmp == null)
objEmp = null;
if (objProj == null)
objProj = null;
}
}
OutPut
Happy reading.........