C# में LINQ और लैम्ब्डा के साथ Join का उपयोग कैसे करें?

इनर जॉइन केवल उन रिकॉर्ड्स को लौटाता है जो दोनों टेबल्स में मौजूद/मैच होते हैं। मल्टीपल टेबल्स पर कंडीशन्स के साथ जॉइन भी लागू किया जा सकता है। मल्टीपल कंडीशन्स पर जॉइन के लिए अनाम टाइप्स का उपयोग करें।

दोनों दृष्टिकोण: मेथड सिंटैक्स vs क्वेरी सिंटैक्स

1. मेथड सिंटैक्स (लैम्ब्डा)

var result = Employee.GetAllEmployees()
    .Join(Department.GetAllDepartments(),
        e => e.DepartmentID,      // आउटर की सेलेक्टर
        d => d.ID,                // इनर की सेलेक्टर
        (employee, department) => new {  // रिजल्ट सेलेक्टर
            EmployeeName = employee.Name,
            DepartmentName = department.Name
        });

2. क्वेरी सिंटैक्स (SQL-जैसा)

var result1 = from e in Employee.GetAllEmployees()
              join d in Department.GetAllDepartments()
              on e.DepartmentID equals d.ID
              select new {
                  EmployeeName = e.Name,
                  DepartmentName = d.Name
              };

पूर्ण उदाहरण

class Program {
    static void Main(string[] args) {
        // मेथड सिंटैक्स
        var result = Employee.GetAllEmployees()
            .Join(Department.GetAllDepartments(),
                e => e.DepartmentID,
                d => d.ID,
                (employee, department) => new {
                    EmployeeName = employee.Name,
                    DepartmentName = department.Name
                });
        
        foreach (var emp in result) {
            Console.WriteLine($"{emp.EmployeeName}\t{emp.DepartmentName}");
        }
        
        // क्वेरी सिंटैक्स
        var result1 = from e in Employee.GetAllEmployees()
                      join d in Department.GetAllDepartments()
                      on e.DepartmentID equals d.ID
                      select new {
                          EmployeeName = e.Name,
                          DepartmentName = d.Name
                      };
        
        foreach (var emp in result1) {
            Console.WriteLine($"{emp.EmployeeName}\t{emp.DepartmentName}");
        }
        
        Console.ReadLine();
    }
}

public class Employee {
    public int ID { get; set; }
    public string Name { get; set; }
    public int DepartmentID { get; set; }
    
    public static List<Employee> GetAllEmployees() {
        return new List<Employee> {
            new Employee { ID = 1, Name = "A", DepartmentID = 1 },
            new Employee { ID = 2, Name = "B", DepartmentID = 2 },
            new Employee { ID = 3, Name = "B", DepartmentID = 1 },
            new Employee { ID = 4, Name = "V", DepartmentID = 1 },
            new Employee { ID = 5, Name = "F", DepartmentID = 2 },
            new Employee { ID = 6, Name = "R", DepartmentID = 2 },
            new Employee { ID = 7, Name = "TT", DepartmentID = 1 },
            new Employee { ID = 8, Name = "YY", DepartmentID = 1 },
            new Employee { ID = 9, Name = "WW", DepartmentID = 2 },
            new Employee { ID = 10, Name = "QQ" }  // कोई डिपार्टमेंट नहीं
        };
    }
}

public class Department {
    public int ID { get; set; }
    public string Name { get; set; }
    
    public static List<Department> GetAllDepartments() {
        return new List<Department> {
            new Department { ID = 1, Name = "IT" },
            new Department { ID = 2, Name = "HR" },
            new Department { ID = 3, Name = "Contract" }
        };
    }
}

आउटपुट

A       IT
B       HR
B       IT
V       IT
F       HR
R       HR
TT      IT
YY      IT
WW      HR
A       IT
B       HR
B       IT
V       IT
F       HR
R       HR
TT      IT
YY      IT
WW      HR

नोट: Employee ID 10 (QQ) आउटपुट में नहीं है

क्योंकि उसका DepartmentID 0 है जो किसी डिपार्टमेंट से मैच नहीं करता - यह इनर जॉइन का व्यवहार है।

अन्य जॉइन प्रकार

लेफ्ट आउटर जॉइन (DefaultIfEmpty)

var leftJoin = from e in employees
               join d in departments on e.DepartmentID equals d.ID into deptGroup
               from d in deptGroup.DefaultIfEmpty()
               select new {
                   EmployeeName = e.Name,
                   DepartmentName = d?.Name ?? "No Department"
               };

ग्रुप जॉइन

var groupJoin = from d in departments
                join e in employees on d.ID equals e.DepartmentID into empGroup
                select new {
                    Department = d.Name,
                    Employees = empGroup.Select(e => e.Name)
                };

मल्टीपल की जॉइन (अनाम टाइप्स)

var multiKeyJoin = from e in employees
                   join d in departments 
                   on new { e.DepartmentID, e.RegionID } equals new { d.ID, d.RegionID }
                   select new { e.Name, d.Name };