我有一份人们的身份证和名字的名单，还有一份人们的身份证和姓氏的名单。有些人没有名字，有些人没有姓;我想在这两个列表上做一个完整的外部连接。
下面列出:
ID FirstName
-- ---------
1 John
2 Sue
ID LastName
-- --------
1 Doe
3 Smith
应该生产:
ID FirstName LastName
-- --------- --------
1 John Doe
2 Sue
3 Smith
我已经发现了相当多的解决方案的“LINQ外部连接”，它们看起来都很相似，但似乎真的是离开外部连接。
到目前为止我的尝试是这样的:
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;
}
但结果是:
ID FirstName LastName
-- --------- --------
1 John Doe
2 Sue
我做错了什么?
两个或多个表的完全外部连接:
首先提取要连接的列。
var DatesA = from A in db.T1 select A.Date;
var DatesB = from B in db.T2 select B.Date;
var DatesC = from C in db.T3 select C.Date;
var Dates = DatesA.Union(DatesB).Union(DatesC);
然后在提取的列和主表之间使用左外连接。
var Full_Outer_Join =
(from A in Dates
join B in db.T1
on A equals B.Date into AB
from ab in AB.DefaultIfEmpty()
join C in db.T2
on A equals C.Date into ABC
from abc in ABC.DefaultIfEmpty()
join D in db.T3
on A equals D.Date into ABCD
from abcd in ABCD.DefaultIfEmpty()
select new { A, ab, abc, abcd })
.AsEnumerable();
我不知道这是否适用于所有情况，但从逻辑上讲，这似乎是正确的。其思想是取一个左外连接和一个右外连接，然后取结果的并集。
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);
这就像写的一样，因为它是在LINQ to Objects中。如果LINQ to SQL或其他，查询处理器可能不支持安全导航或其他操作。你必须使用条件操作符有条件地获取值。
也就是说,
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,
};
两个或多个表的完全外部连接:
首先提取要连接的列。
var DatesA = from A in db.T1 select A.Date;
var DatesB = from B in db.T2 select B.Date;
var DatesC = from C in db.T3 select C.Date;
var Dates = DatesA.Union(DatesB).Union(DatesC);
然后在提取的列和主表之间使用左外连接。
var Full_Outer_Join =
(from A in Dates
join B in db.T1
on A equals B.Date into AB
from ab in AB.DefaultIfEmpty()
join C in db.T2
on A equals C.Date into ABC
from abc in ABC.DefaultIfEmpty()
join D in db.T3
on A equals D.Date into ABCD
from abcd in ABCD.DefaultIfEmpty()
select new { A, ab, abc, abcd })
.AsEnumerable();
下面是一个扩展方法:
public static IEnumerable<KeyValuePair<TLeft, TRight>> FullOuterJoin<TLeft, TRight>(this IEnumerable<TLeft> leftItems, Func<TLeft, object> leftIdSelector, IEnumerable<TRight> rightItems, Func<TRight, object> rightIdSelector)
{
var leftOuterJoin = from left in leftItems
join right in rightItems on leftIdSelector(left) equals rightIdSelector(right) into temp
from right in temp.DefaultIfEmpty()
select new { left, right };
var rightOuterJoin = from right in rightItems
join left in leftItems on rightIdSelector(right) equals leftIdSelector(left) into temp
from left in temp.DefaultIfEmpty()
select new { left, right };
var fullOuterJoin = leftOuterJoin.Union(rightOuterJoin);
return fullOuterJoin.Select(x => new KeyValuePair<TLeft, TRight>(x.left, x.right));
}
