C# LINQ 内连接与左连接

LINQ Inner-Join vs Left-Join

提问by Guy

Using extension syntax I'm trying to create a left-join using LINQ on two lists that I have. The following is from the Microsoft help but I've modified it to show that the pets list has no elements. What I'm ending up with is a list of 0 elements. I assume that this is because an inner-join is taking place. What I want to end up with is a list of 3 elements (the 3 Person objects) with null data filled in for the missing elements. i.e. a Left-Join. Is this possible?

使用扩展语法，我尝试在我拥有的两个列表上使用 LINQ 创建左连接。以下来自 Microsoft 帮助，但我对其进行了修改以显示宠物列表没有元素。我最终得到的是一个包含 0 个元素的列表。我认为这是因为正在进行内部联接。我想要结束的是一个包含 3 个元素（3 个 Person 对象）的列表，其中为缺失的元素填充了空数据。即左连接。这可能吗？

Person magnus = new Person { Name = "Hedlund, Magnus" };
Person terry = new Person { Name = "Adams, Terry" };
Person charlotte = new Person { Name = "Weiss, Charlotte" };

//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 daisy = new Pet { Name = "Daisy", Owner = magnus };

List<Person> people = new List<Person> { magnus, terry, charlotte };
//List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy };
List<Pet> pets = new List<Pet>();

// Create a list of Person-Pet pairs where 
// each element is an anonymous type that contains a
// Pet's name and the name of the Person that owns the Pet.
var query =
    people.Join(pets,
                person => person,
                pet => pet.Owner,
                (person, pet) =>
                    new { OwnerName = person.Name, Pet = pet.Name }).ToList();

采纳答案by tvanfosson

I think if you want to use extension methods you need to use the GroupJoin

我想如果你想使用扩展方法，你需要使用GroupJoin

var query =
    people.GroupJoin(pets,
                     person => person,
                     pet => pet.Owner,
                     (person, petCollection) =>
                        new { OwnerName = person.Name,
                              Pet = PetCollection.Select( p => p.Name )
                                                 .DefaultIfEmpty() }
                    ).ToList();

You may have to play around with the selection expression. I'm not sure it would give you want you want in the case where you have a 1-to-many relationship.

您可能需要使用选择表达式。在您拥有一对多关系的情况下，我不确定它会给您想要的。

I think it's a little easier with the LINQ Query syntax

我认为使用 LINQ 查询语法更容易一些

var query = (from person in context.People
             join pet in context.Pets on person equals pet.Owner
             into tempPets
             from pets in tempPets.DefaultIfEmpty()
             select new { OwnerName = person.Name, Pet = pets.Name })
            .ToList();

回答by Jason Punyon

Left joins in LINQ are possible with the DefaultIfEmpty() method. I don't have the exact syntax for your case though...

LINQ 中的左连接可以使用 DefaultIfEmpty() 方法。我没有你的情况的确切语法...

Actually I think if you just change pets to pets.DefaultIfEmpty() in the query it might work...

实际上我认为如果你只是在查询中将 pets 更改为 pets.DefaultIfEmpty() 它可能会起作用......

EDIT: I really shouldn't answer things when its late...

编辑：我真的不应该在很晚的时候回答问题......

回答by Gishu

You need to get the joined objects into a set and then apply DefaultIfEmpty as JPunyon said:

您需要将连接的对象放入一个集合中，然后像 JPunyon 所说的那样应用 DefaultIfEmpty：

Person magnus = new Person { Name = "Hedlund, Magnus" };
Person terry = new Person { Name = "Adams, Terry" };
Person charlotte = new Person { Name = "Weiss, Charlotte" };

Pet barley = new Pet { Name = "Barley", Owner = terry };
List<Person> people = new List<Person> { magnus, terry, charlotte };
List<Pet> pets = new List<Pet>{barley};

var results =
    from person in people
    join pet in pets on person.Name equals pet.Owner.Name into ownedPets
    from ownedPet in ownedPets.DefaultIfEmpty(new Pet())
    orderby person.Name
    select new { OwnerName = person.Name, ownedPet.Name };


foreach (var item in results)
{
    Console.WriteLine(
        String.Format("{0,-25} has {1}", item.OwnerName, item.Name ) );
}

Outputs:

输出：

Adams, Terry              has Barley
Hedlund, Magnus           has
Weiss, Charlotte          has

回答by Guy

Here's a good blog post that's just been posted by Fabrice (author of LINQ in Action) which covers the material in the question that I asked. I'm putting it here for reference as readers of the question will find this useful.

这是 Fabrice（LINQ in Action 的作者）刚刚发布的一篇很好的博客文章，其中涵盖了我提出的问题中的材料。我把它放在这里以供参考，因为问题的读者会发现这很有用。

Converting LINQ queries from query syntax to method/operator syntax

将 LINQ 查询从查询​​语法转换为方法/运算符语法

回答by Jo?o Vieira

I the following error message when faced this same problem:

遇到同样的问题时，我收到以下错误消息：

The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.

join 子句中的表达式之一的类型不正确。调用“GroupJoin”时类型推断失败。

Solved when I used the same property name, it worked.

当我使用相同的属性名称时解决了，它起作用了。

(...)

（……）

join enderecoST in db.PessoaEnderecos on 
    new 
      {  
         CD_PESSOA          = nf.CD_PESSOA_ST, 
         CD_ENDERECO_PESSOA = nf.CD_ENDERECO_PESSOA_ST 
      } equals 
    new 
    { 
         enderecoST.CD_PESSOA, 
         enderecoST.CD_ENDERECO_PESSOA 
    } into eST

(...)

（……）

回答by Stefan Steiger

If you actually have a database, this is the most-simple way:

如果你真的有一个数据库，这是最简单的方法：

var lsPetOwners = ( from person in context.People
                    from pets in context.Pets
                        .Where(mypet => mypet.Owner == person.ID) 
                        .DefaultIfEmpty()
                     select new { OwnerName = person.Name, Pet = pets.Name }
                   ).ToList();