如何在 Linq 中进行完整的外部联接?

问题描述

我继承了一个没有完全优化设计的数据库，我需要处理一些数据.让我对我必须做的事情做一个更常见的类比:

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:

假设我们有一个 Student 表，一个 StudentClass 表记录他参加的所有课程，以及一个 StudentTeacher 表存储所有教过这个学生的老师.是的，我知道这是一个愚蠢的设计，将老师存储在 Class 桌子上会更有意义 - 但这就是我们正在使用的.

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.

我现在想清理数据，我想找到一个学生有老师没有课的地方，或者有课没有老师的地方.SQL因此:

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

你如何在 Linq 中做到这一点?

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.