How to do a full outer join in Linq?
I've inherited a datab开发者_运维百科ase 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 Student
table, a StudentClass
table keeping record of all the classes he attended, and a StudentTeacher
table 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.
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:
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?
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.
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"} );
}
for the given 2 collections a and b, a required full outer join might be as following:
a.Union(b).Except(a.Intersect(b));
If a and b are not of the same type, then 2 separate left outer joins are required:
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():
(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});
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
Based on Shaul's answer, but with a little streamlining:
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 where
clause and use the first select
above, rather than the second.
精彩评论