LINQ 分組與連接完全指南：GroupBy、Join、GroupJoin、左外連接、笛卡爾積

數據源準備

class Student { public int Id; public string Name; }
class Course  { public int Id; public string Name; }
class Score   { public int StudentId; public int CourseId; public double Value; }

var students = new List<Student> { 
    new Student{Id=1,Name="Andy"}, new Student{Id=2,Name="Bill"}, 
    new Student{Id=3,Name="Cindy"}, new Student{Id=4,Name="Dark"} 
};
var courses = new List<Course> { 
    new Course{Id=1,Name="C語言"}, new Course{Id=2,Name="生物物理"},
    new Course{Id=3,Name="編譯原理"}, new Course{Id=4,Name="財務"},
    new Course{Id=5,Name="大學英語"} 
};
var scores = new List<Score> {
    new Score{StudentId=1,CourseId=1,Value=78}, new Score{StudentId=1,CourseId=2,Value=60},
    new Score{StudentId=1,CourseId=3,Value=92}, new Score{StudentId=1,CourseId=4,Value=85},
    new Score{StudentId=1,CourseId=5,Value=55.5}, new Score{StudentId=2,CourseId=1,Value=59},
    new Score{StudentId=2,CourseId=2,Value=78}, new Score{StudentId=2,CourseId=3,Value=86},
    new Score{StudentId=3,CourseId=2,Value=60}, new Score{StudentId=4,CourseId=4,Value=96},
    new Score{StudentId=5,CourseId=5,Value=78}
};

一、GroupBy：分組聚合

基本分組：返回 IGrouping 集合

var groups = from s in scores
             group s by s.StudentId into g
             select g; // IEnumerable<IGrouping<int, Score>>

foreach (var g in groups)
{
    Console.WriteLine($"\n學生 {g.Key}:");
    foreach (var s in g) Console.WriteLine($"  {s.CourseId}: {s.Value}");
}

分組並投影匿名類型

var result = from s in scores
             group s by s.StudentId into g
             select new { StudentId = g.Key, Scores = g };

// 等價擴展方法
var result = scores.GroupBy(s => s.StudentId, 
    (key, group) => new { StudentId = key, Scores = group });

二、Join：內連接

多表連接查詢學生-課程-成績

var query = from sc in scores
            join st in students on sc.StudentId equals st.Id
            join co in courses on sc.CourseId equals co.Id
            select new { Student = st.Name, Course = co.Name, Score = sc.Value };

/* 結果:
{ Student = Andy, Course = C語言, Score = 78 }
{ Student = Andy, Course = 生物物理, Score = 60 }
...
*/

擴展方法鏈式調用

var query = scores
    .Join(students, sc => sc.StudentId, st => st.Id, 
          (sc, st) => new { st.Name, sc.Value, sc.CourseId })
    .Join(courses, x => x.CourseId, co => co.Id,
          (x, co) => new { Student = x.Name, Course = co.Name, Score = x.Value });

三、左外連接：保留左側所有數據

當右側無匹配時，右側字段為默認值 (null/0)。

var students2 = new List<Student> { 
    new Student{Id=5,Name="Erik"}, new Student{Id=6,Name="Frank"} // Frank 無成績
};

var leftJoin = from st in students2
               join sc in scores on st.Id equals sc.StudentId into scGroup
               from sc in scGroup.DefaultIfEmpty()
               select new { Student = st.Name, Score = sc == null ? 0 : sc.Value };

/* 結果:
{ Student = Erik, Score = 78 }
{ Student = Frank, Score = 0 }  // 保留無成績學生
*/

擴展方法實現：GroupJoin + SelectMany

var leftJoin = students2
    .GroupJoin(scores, st => st.Id, sc => sc.StudentId,
               (st, scGroup) => new { st, scGroup })
    .SelectMany(x => x.scGroup.DefaultIfEmpty(),
               (x, sc) => new { Student = x.st.Name, Score = sc == null ? 0 : sc.Value });

四、笛卡爾積：SelectMany 交叉連接

生成所有學生×課程組合，作為成績錄入模板。

var template = from st in students
               from co in courses
               select new { Student = st.Name, Course = co.Name, Score = (double?)null };

/* 結果: 4學生 × 5課程 = 20 行，Score 全為 null */

擴展方法

var template = students.SelectMany(st => 
    courses.Select(co => new { Student = st.Name, Course = co.Name, Score = (double?)null }));

五、GroupJoin：分組連接 (一對多扁平化)

相當於 GroupBy + Join 結合，直接得到分組後的集合。

var grouped = students
    .GroupJoin(scores, st => st.Id, sc => sc.StudentId,
               (st, scGroup) => new { Student = st.Name, Scores = scGroup });

/* 結果結構:
{ Student = Andy, Scores = [Score1, Score2, ...] }
{ Student = Bill, Scores = [Score1, Score2, ...] }
...
*/

連接類型對照表

實戰建議

• 大數據量 Join 先確保連接鍵有索引 (內存列表則無關)
• 左外連接用 DefaultIfEmpty() 時，引用類型結果為 null，值類型為 0，需顯式處理
• GroupJoin 結果直接可序列化為 JSON，適合 Web API 返回巢狀結構

掌握這五種連接模式，LINQ 即可覆蓋 SQL 90% 以上的查詢場景。