none
Implementing outer joins using IEnumerable<T> RRS feed

  • Question

  • I am trying to implement the outer join using IEnumerable<T>.DEfaultIfEmpy(). But not sure where went wrong. Help me to achieve this.

    Sample Code:

     var microsoft = new Company() { CompanyName = "Microsoft" };
                this.CompanyList.Add(microsoft);
                var google = new Company() { CompanyName = "Google" };
                this.CompanyList.Add(google);
                var apple = new Company() { CompanyName = "Apple" };
                this.CompanyList.Add(apple);
                var samsung = new Company() { CompanyName = "Samsung" };
                this.CompanyList.Add(samsung);

                this.EmployeeList.Add(new Employee(microsoft.CompanyID) { EmployeeFirstName = "Bill", EmployeeLastName = "Gates", Age = 58 });
                this.EmployeeList.Add(new Employee(microsoft.CompanyID) { EmployeeFirstName = "Paul", EmployeeLastName = "Allen", Age = 61 });
                this.EmployeeList.Add(new Employee(microsoft.CompanyID) { EmployeeFirstName = "Steven", EmployeeLastName = "Ballmer", Age = 58 });
                this.EmployeeList.Add(new Employee(google.CompanyID) { EmployeeFirstName = "Larry", EmployeeLastName = "Page", Age = 41 });
                this.EmployeeList.Add(new Employee(google.CompanyID) { EmployeeFirstName = "Sergey", EmployeeLastName = "Brin", Age = 40 });
                this.EmployeeList.Add(new Employee(apple.CompanyID) { EmployeeFirstName = "Stephen", EmployeeLastName = "Wozniak", Age = 64 });
                this.EmployeeList.Add(new Employee(apple.CompanyID) { EmployeeFirstName = "Ronald ", EmployeeLastName = "Wayne ", Age = 80 });
                this.EmployeeList.Add(new Employee(apple.CompanyID) { EmployeeFirstName = "Steven", EmployeeLastName = "Jobs", Age = 58 });
                this.EmployeeList.Add(new Employee(Guid.NewGuid()) { EmployeeFirstName = "Reza", EmployeeLastName = "Hedayati", Age = 31 });

    Method 1: 

    var leftDefaultSyntax = from emp in EmployeeList
                                        join comp in CompanyList on emp.CompanyID equals comp.CompanyID into EmpComp
                                        from EmpCompRes in EmpComp.DefaultIfEmpty()
                                        select new
                                        {
                                            EmployeeId = emp.CompanyID,
                                            EmployeeName = string.Format("{0},{1}", emp.EmployeeFirstName, emp.EmployeeLastName),
                                            Age = emp.Age,
                                            CompanyId = EmpCompRes == null ? Guid.Empty : EmpCompRes.CompanyID,
                                            CompanyName = EmpCompRes == null ? string.Empty : EmpCompRes.CompanyName
                                        };
                foreach(var empComp in leftDefaultSyntax)
                    Console.WriteLine("Name:{0}, Age:{1}, Company:{2}", empComp.EmployeeName, empComp.Age, empComp.CompanyName);

    Method 2:

                

    List<Result> leftJoin = null;
                leftJoin = EmployeeList.Join(CompanyList, Emp => Emp.CompanyID, Com => Com.CompanyID, (Emp, Comp) => new
                {
                    EmployeeID = Emp.EmployeeID,
                    EmployeeName = string.Format("{0},{1}", Emp.EmployeeLastName, Emp.EmployeeFirstName),
                    Age = Emp.Age,
                    CompanyID = Comp.CompanyID,
                    CompanyName = Comp.CompanyName
                }).DefaultIfEmpty().Select(data => new Result
                {
                    EmployeeID = data.EmployeeID,
                    EmployeeName = data.EmployeeName,
                    Age = data.Age,
                    CompanyID = data == null ? Guid.Empty : data.CompanyID,
                    CompanyName = data == null ? string.Empty : data.CompanyName
                }).ToList<Result>();

                foreach (Result res in leftJoin)
                    Console.WriteLine("Name:{0}, Age:{1}, Company:{2}", res.EmployeeName, res.Age, res.CompanyName);

    Method 1 is giving expected result, where Method 2 not. Help me to achieve the Method 1 result using Method 2.

    Wednesday, April 27, 2016 6:42 AM

Answers

  • Hi Shaji Kumar VK,

    You could use groupjoin method to achieve it. and I create a demo as below for your reference.

    var result = EmployeeList.GroupJoin
                    (
                        CompanyList,
                        left => left.CompanyID,
                        right => right.CompanyID,
                        (left, right) => {
                            var mr = right.FirstOrDefault();
                            return new
                            {
                                EmployeeId = left.CompanyID,
                                EmployeeName = string.Format("{0},{1}", left.EmployeeFirstName, left.EmployeeLastName),
                                Age = left.Age,
                                CompanyId = mr == null ? Guid.Empty : mr.CompanyID,
                                CompanyName = mr == null ? string.Empty : mr.CompanyName
                            };
                        }
                    );
    
                foreach (var empComp in result)
                    Console.WriteLine("Name:{0}, Age:{1}, Company:{2}", empComp.EmployeeName, empComp.Age, empComp.CompanyName);

    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.

    Friday, April 29, 2016 9:06 AM
    Moderator

All replies

  • Hi Shaji Kumar VK,

    This forum is about the C# programming language, IDE, libraries, samples, and tools, as your issue is more related to the LINQ Development, we help you move it to the LINQ development forum for better support.

    Thank you for your understanding.

    Best Regards,

    Albert Zhang

    Thursday, April 28, 2016 9:16 AM
  • Hi Shaji Kumar VK,

    You could use groupjoin method to achieve it. and I create a demo as below for your reference.

    var result = EmployeeList.GroupJoin
                    (
                        CompanyList,
                        left => left.CompanyID,
                        right => right.CompanyID,
                        (left, right) => {
                            var mr = right.FirstOrDefault();
                            return new
                            {
                                EmployeeId = left.CompanyID,
                                EmployeeName = string.Format("{0},{1}", left.EmployeeFirstName, left.EmployeeLastName),
                                Age = left.Age,
                                CompanyId = mr == null ? Guid.Empty : mr.CompanyID,
                                CompanyName = mr == null ? string.Empty : mr.CompanyName
                            };
                        }
                    );
    
                foreach (var empComp in result)
                    Console.WriteLine("Name:{0}, Age:{1}, Company:{2}", empComp.EmployeeName, empComp.Age, empComp.CompanyName);

    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.

    Friday, April 29, 2016 9:06 AM
    Moderator
  • Thanks Cole Wu,

        It worked.

    Thursday, May 5, 2016 5:34 PM