這份筆記整理了 .NET 搭配 Entity Framework (EF Core) 操作 SQL 資料庫關聯與群組查詢的常見範例。以下是重新整理後的詳細筆記，使用清晰的 Markdown 結構與註解說明，幫助各位未來複習時快速掌握概念。
.NET + SQL 資料表關聯與群組操作筆記¶
一、資料表關聯 (Join)¶
1. 兩個資料表關聯：產品 (Products) 與 類別 (Categories)¶
目標：
顯示所有產品欄位 + 對應的類別名稱，並篩選出「類別編號 = 1」的產品。
var products = ctx.Products.Join(
ctx.Categories,
p => p.CategoryId, // 產品表的關聯欄位
c => c.CategoryId, // 類別表的關聯欄位
(p, c) => new
{
p, // 產品全部欄位
c.CategoryName // 類別名稱
}
).Where(x => x.p.CategoryId == 1) // 篩選條件：類別編號為 1
.ToList();
📘 重點說明：
- Join 方法用來建立兩個資料表的關聯。
- 匿名型別 (p, c) => new { p, c.CategoryName } 同時取兩邊的資料。
- .Where() 為篩選條件。
2. 三個資料表關聯：產品 (Products)、類別 (Categories)、供應商 (Suppliers)¶
目標：
顯示產品資料、類別名稱、供應商公司名稱。
var products = ctx.Products.Join(
ctx.Categories,
p => p.CategoryId,
c => c.CategoryId,
(p, c) => new
{
p, c.CategoryName
}
).Join(
ctx.Suppliers,
product => product.p.SupplierId,
s => s.SupplierId,
(product, s) => new
{
product.p,
product.CategoryName,
s.CompanyName
}
)
.ToList();
📘 重點說明：
- 第二層 Join 時，第一個參數是上一層 Join 的結果。
- 可一次關聯多個資料表，用匿名型別階層展開。
3. 訂單與訂單明細關聯¶
目標：
找出 OrderId = 10251 的訂單，包含訂單資訊與訂單明細。
var products = ctx.Orders.Join(
ctx.OrderDetails,
order => order.OrderId,
orderdetail => orderdetail.OrderId,
(order, orderdetail) => new
{
order.OrderId,
order.CustomerId,
order.EmployeeId,
order.OrderDate,
orderdetail
}
).Where(x => x.OrderId == 10251)
.ToList();
📘 重點說明：
- 一對多關聯：一筆訂單對應多筆訂單明細。
- 將所有明細以 .Join 取出後可搭配條件過濾。
二、群組運算 (GroupBy)¶
1. 單欄位群組統計¶
範例：
計算每個群組的產品數量。
var groupCounts = context.Products
.GroupBy(p => p.GroupId)
.Select(g => new
{
GroupId = g.Key, // 群組依據欄位
Count = g.Count() // 各群組數量
})
.ToList();
📘 用法：
GroupBy() 將資料依欄位分組，Select() 可對群組進行聚合 (如 .Count(), .Sum(), .Average() 等)。
2. 群組練習：每個縣市的客戶數¶
var cuscount = ctx.Customers
.GroupBy(x => x.City)
.Select(x => new
{
縣市 = x.Key,
個數 = x.Count()
})
.ToList();
3. 多欄位群組¶
範例：
同時依 GroupId 和 Price 兩欄位群組，並計算同組價格總和。
var groupedData = context.Products
.GroupBy(e => new { e.GroupId, e.Price }) // 多欄位群組
.Select(g => new
{
g.Key.GroupId,
g.Key.Price,
TotalSum = g.Sum(e => e.Price)
})
.ToList();
📘 重點說明：
- new { e.GroupId, e.Price } 建立複合鍵群組。
- 群組後可以針對組內資料進行加總或統計。
三、日期時間欄位處理¶
範例：
從 OrderDate 中取出年份。
var y = ctx.Orders
.Select(x => new
{
x.OrderDate,
x.OrderDate.Value.Year
})
.ToList();
📘 重點說明：
- .Value.Year 適用於 DateTime? (可為 null) 欄位。
- 可用於後續群組或篩選條件。
四、綜合練習：計算每個客戶每年銷售總金額¶
1. 關聯 Customers → Orders → OrderDetails¶
var sales = ctx.Customers.Join(
ctx.Orders,
customer => customer.CustomerId,
order => order.CustomerId,
(customer, order) => new
{
customer.CompanyName,
order.OrderId,
order.OrderDate
}).Join(
ctx.OrderDetails,
order => order.OrderId,
orderdetail => orderdetail.OrderId,
(order, orderdetail) => new
{
order,
orderdetail
}
)
.OrderBy(x => x.order.OrderDate)
.ToList();
2. 最終整合：群組計算每年每客戶銷售額¶
var sales = ctx.Customers.Join(
ctx.Orders,
customer => customer.CustomerId,
order => order.CustomerId,
(customer, order) => new
{
customer.CompanyName,
order.OrderId,
order.OrderDate
}).Join(
ctx.OrderDetails,
order => order.OrderId,
orderdetail => orderdetail.OrderId,
(order, orderdetail) => new
{
order,
orderdetail
}
).GroupBy(x => new { x.order.CompanyName, Year = x.order.OrderDate.Value.Year })
.Select(x => new
{
x.Key.CompanyName,
x.Key.Year,
銷售額 = x.Sum(x =>
(Convert.ToDouble(x.orderdetail.Quantity) *
Convert.ToDouble(x.orderdetail.UnitPrice) *
(1 - x.orderdetail.Discount)))
})
.OrderBy(x => x.CompanyName)
.ThenBy(x => x.Year)
.ToList();
📘 邏輯分解：
1. 第一層 Join： 將客戶與訂單連接。
2. 第二層 Join： 將訂單與訂單明細連接。
3. GroupBy： 依「客戶名稱 + 年份」分組。
4. Sum()： 銷售總額公式 = 數量 * 價格 * (1 - 折扣)。
5. 排序： 先按客戶名稱，再按年度排序輸出。