C# 如何在 Linq 中进行完整的外部联接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2085422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do a full outer join in Linq?
提问by Shaul Behr
I've inherited a database that wasn't designed exactly optimally, and I need to manipulate some data. Let me give a more common analogy of the kind of thing I have to do:
我继承了一个没有完全优化设计的数据库，我需要处理一些数据。让我对我必须做的事情做一个更常见的类比：
Let's say we have a Studenttable, a StudentClasstable keeping record of all the classes he attended, and a StudentTeachertable that stores all the teachers who taught this student. Yes, I know it's a dumb design and it would make more sense to store the teacher on the Class table - but that's what we're working with.
假设我们有一张Student桌子，一张StudentClass桌子记录了他参加的所有课程，还有一张StudentTeacher桌子存储了教过这个学生的所有老师。是的，我知道这是一个愚蠢的设计，将老师存储在 Class 桌子上会更有意义 - 但这就是我们正在使用的。
I now want to clean up the data, and I want to find all the places where a student has a teacher but no classes, or a class but no teachers. SQL thus:
我现在想清理数据，我想找到一个学生有老师没有课的地方，或者有课没有老师的地方。SQL因此：
select *
from StudentClass sc
full outer join StudentTeacher st on st.StudentID = sc.StudentID
where st.id is null or sc.id is null
How do you do that in Linq?
你如何在 Linq 中做到这一点？
采纳答案by Shaul Behr
I think I have the answer here, which is not as elegant as I'd hoped, but it should do the trick:
我想我在这里有答案，这并不像我希望的那么优雅，但它应该可以解决问题：
var studentIDs = StudentClasses.Select(sc => sc.StudentID)
.Union(StudentTeachers.Select(st => st.StudentID);
//.Distinct(); -- Distinct not necessary after Union
var q =
from id in studentIDs
join sc in StudentClasses on id equals sc.StudentID into jsc
from sc in jsc.DefaultIfEmpty()
join st in StudentTeachers on id equals st.StudentID into jst
from st in jst.DefaultIfEmpty()
where st == null ^ sc == null
select new { sc, st };
You could probably squeeze these two statements into one, but I think you'd sacrifice code clarity.
您可能可以将这两个语句合二为一，但我认为您会牺牲代码的清晰度。
回答by salgo60
A start...
一个开始...
var q = from sc in StudentClass
join st in StudentTeachers on sc.StudentID equals st.StudentID into g
from st in g.DefaultIfEmpty()
select new {StudentID = sc.StudentID, StudentIDParent = st == null ? "(no StudentTeacher)" : st.StudentID...........};
See also http://www.linqpad.net/for more samples Good tool to play with
有关更多示例，另请参见http://www.linqpad.net/很好的工具
回答by Boris Lipschitz
for the given 2 collections aand b, a required full outer join might be as following:
对于给定的 2 个集合a和b，所需的完全外连接可能如下所示：
a.Union(b).Except(a.Intersect(b));
If a and b are not of the same type, then 2 separate left outer joinsare required:
如果 a 和 b 的类型不同，则需要 2 个单独的左外连接：
var studentsWithoutTeachers =
from sc in studentClasses
join st in studentTeachers on sc.StudentId equals st.StudentId into g
from st in g.DefaultIfEmpty()
where st == null
select sc;
var teachersWithoutStudents =
from st in studentTeachers
join sc in studentClasses on st.StudentId equals sc.StudentId into g
from sc in g.DefaultIfEmpty()
where sc == null
select st;
here is a one line option using Concat():
这是使用 Concat() 的单行选项：
(from l in left
join r in right on l.Id equals r.Id into g
from r in g.DefaultIfEmpty()
where r == null
select new {l, r})
.Concat(
from r in right
join sc in left on r.Id equals sc.Id into g
from l in g.DefaultIfEmpty()
where l == null
select new {l, r});
回答by andrey.tsykunov
Extension method:
扩展方法：
public static IEnumerable<TResult> FullOuterJoin<TOuter, TInner, TKey, TResult>(this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter,TKey> outerKeySelector, Func<TInner,TKey> innerKeySelector, Func<TOuter,TInner,TResult> resultSelector)
where TInner : class
where TOuter : class
{
var innerLookup = inner.ToLookup(innerKeySelector);
var outerLookup = outer.ToLookup(outerKeySelector);
var innerJoinItems = inner
.Where(innerItem => !outerLookup.Contains(innerKeySelector(innerItem)))
.Select(innerItem => resultSelector(null, innerItem));
return outer
.SelectMany(outerItem =>
{
var innerItems = innerLookup[outerKeySelector(outerItem)];
return innerItems.Any() ? innerItems : new TInner[] { null };
}, resultSelector)
.Concat(innerJoinItems);
}
Test:
测试：
[Test]
public void CanDoFullOuterJoin()
{
var list1 = new[] {"A", "B"};
var list2 = new[] { "B", "C" };
list1.FullOuterJoin(list2, x => x, x => x, (x1, x2) => (x1 ?? "") + (x2 ?? ""))
.ShouldCollectionEqual(new [] { "A", "BB", "C"} );
}
回答by sq33G
Based on Shaul's answer, but with a little streamlining:
基于 Shaul 的回答，但稍作精简：
var q =
from id in studentIDs
join sc in StudentClasses on id equals sc.StudentID into jsc
join st in StudentTeachers on id equals st.StudentID into jst
where jst.Any() ^ jsc.Any() //exclusive OR, so one must be empty
//this will return the group with the student's teachers, and an empty group
// for the student's classes -
// or group of classes, and empty group of teachers
select new { classes = jsc, teachers = jst };
//or, if you know that the non-empty group will always have only one element:
select new { class = jsc.DefaultIfEmpty(), teacher = jst.DefaultIfEmpty() };
Note that for a full outer join, this can work, too. Leave out the whereclause and use the first selectabove, rather than the second.
请注意，对于完整的外部联接，这也可以工作。省略where子句并使用select上面的第一个，而不是第二个。
