none
how to distinguish left and right outer join in LINQ RRS feed

  • Question

  • please first post two sample code of left and right outer join using LINQ and after then show what is difference in coding to form a left and right outer join using LINQ. thanks
    Sunday, October 16, 2016 3:55 PM

Answers

  • Hi Mou_kolkata,

    Here is a sample about left outer join and right outer join for your reference, the code is same and just change the order of models.

    #Left Outer JOIN

    A SQL LEFT OUTER JOIN is like a standard Inner Join, but it returns results in the “Left Table” even if there is no matching result in the “Right Table”.

    #Right Outer JOIN

    A SQL LEFT OUTER JOIN is like a standard Inner Join, but it returns results in the “Right Table” even if there is no matching result in the “Left Table”.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace LeftAndRightOuterJoinDemo
    {
        class Employee
        {
            public string Name { get; set; }
            public int ID { get; set; }
            public int ProjectID { get; set; }
        }
    
        class Project
        {
            public int ProjectID { get; set; }
            public string ProjectName { get; set; }
        }
        class Program
        {
            static void Main(string[] args)
            {
                Project P1 = new Project() { ProjectID = 1, ProjectName = "UID" };
                Project P2 = new Project() { ProjectID = 2, ProjectName = "RBS" };
                Project P3 = new Project() { ProjectID = 3, ProjectName = "XYZ" };
                // Employee List
                List<Employee> ListOfEmployees = new List<Employee>();
                ListOfEmployees.AddRange((new Employee[]
                {
                    new Employee() { ID = 1, Name = "Sunil",  ProjectID = 1 },
                    new Employee() { ID = 1, Name = "Anil", ProjectID = 1 },
                    new Employee() { ID = 1, Name = "Suman", ProjectID = 2 },
                    new Employee() { ID = 1, Name = "Ajay", ProjectID = 3 },
                    new Employee() { ID = 1, Name = "Jimmy", ProjectID = 4 }}));
    
                //Project List
                List<Project> ListOfProject = new List<Project>();
                ListOfProject.AddRange(new Project[] { P1, P2, P3 });
    
                //Left join
                var Ljoin = from emp in ListOfEmployees
                            join proj in ListOfProject
                               on emp.ProjectID equals proj.ProjectID into JoinedEmpDept
                            from proj in JoinedEmpDept.DefaultIfEmpty()
                            select new
                            {
                                EmployeeName = emp.Name,
                                ProjectName = proj != null ? proj.ProjectName : null
                            };
    
                //Right outer join
                var RJoin = from proj in ListOfProject
                            join employee in ListOfEmployees
                            on proj.ProjectID equals employee.ProjectID into joinDeptEmp
                            from employee in joinDeptEmp.DefaultIfEmpty()
                            select new
                            {
                                EmployeeName = employee != null ? employee.Name : null,
                                ProjectName = proj.ProjectName
                            };
    
                //Printing result of left join
    
                Console.WriteLine("left join");
                Console.WriteLine(string.Join("\n", Ljoin.Select(emp => " Employee Name = " +
    emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));
    
                Console.WriteLine("-------------------------------------------");
    
                Console.WriteLine("right join");
    
                //printing result of right outer join
                Console.WriteLine(string.Join("\n", RJoin.Select(emp => " Employee Name = " +
    emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));
    
                Console.ReadLine();
    
    
            }
        }
    }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mou_kolkata Monday, October 17, 2016 2:29 PM
    Monday, October 17, 2016 6:43 AM
    Moderator

All replies

  • Hi Mou_kolkata,

    Here is a sample about left outer join and right outer join for your reference, the code is same and just change the order of models.

    #Left Outer JOIN

    A SQL LEFT OUTER JOIN is like a standard Inner Join, but it returns results in the “Left Table” even if there is no matching result in the “Right Table”.

    #Right Outer JOIN

    A SQL LEFT OUTER JOIN is like a standard Inner Join, but it returns results in the “Right Table” even if there is no matching result in the “Left Table”.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace LeftAndRightOuterJoinDemo
    {
        class Employee
        {
            public string Name { get; set; }
            public int ID { get; set; }
            public int ProjectID { get; set; }
        }
    
        class Project
        {
            public int ProjectID { get; set; }
            public string ProjectName { get; set; }
        }
        class Program
        {
            static void Main(string[] args)
            {
                Project P1 = new Project() { ProjectID = 1, ProjectName = "UID" };
                Project P2 = new Project() { ProjectID = 2, ProjectName = "RBS" };
                Project P3 = new Project() { ProjectID = 3, ProjectName = "XYZ" };
                // Employee List
                List<Employee> ListOfEmployees = new List<Employee>();
                ListOfEmployees.AddRange((new Employee[]
                {
                    new Employee() { ID = 1, Name = "Sunil",  ProjectID = 1 },
                    new Employee() { ID = 1, Name = "Anil", ProjectID = 1 },
                    new Employee() { ID = 1, Name = "Suman", ProjectID = 2 },
                    new Employee() { ID = 1, Name = "Ajay", ProjectID = 3 },
                    new Employee() { ID = 1, Name = "Jimmy", ProjectID = 4 }}));
    
                //Project List
                List<Project> ListOfProject = new List<Project>();
                ListOfProject.AddRange(new Project[] { P1, P2, P3 });
    
                //Left join
                var Ljoin = from emp in ListOfEmployees
                            join proj in ListOfProject
                               on emp.ProjectID equals proj.ProjectID into JoinedEmpDept
                            from proj in JoinedEmpDept.DefaultIfEmpty()
                            select new
                            {
                                EmployeeName = emp.Name,
                                ProjectName = proj != null ? proj.ProjectName : null
                            };
    
                //Right outer join
                var RJoin = from proj in ListOfProject
                            join employee in ListOfEmployees
                            on proj.ProjectID equals employee.ProjectID into joinDeptEmp
                            from employee in joinDeptEmp.DefaultIfEmpty()
                            select new
                            {
                                EmployeeName = employee != null ? employee.Name : null,
                                ProjectName = proj.ProjectName
                            };
    
                //Printing result of left join
    
                Console.WriteLine("left join");
                Console.WriteLine(string.Join("\n", Ljoin.Select(emp => " Employee Name = " +
    emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));
    
                Console.WriteLine("-------------------------------------------");
    
                Console.WriteLine("right join");
    
                //printing result of right outer join
                Console.WriteLine(string.Join("\n", RJoin.Select(emp => " Employee Name = " +
    emp.EmployeeName + ", Project Name = " + emp.ProjectName).ToArray<string>()));
    
                Console.ReadLine();
    
    
            }
        }
    }
    

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Mou_kolkata Monday, October 17, 2016 2:29 PM
    Monday, October 17, 2016 6:43 AM
    Moderator