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

问题描述

我有一个人的 ID 和他们的名字的列表，以及一个人的 ID 和他们的姓氏的列表.有些人没有名字，有些人没有姓氏；我想对两个列表进行完整的外部联接.

所以以下列表:

ID 名字-- ----------1 约翰2 苏ID 姓氏-- -- --1 母鹿3 史密斯

应该产生:

ID FirstName LastName-- ---------- --------1 约翰·多伊2 苏3 史密斯

我是 LINQ 的新手(所以请原谅我的跛脚)并且已经找到了很多LINQ 外连接"的解决方案，它们看起来都非常相似，但实际上似乎是左外连接.

到目前为止，我的尝试是这样的:

private void OuterJoinTest(){列表<名字>firstNames = new List();firstNames.Add(new FirstName { ID = 1, Name = "John" });firstNames.Add(new FirstName { ID = 2, Name = "Sue" });列表<姓氏>lastNames = new List();lastNames.Add(new LastName { ID = 1, Name = "Doe" });lastNames.Add(new LastName { ID = 3, Name = "Smith" });var externalJoin = from first in firstNames最后加入 lastNames在 first.ID 等于 last.ID进入温度从 temp.DefaultIfEmpty() 中的最后一个选择新的{id = 第一个 != null ?first.ID : last.ID,名字=第一个！=空?first.Name : string.Empty,姓氏 = 最后！= 空?last.Name : string.Empty};}}公开课名字{公共整数 ID；公共字符串名称；}公开课姓氏{公共整数 ID；公共字符串名称；}

但这会返回:

ID FirstName LastName-- ---------- --------1 约翰·多伊2 苏

我做错了什么?

解决方案

我不知道这是否涵盖所有情况，从逻辑上讲似乎是正确的.这个想法是采用左外连接和右外连接，然后对结果进行并集.

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 =从 first in firstNames将 last 加入 lastNames on first.ID 等于 last.ID 到 temp从 temp.DefaultIfEmpty() 中的最后一个选择新的{第一个.ID,FirstName = first.Name,LastName = last?.Name,};var rightOuterJoin =从 last 在 lastNames在 last.ID 上的 firstNames 中加入 first.ID 等于 first.ID 到 temp从第一个在 temp.DefaultIfEmpty()选择新的{最后一个.ID,FirstName = first?.Name,姓氏 = last.Name,};var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

因为它在 LINQ to Objects 中，所以它的工作原理是这样的.如果是 LINQ to SQL 或其他，查询处理器可能不支持安全导航或其他操作.您必须使用条件运算符来有条件地获取值.

即

var leftOuterJoin =从 first in firstNames将 last 加入 lastNames on first.ID 等于 last.ID 到 temp从 temp.DefaultIfEmpty() 中的最后一个选择新的{第一个.ID,FirstName = first.Name,姓氏 = 最后一个 != null ?last.Name : 默认，};

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.

So the following lists:

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

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

Should produce:

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

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 then take the union of the results.

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()
    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()
    select new
    {
        last.ID,
        FirstName = first?.Name,
        LastName = last.Name,
    };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);

This works as written since it is in LINQ to Objects. If LINQ to SQL or other, the query processor might not support safe navigation or other operations. You'd have to use the conditional operator to conditionally get the values.

i.e.,

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,
    };

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