我有一个人的ID和他们的名字的列表,以及一个人的ID和他们的姓的列表。有些人没有名字,有些人没有姓;我想在两个列表上进行完全外部联接。
因此,以下列表:
ID FirstName -- --------- 1 John 2 Sue ID LastName -- -------- 1 Doe 3 Smith
应产生:
ID FirstName LastName -- --------- -------- 1 John Doe 2 Sue 3 Smith
我是LINQ的新手(如果我很la脚,请原谅我),并且已经找到了许多解决方案,这些解决方案看起来都非常相似,但实际上似乎是外部联接,而对于“ 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 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, };