LINQ to SQL GroupBy 進階：匿名型別、聚合函數、多重鍵分組與 Having 條件

GroupBy 回傳結果結構

group ... by ... into g 中的 g 代表一個群組，包含 Key 與群組內所有元素。

Select 匿名型別包裝群組

var q = from p in db.Products
        group p by p.CategoryID into g
        select new { CategoryID = g.Key, g }; // g 為完整群組

遍歷方式：

foreach (var gp in q) {
    if (gp.CategoryID == 7) {
        foreach (var p in gp.g) { ... }
    }
}

聚合函數：Max、Min、Sum、Average、Count

只需統計值時，LINQ to SQL 直接轉換為 SQL 聚合，不載入明細資料。

各類產品最高單價

var q = from p in db.Products
        group p by p.CategoryID into g
        select new { g.Key, MaxPrice = g.Max(p => p.UnitPrice) };

生成 SQL：SELECT MAX(UnitPrice), CategoryID FROM Products GROUP BY CategoryID

其他聚合範例

• g.Min(p => p.UnitPrice) 最低價
• g.Average(p => p.UnitPrice) 平均價
• g.Sum(p => p.UnitPrice) 價格總和
• g.Count() 產品數量
• g.Count(p => p.Discontinued) 條件計數（停產商品數）

Having 條件：群組篩選

var q = from p in db.Products
        group p by p.CategoryID into g
        where g.Count() >= 10
        select new { g.Key, ProductCount = g.Count() };

轉換為 SQL 子查詢外層加 WHERE：

SELECT CategoryID, value2 AS ProductCount
FROM (SELECT COUNT(*) AS value, COUNT(*) AS value2, CategoryID
      FROM Products GROUP BY CategoryID) AS t1
WHERE t1.value >= 10

GroupBy 多重鍵：使用匿名型別

var q = from p in db.Products
        group p by new { p.CategoryID, p.SupplierID } into g
        select new { g.Key, g };

g.Key 為匿名型別實例，存取需 g.Key.CategoryID、g.Key.SupplierID。SQL 為 GROUP BY CategoryID, SupplierID。

運算式分組：依條件分組

var q = from p in db.Products
        group p by new { Criterion = p.UnitPrice > 10 } into g
        select g;

依單價 > 10 分為兩群。Key 為 { Criterion = true/false }。