如何做的LINQ完全外连接？ [英] How to do a full outer join in 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:

让我们说我们有一个学生表中，所有他参加了类的 StudentClass 表格备案，并存储所有谁教这个学生的老师 StudentTeacher 表。是的，我知道这是一个愚蠢的设计，它会更有意义，存储在类表中的老师 - 但是这就是我们正在使用

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 };

您也许可以挤压这两个语句成一个，但我认为你会牺牲code清晰。

You could probably squeeze these two statements into one, but I think you'd sacrifice code clarity.

这篇关于如何做的LINQ完全外连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！