Enumerable 的擴充方法 - GroupBy、join、GroupJoin、SelectMany 方法的使用

 

資料準備

USE [testDB]
GO
/****** Object:  Table [dbo].[Score]    Script Date: 2022/10/30 上午 10:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Score](
    [StudentID] [int] NOT NULL,
    [Class] [nvarchar](50) NOT NULL,
    [TheScore] [int] NOT NULL
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Student]    Script Date: 2022/10/30 上午 10:51:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
    [ID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Sex] [nvarchar](50) NULL,
    [Age] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (1, N'國文', 100)
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (1, N'英文', 60)
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (2, N'國文', 50)
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (2, N'英文', 40)
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (3, N'國文', 60)
GO
INSERT [dbo].[Score] ([StudentID], [Class], [TheScore]) VALUES (3, N'英文', 80)
GO
INSERT [dbo].[Student] ([ID], [Name], [Sex], [Age]) VALUES (1, N'Bill', N'male', 25)
GO
INSERT [dbo].[Student] ([ID], [Name], [Sex], [Age]) VALUES (2, N'Mary', N'female', 30)
GO
INSERT [dbo].[Student] ([ID], [Name], [Sex], [Age]) VALUES (3, N'Tim', N'female', 30)
GO
INSERT [dbo].[Student] ([ID], [Name], [Sex], [Age]) VALUES (4, N'John', N'male', 28)
GO

一、GroupBy
範例1、針對 group key 去做群組，並對每組去計算出平均值

void Main()
{
    Scores.Dump();
    Scores.GroupBy(o => o.Class)
          .Select(o => new
          {
              Class = o.Key,
              Avg = o.Average(m => m.TheScore)
          })
          .Dump();
}

也等於以下寫法

void Main()
{
    Scores.Dump();
    Scores.GroupBy(o => o.Class,
        (key, scores) => new
        {
            Class = key,
            Avg = scores.Average(s => s.TheScore)
        }).Dump();
}

範例2、針對每組年齡去計算出學生數量

void Main()
{
    Students.Dump();
    Students.GroupBy(o => o.Age)
          .Select(o => new
          {
              Age = o.Key,
              Count = o.Count()
          })
          .Dump();
}

範例3、
GroupBy 的 Key 的值可以是多個 key 組成

void Main()
{
    Students.Dump();
    Students.GroupBy(s => new { s.Sex, s.Age })
    .Select(x => new
    {
        Key = x.Key,
        Names = x.Select(x => x.Name)
    })
    .Dump();
}

二、Join

void Main()
{
    Scores.Dump();
    Students.Dump();
    Scores.Join(Students, score => score.StudentID, stud => stud.ID, (score, stud) => new
    {
        score.Class,
        score.TheScore,
        stud.Name
    }).Dump();
}

三、GroupJoin
要達成Left join 必須依靠GroupJoin來完成。

GroupJoin 顧名思義就是先Group 在做 Join ，與Join的不同處在於Join會得到一個一對一的新物件集合 (List)，而GroupJoin 則會得到一對多的物件集合({key,List})。

void Main()
{
    Students.Dump();
    Scores.Dump();
    Students.GroupJoin(Scores, stud => stud.ID, score => score.StudentID, (stud, score) => new
    {
        stud.ID,
        stud.Name,
        Details = score.DefaultIfEmpty()
    }).Dump();
}

四、SelectMany 方法的使用
SelectMany 方法是用於處理集合中的集合，並將它們合併為單個集合。

以下目的為將所有學生底下所有修的科目都一一列出成單個集合。

void Main()
{
    var students = new List<Student>
    {
        new Student { Name = "Alice", Subjects = new List<Subject> { new Subject { Name = "Math" }, new Subject { Name = "Physics" } } },
        new Student { Name = "Bob", Subjects = new List<Subject> { new Subject { Name = "Biology" } } },
    };
    var allSubjects = students.SelectMany(student => student.Subjects);

    foreach (var subject in allSubjects)
    {
        Console.WriteLine(subject.Name);
        // output:
        // Math
        // Physics
        // Biology
    }
}

public class Student
{
    public string Name { get; set; }
    public List<Subject> Subjects { get; set; }
}

public class Subject
{
    public string Name { get; set; }
}

以下目的是以每筆成績為基本單位，列出所有學生的每一筆成績。

void Main()
{
    Students.Dump();
    Scores.Dump();
    Students.GroupJoin(Scores, stud => stud.ID, score => score.StudentID, (stud, score) => new
    {
        stud.ID,
        stud.Name,
        Details = score.DefaultIfEmpty()
    })
    .SelectMany(stud => stud.Details.DefaultIfEmpty(), (stud, score) => new
    {
        stud.ID,
        stud.Name,
        Class = (score == null || score.Class == null ? "N/A" : score.Class),
        Score = (score == null || score.TheScore == null ? new Nullable<int>() : score.TheScore)
    })
    .Dump();
}

SelectMany方法的另一種寫法，如果不對 null 紀錄做特別處理的話，可以使用以下寫法

void Main()
{
    Students.Dump();
    Scores.Dump();
    Students.GroupJoin(Scores, stud => stud.ID, score => score.StudentID, (stud, score) => new
    {
        stud.ID,
        stud.Name,
        Details = score.DefaultIfEmpty()
    })
    .SelectMany(stud => stud.Details.DefaultIfEmpty())
    .Dump();
}

參考資料：
LINQ學習筆記(7)實作Left join(1) Join與Group join

C# LINQ: GroupBy