使用 Linq 进行左连接查询
2018-09-29 本文已影响105人
天天向上卡索
使用 linq 进行左连接查询
Intro
今天改项目发现一个bug，原因是因为两个数据 join 的时候另外一个数据不存在，所以导致最后join出来的结果和预期不符，于是要改成左连接查询，突然发现自己不会写 linq 的左连接查询。。
Linq 左连接
默认join 的是属于是 inner join，如果要使用到左连接，你需要使用 DefaultIfEmpty 来在没有join到数据的时候返回默认的集合
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:
