[C# LINQ] inner join / left join / right join

 

inner join :

using System;
using System.Collections.Generic;
using System.Linq;

public class Person
{
    public int ID { get; set; }
    public string Name { get; set; }
}

public class Address
{
    public int ID { get; set; }
    public string Street { get; set; }
}

class Program
{
    static void Main()
    {
        List<Person> people = new List<Person>
        {
            new Person { ID = 1, Name = "Alice" },
            new Person { ID = 2, Name = "Bob" },
            new Person { ID = 3, Name = "Charlie" }
        };

        List<Address> addresses = new List<Address>
        {
            new Address { ID = 1, Street = "123 Main St" },
            new Address { ID = 2, Street = "456 Elm St" },
            new Address { ID = 4, Street = "789 Oak St" }
        };

        var query = from person in people
                    join address in addresses
                    on person.ID equals address.ID
                    select new
                    {
                        person.Name,
                        address.Street
                    };

        foreach (var result in query)
        {
            Console.WriteLine($"Name: {result.Name}, Street: {result.Street}");
        }
    }
}

left join :

• 左連接會返回左邊清單的所有元素，以及那些符合連接條件的右邊清單的元素，如果右邊清單中沒有匹配的元素，則返回null或默認值
• 在這個左連接的示例中，我們使用into子句將右邊清單的元素分組到addressGroup，然後使用from子句和DefaultIfEmpty方法來處理左連接。如果沒有匹配的地址，address會為null，我們在結果中使用條件運算符處理這種情況(沒有判斷null, 會發生null exception)。

var leftJoinQuery = from person in people
                    join address in addresses
                    on person.ID equals address.ID into addressGroup
                    from address in addressGroup.DefaultIfEmpty()
                    select new
                    {
                        person.Name,
                        Address = address == null ? "No Address" : address.Street
                    };

foreach (var result in leftJoinQuery)
{
    Console.WriteLine($"Name: {result.Name}, Street: {result.Address}");
}

right join :

• 右連接類似於左連接，但返回右邊清單的所有元素，以及那些符合連接條件的左邊清單的元素，如果左邊清單中沒有匹配的元素，則返回null或默認值。
• 在這個右連接的示例中，我們反轉了左右清單的位置，然後使用相同的into子句和DefaultIfEmpty方法來處理右連接。如果沒有匹配的人名，person會為null，我們在結果中使用條件運算符處理這種情況(沒有判斷null, 會發生null exception)。

var rightJoinQuery = from address in addresses
                     join person in people
                     on address.ID equals person.ID into personGroup
                     from person in personGroup.DefaultIfEmpty()
                     select new
                     {
                         Address = address.Street,
                         Name = person == null ? "No Name" : person.Name
                     };

foreach (var result in rightJoinQuery)
{
    Console.WriteLine($"Name: {result.Name}, Street: {result.Address}");
}