Question

我试图加入2个表格,其中一张可以链接,但我只想把“一”一栏数据放在一行中,在所有其他各行中,这种数据应一律无效。因此:

TEACHER
---------
TeacherID
TeacherBiography
.
.
.

STUDENT
-----------
TeacherID
StudentFName
StudentLName
.
.
.

例: 你们想让所有第一名学生Joe,通过教师培训加入教师队伍,但限制退学结果,以便教师数据不会在每行复课。原因是教师比照率很高;我需要回来,但不是每行。

因此,一些样本产出应当像:

------------------------------------------------
StudentFName | StudentLName | TeacherID | TeacherBiography
------------------------------------------------
 Joe         |  Smith       | 1         |  long biography for teacher 1..   
 Joe         |  Jones       | 2         |  long biography for teacher 2..   
 Joe         |  Michaels    | 1         | null
 Joe         |  Rogers      | 3         |  long biography for teacher 3..  
 Joe         |  Washington  | 1         | null
.
.
.
.

So in the case of Michaels and Washington the TeacherBiograph (and all other teacher columns) is null because the data was already returned in the Smith row.

我如何这样做?

- 法国

Answer 1

I think the only efficient way to do this is to Join STUDENTs and TEACHERs with the TeacherID as a key, then you can output this key only one time for only one row and the other rows for the same key output null instead, like this:

var StudentsList = students.Join(  //Inner join
    Teachers,
    s => s.TeacherID,
    t => t.TeacherID,
    (teacher, teacherStudetns) => 
        new
        {
            Teacher = teacher,
            HisStudents = teacherStudetns
        });

然后,你只能一度完成关键产出,例如:

foreach(var item in StudentsList)
{
     Console.Writline("Teach Name: {0}, His students:", item.TeacherBiography);
     foreach(var student in item.HisStudents)
     {
         Console.writeLine("---------- {0}", student.StudentName);
     }
}

Answer 2

我将在两个步骤中这样做,而不是使你的守则复杂化,试图在其中做到这一点。首先,检索你所关心的学生信息。如果有的话,鉴于教师信息数据库的独特清单,你可以再做一次问询,了解教师的信息。

var students = //student query
var teacherIds = students.Select(p => p.TeacherID).Distinct();
var teachers = teacherTable.Where(p => p.Contains(teacherIds));

然后,你可以怀念这两个成果。

var allData = from s in students
    join t in teachers on
    t.teacherID equals s.TeacherID
    select new { StudentName = s.Name, TeacherBio = t.TeacherBiography};

这将造成两个数据库的点击,但你只将所需要的数据项目退回。

友情链接