使用 linq 進(jìn)行左連接查詢

Intro

今天改項目發(fā)現(xiàn)一個bug，原因是因為兩個數(shù)據(jù) join 的時候另外一個數(shù)據(jù)不存在，所以導(dǎo)致最后join出來的結(jié)果和預(yù)期不符，于是要改成左連接查詢，突然發(fā)現(xiàn)自己不會寫 linq 的左連接查詢。。

Linq 左連接

默認(rèn)join 的是屬于是 inner join，如果要使用到左連接，你需要使用 DefaultIfEmpty 來在沒有join到數(shù)據(jù)的時候返回默認(rèn)的集合

class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

class Pet
{
    public string Name { get; set; }
    public Person Owner { get; set; }
}

public static void LeftOuterJoinExample()
{
    Person magnus = new Person { FirstName = "Magnus", LastName = "Hedlund" };
    Person terry = new Person { FirstName = "Terry", LastName = "Adams" };
    Person charlotte = new Person { FirstName = "Charlotte", LastName = "Weiss" };
    Person arlene = new Person { FirstName = "Arlene", LastName = "Huff" };

    Pet barley = new Pet { Name = "Barley", Owner = terry };
    Pet boots = new Pet { Name = "Boots", Owner = terry };
    Pet whiskers = new Pet { Name = "Whiskers", Owner = charlotte };
    Pet bluemoon = new Pet { Name = "Blue Moon", Owner = terry };
    Pet daisy = new Pet { Name = "Daisy", Owner = magnus };

    // Create two lists.
    List<Person> people = new List<Person> { magnus, terry, charlotte, arlene };
    List<Pet> pets = new List<Pet> { barley, boots, whiskers, bluemoon, daisy };

    var query = from person in people
                join pet in pets on person equals pet.Owner into gj
                from subpet in gj.DefaultIfEmpty()
                select new { person.FirstName, PetName = subpet?.Name ?? String.Empty };

    foreach (var v in query)
    {
        Console.WriteLine($"{v.FirstName+":",-15}{v.PetName}");
    }
}

// This code produces the following output:
//
// Magnus:        Daisy
// Terry:         Barley
// Terry:         Boots
// Terry:         Blue Moon
// Charlotte:     Whiskers
// Arlene:

Reference

• https://docs.microsoft.com/en-us/dotnet/csharp/linq/perform-left-outer-joins
• https://docs.microsoft.com/zh-cn/dotnet/csharp/linq/perform-left-outer-joins