這篇教學文件，我想一步一步的教讀者如何利用 EntityFramework 來寫一對多 (One-To-Many) 關聯 Table的程式碼技巧，並且分別利用 Entity SQL 和
LINQ To SQL 的方式來讀取資料庫。
在實例個案，我們常常會遇到如下的資料表一對多關聯
（SQL 語法）
- CREATE TABLE Employee
- (
- [No] NVARCHAR( 12) NOT NULL PRIMARY KEY,
- HireDate Datetime not null default(getdate ()),
- Name NVARCHAR( 12) NOT NULL
- )
- CREATE TABLE EmployeeSalary
- (
- EmpNo NVARCHAR(12) NOT NULL,
- PayYear INT NOT NULL,
- PayMonth INT NOT NULL,
- bonus DECIMAL(18,2) NOT NULL,
- constraint PK_EmployeeSalary primary key (EmpNo,PayYear,PayMonth),
- constraint FK_EmployeeSalary foreign key (EmpNo)
- references Employee ([No])
- )
在上述案例，我們可以知道一個員工(Employee)在每年的每個月有可能因為績效達到某些企業設定的條件，因此可以得到應領取的獎金(EmployeeSalary) , 但也有可能某員工為新進員工，所以雖然在 Employee 資料表存在記錄，但 EmplyeeSalary 卻不存在任何記錄，如果此時企業的系統應用要求，列出所有員工(不論是否已有任何獎金），如有獎金也顯示出在前端界面，我們該如何利用 EntityFramework 去實戰這個很普遍到不行的案例呢？ 下面我就示範出比較適當的程式技巧。
首先在此我一樣是利用 Code-First Style 來操控 EntityFramework , 並前端界面我利用Windows Form來示範，前端界面圖示如下
在寫入資料 Button ，我先示範利用 EntityFramewok，來寫入所需的測試資料
1: private void btnInsert_Click(object sender, EventArgs e)
2: {
3: using (DBEntities entities = new DBEntities())
4: {
5: Employee emp1 = new Employee()
6: {
7: No = "001", //員工編號
8: HireDate = DateTime.Parse("2014/05/01"), //僱用日期
9: Name = "湯姆克魯斯", //員工姓名
10: };
11: EmployeeSalary salary1 = new EmployeeSalary()
12: {
13: bonus = 1000, //獎金
14: PayYear = 2014, //年
15: PayMonth = 9, //月
16: };
17: emp1.EmployeeSalary.Add(salary1);
18:
19: Employee emp2 = new Employee()
20: {
21: No = "002", //員工編號
22: HireDate = DateTime.Parse("2014/06/01"), //僱用日期
23: Name = "李奧納多", //員工姓名
24: };
25: EmployeeSalary salary2_1 = new EmployeeSalary()
26: {
27: bonus = 1000, //獎金
28: PayYear = 2014, //年
29: PayMonth = 9, //月
30: };
31: emp2.EmployeeSalary.Add(salary2_1);
32: EmployeeSalary salary2_2 = new EmployeeSalary()
33: {
34: bonus = 6000, //獎金
35: PayYear = 2014, //年
36: PayMonth = 10, //月
37: };
38: emp2.EmployeeSalary.Add(salary2_2);
39:
40:
41: Employee emp3 = new Employee()
42: {
43: No = "003", //員工編號
44: HireDate = DateTime.Parse("2014/10/01"), //僱用日期
45: Name = "基諾李維", //員工姓名
46: };
47:
48: entities.Employee.Add(emp1);
49: entities.Employee.Add(emp2);
50: entities.Employee.Add(emp3);
51:
52: entities.SaveChanges();
53: }
54: }
執行寫入資料按鈕後，後端資料庫結果為如下
測試資料有了以後，開始我們可以分別利用 Entity SQL 與 LINQ To Entities 兩種技巧來讀取
在 顯示資料(Entity SQL) 按鈕 ，程式如下
1: private void button1_Click(object sender, EventArgs e)
2: {
3: this.richTextBox1.Clear();
4:
5: using(DBEntities entites = new DBEntities())
6: {
7: var eSQL = @"SELECT A.No,A.Name,A.HireDate,B.PayYear,B.PayMonth,B.bonus
8: FROM Employee AS A OUTER APPLY A.EmployeeSalary AS B
9: ORDER BY A.No ";
10: var allEmpSalaryRecords = ((IObjectContextAdapter)entites).ObjectContext.CreateQuery<DbDataRecord>(eSQL).ToList();
11:
12: this.richTextBox1.AppendText("所有員工與獎金明細表：\r\n");
13: this.richTextBox1.AppendText("-------------------------------------------------------------------\r\n");
14:
15: //開始讀取
16: foreach(var empRecord in allEmpSalaryRecords)
17: {
18: if(empRecord["bonus"] != DBNull.Value)
19: {
20: this.richTextBox1.AppendText( "員工姓名 ： " + empRecord["Name"].ToString() + " \r\n");
21: this.richTextBox1.AppendText("（年/月） " + empRecord["PayYear"].ToString() + "/" + empRecord["PayMonth"].ToString()
+ " 獎金為 " + empRecord["bonus"].ToString() + " \r\n");
22:
23: }
24: else
25: {
26: this.richTextBox1.AppendText("員工姓名 ： " + empRecord["Name"].ToString() + " \r\n");
27: this.richTextBox1.AppendText(" 未有獎金 \r\n");
28: }
29: }
30:
31: }
32: }
執行結果如下
在 顯示資料(LINQ) 按鈕 ，程式如下
- private void button2_Click(object sender, EventArgs e)
- {
- this.richTextBox1.Clear();
- using (DBEntities entities = new DBEntities())
- {
- var allEmpSalaryRecords = (from emp in entities.Employee
- from empSalary in emp.EmployeeSalary.DefaultIfEmpty()
- orderby emp.No
- select new
- {
- emp.Name,
- PayYear = (int?)empSalary.PayYear,
- PayMonth = (int?)empSalary.PayMonth,
- Bonus = (decimal?)empSalary.bonus
- }).ToList();
- //開始讀取
- foreach (var empRecord in allEmpSalaryRecords)
- {
- if (empRecord.Bonus.HasValue)
- {
- this.richTextBox1.AppendText("員工姓名 ： " + empRecord.Name + " \r\n");
- this.richTextBox1.AppendText("（年/月） " + empRecord.PayYear.ToString() + "/" + empRecord.PayMonth.ToString()
- + " 獎金為 " + empRecord.Bonus.ToString() + " \r\n");
- }
- else
- {
- this.richTextBox1.AppendText("員工姓名 ： " + empRecord.Name + " \r\n");
- this.richTextBox1.AppendText(" 未有獎金 \r\n");
- }
- }
- }
- }
執行結果
透過以上2種讀取技巧，我追蹤EntityFramework 6都是自動產生以下 SQL 語法
- SELECT
- [Project1].[C1] AS [C1],
- [Project1].[No] AS [No],
- [Project1].[Name] AS [Name],
- [Project1].[HireDate] AS [HireDate],
- [Project1].[PayYear] AS [PayYear],
- [Project1].[PayMonth] AS [PayMonth],
- [Project1].[bonus] AS [bonus]
- FROM ( SELECT
- [Extent1].[No] AS [No],
- [Extent1].[HireDate] AS [HireDate],
- [Extent1].[Name] AS [Name],
- [Extent2].[PayYear] AS [PayYear],
- [Extent2].[PayMonth] AS [PayMonth],
- [Extent2].[bonus] AS [bonus],
- 1 AS [C1]
- FROM [dbo].[Employee] AS [Extent1]
- LEFT OUTER JOIN [dbo].[EmployeeSalary] AS [Extent2] ON [Extent1].[No] = [Extent2].[EmpNo]
- ) AS [Project1]
- ORDER BY [Project1].[No] ASC
以上 One-To-Many的EntityFramework 6 讀取方式，完整介紹完畢，希望對讀取有所幫忙
0 意見:
張貼留言
如您對本文有任何建議或意見，歡迎您留下您寶貴的意見！