User Id :    Password :      New Member   Forgot Password
 
Composite key join in LINQ
Description In this article I will show how to use composite key join to perform join operation in which you want to use more than one key to define a match.   No. of Views     1928
  Rating     0
Author Najmul Hoda   Posted On     12 Jul 2011
Tags ADO.NET,LINQ Basic,LINQ to Object,.NET Frameworks    

Sample Code   Download Code

A composite key join is used to perform join operation in which you want to use more than one key to define a match.

 

In this article we will discuss Composite Key join in LINQ. In this join two or more tables/objects are joined with a more than one keys(column).

Lets take an example of Employee, Project and ProjectLocation and discuss it.

In this example we will work on the query below.

 

"List all the employee name, project name and their project location where employee belong to a project and a project has a project location defined."

 

Here I have declared 3 classes Employee, Project and ProjectLocation.

 

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 Kant", 1));
            lstEmployee.Add(new Employee(2, "Rohit Sharma", 2));
            lstEmployee.Add(new Employee(3, "Sumit Gupta", 3));
            lstEmployee.Add(new Employee(4, "Sanjay Singh", 2));
            lstEmployee.Add(new Employee(5, "Najmul Hoda", 2));
            lstEmployee.Add(new Employee(6, "John Smith", 1));
            lstEmployee.Add(new Employee(7, "Kiran Verma", 3));
            lstEmployee.Add(new Employee(8, "Suzain Khan", 1));

            return lstEmployee;
        }
    }

 

 Project Class

 public class Project
    {
        public int ProjectId { get; set; }
        public string ProjectName { get; set; }
        public int LocationId { get; set; }

        public Project()
        {
        }

        public Project(int ProjectId, string ProjectName, int LocationId)
        {
            this.ProjectId = ProjectId;
            this.ProjectName = ProjectName;
            this.LocationId = LocationId;

        }

        public List<project> GetProject()
        {
            List<project> lstProject = new List<project>();

            lstProject.Add(new Project(1, "GoAirlines", 2));
            lstProject.Add(new Project(2, "UniHomes", 3));
            lstProject.Add(new Project(3, "TravAfrica",4));
            lstProject.Add(new Project(4, "MahindraMahindra", 1));

            return lstProject;
        }
    }


ProjectLocation Class

public class ProjectLocation
    {
        public int LocationId { get; set; }
        public string Location { get; set; }

        public ProjectLocation()
        {
        }

        public ProjectLocation(int LocationId, string Location)
        {
            this.LocationId = LocationId;
            this.Location = Location;
        }

        public List<projectlocation> GetProjectLocation()
        {
            List<projectlocation> lstProject = new List<projectlocation>();

            lstProject.Add(new ProjectLocation(1, "India"));
            lstProject.Add(new ProjectLocation(2, "UAE"));
            lstProject.Add(new ProjectLocation(3, "USA"));
            lstProject.Add(new ProjectLocation(4, "South Africa"));

            return lstProject;
        }
    }

 

Here I have declared 3 Methods GetEmployee() , GetProject() and GetProjectLocation()above. GetEmployee() method in Employee class will return a list of employee and GetProject() in Project class will return a list of Project and GetProjectLocation() in ProjectLocation class will return a list of ProjectLocation.
 

In the method CompositeKeyJoinExample() below I have added a join condition between Project, Employee and ProjectLocation on ProjectId, employeeId and LocationId keys.

public static void CompositeJoinExample()
        {
            var objEmp = new Employee();
            var objProj = new Project();
            var objLoc = new ProjectLocation();

            try
            {
                var employee = objEmp.GetEmployee();

                var project = objProj.GetProject();

                var location = objLoc.GetProjectLocation();

                var Query = from e in employee
                            from l in location
                            join p in project
                            on new { e.ProjectId, l.LocationId } equals new { p.ProjectId, p.LocationId }
                            select new { e.EmployeeName, p.ProjectName, l.Location };

                Console.WriteLine("Employee Name\tProject Name\t Location");
                Console.WriteLine("=============\t============\t ========");

                foreach (var q in Query)
                {

                    Console.WriteLine( "{0}\t{1}\t{2}" , q.EmployeeName,q.ProjectName,q.Location);
                    Console.WriteLine("\n");
                }

                Console.ReadKey();

            }
            finally
            {
                if (objEmp == null)
                    objEmp = null;

                if (objProj == null)
                    objProj = null;

                if (objLoc == null)
                    objLoc = null;
            }
        }

 

Output

 

 

About Author

About Author He is a .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

 
 
Posted By Annonymous on 03 Sep 2011 at 02:35 AM
 
You're a real deep thneikr. Thanks for sharing.
 
 
     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by