LINQ - 全外连接 [英] LINQ - Full Outer Join

问题描述

我对人的ID列表以及他们的名字，和人的ID和自己的姓氏列表。有些人没有名字，有的没有姓氏;我希望做一个完全外部联接在两个列表。

I have a list of people's ID and their first name, and a list of people's ID and their surname. Some people don't have a first name and some don't have a surname; I'd like to do a full outer join on the two lists.

所以下面的列表：

ID  FirstName
--  ---------
 1  John
 2  Sue

ID  LastName
--  --------
 1  Doe
 3  Smith

应该产生：

ID  FirstName  LastName
--  ---------  --------
 1  John       Doe
 2  Sue
 3             Smith

我是新来的LINQ（所以请原谅我，如果我是跛脚），并发现了不少解决方案LINQ外部联接，这一切看起来很相似，但似乎真的被左外连接。

I'm new to LINQ (so forgive me if I'm being lame) and have found quite a few solutions for 'LINQ Outer Joins' which all look quite similar, but really seem to be left outer joins.

我的努力，到目前为止去是这样的：

My attempts so far go something like this:

private void OuterJoinTest()
{
    List<FirstName> firstNames = new List<FirstName>();
    firstNames.Add(new FirstName { ID = 1, Name = "John" });
    firstNames.Add(new FirstName { ID = 2, Name = "Sue" });

    List<LastName> lastNames = new List<LastName>();
    lastNames.Add(new LastName { ID = 1, Name = "Doe" });
    lastNames.Add(new LastName { ID = 3, Name = "Smith" });

    var outerJoin = from first in firstNames
        join last in lastNames
        on first.ID equals last.ID
        into temp
        from last in temp.DefaultIfEmpty()
        select new
        {
            id = first != null ? first.ID : last.ID,
            firstname = first != null ? first.Name : string.Empty,
            surname = last != null ? last.Name : string.Empty
        };
    }
}

public class FirstName
{
    public int ID;

    public string Name;
}

public class LastName
{
    public int ID;

    public string Name;
}

但是，这将返回：

But this returns:

ID  FirstName  LastName
--  ---------  --------
 1  John       Doe
 2  Sue

我是什么做错了吗？

What am I doing wrong?

推荐答案

我不知道这是否涵盖了所有的情况下，逻辑上似乎是正确的。我们的想法是把左外连接，右外连接并结合在一起（因为它应该是）。

I don't know if this covers all cases, logically it seems correct. The idea is to take a left outer join and right outer join and combine them together (as it should be).

var firstNames = new[]
{
    new { ID = 1, Name = "John" },
    new { ID = 2, Name = "Sue" },
};
var lastNames = new[]
{
    new { ID = 1, Name = "Doe" },
    new { ID = 3, Name = "Smith" },
};
var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty(new { first.ID, Name = default(string) })
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last.Name,
                    };
var rightOuterJoin = from last in lastNames
                     join first in firstNames
                     on last.ID equals first.ID
                     into temp
                     from first in temp.DefaultIfEmpty(new { last.ID, Name = default(string) })
                     select new
                     {
                         last.ID,
                         FirstName = first.Name,
                         LastName = last.Name,
                     };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

这可以作为写，因为它是在LINQ到对象。如果的LINQ to SQL或其他，的DefaultIfEmpty过载（）这需要在默认可能无法正常工作。然后，你必须使用条件运算符有条件地获取值。

This works as written since it is in LINQ to Objects. If LINQ to SQL or other, the overload of DefaultIfEmpty() that takes in a default may not work. Then you'd have to use the conditional operator to conditionally get the values.

即，

var leftOuterJoin = from first in firstNames
                    join last in lastNames
                    on first.ID equals last.ID
                    into temp
                    from last in temp.DefaultIfEmpty()
                    select new
                    {
                        first.ID,
                        FirstName = first.Name,
                        LastName = last != null ? last.Name : default(string),
                    };

这篇关于LINQ - 全外连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！