作者我在使用Entity Framewok開發時，最常聽到的一句話就是，Entity Framework 是否"好像"效能很慢喔，在微軟剛推出Entity Framewok前（事實上微軟真的這方面算是進展很慢，其他程式陣營早就有相關的Framework，例如 Java 的 Hibernate，不過後來真的速度改版的很迅速，微軟都這樣玩法），我會很肯定的回答"是"，如果當時要採用 Entity Framewok時，我想可能最大的益處就是可以在程式開發過程時，比較可以使用"概念模型"（Conceptual Model）以及 OOP(物件導向)的思維導向，也可以避免程式開發人員在寫程式盡可能避免了 SQL Script，當然也避免了一些開發人員真的不太會寫 SQL，相信我真的還遇到過，不過隨著Entity Framewok 發展到 Version 6 UP.., 我相信大多數是可以依靠 EF(Entity Framewok)，而且開發速度的優勢已經是大多數優先考慮採用的原因，尤其現在到處都是 WEB API、MVC架構（EF歸於 Model Layer），我們更需要好好學習一下，所以我打算寫幾篇關於 EF效能的底層技術與原理，畢竟 Entity Framewok 雖然把底層 SQL的工作，都幫你處理好，但你知道為何有時似乎效能真的好像還是很慢，所以我想直接跟讀者說一些原理，這也是市面上書籍似乎都沒在探討的.....
{
try
{
using (CRMEF ef = new CRMEF())
{
CustomerCate cate1 = new CustomerCate() { Code = "Retail", Name = "零售商" };
CustomerCate cate2 = new CustomerCate() { Code = "Direct", Name = "直客" };
CustomerCate cate3 = new CustomerCate() { Code = "EC", Name = "網路消費者" };
Customer cust1 = new Customer() { CustomerCate = cate1, No = "001", CName = "智慧零售商", ENmae = "Smart" };
CustomerEvent custEvent1 = new CustomerEvent() { Customer = cust1, EventID = Guid.NewGuid().ToString(), Desc = "詢問新產品目錄", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent1);
CustomerEvent custEvent2 = new CustomerEvent() { Customer = cust1, EventID = Guid.NewGuid().ToString(), Desc = "購買10套 網智數位 CRM 套裝軟體", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent2);
Customer cust2 = new Customer() { CustomerCate = cate2, No = "002", CName = "柯P", ENmae = "柯P" };
CustomerEvent custEvent3 = new CustomerEvent() { Customer = cust2, EventID = Guid.NewGuid().ToString(), Desc = "尋找物聯網的合作廠商", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent3);
Customer cust3 = new Customer() { CustomerCate = cate3, No = "003", CName = "連勝吻", ENmae = "連勝吻" };
CustomerEvent custEvent4 = new CustomerEvent() { Customer = cust3, EventID = Guid.NewGuid().ToString(), Desc = "網路行銷推廣", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent4);
ef.SaveChanges();
MessageBox.Show("Test Insert OK");
}
}
catch { }
我先在此介紹一個Entity Framework 的術語 Lazy Load , 它代表 延遲載入 預設值是成立（true) 。為了解釋何謂Lazy Load，我自己帶入一個案例來進行解釋
例如在我們 CRM系統裏有 [客戶類型]類別 (CustCate Class) , 而每個[客戶類型]可以有多個興趣的[客戶]類別 (Customer Class)，此時很典型存在所謂的一對多 One-To-Many 的模型 ；而每個[客戶]類別也可以記錄此客戶相關的[客戶事件]類別(CustomerEvent Class)
，下面我列出了案例模型圖
根據上述模型我們來實作一個 Code First EF Model
實作 Customer 、CustCate、CustomerEvent Class Code ....
[Table("Customer")]
public partial class Customer
{
public Customer()
{
CustomerEvent = new HashSet<CustomerEvent>();
}
[Key]
[StringLength(15)]
public string No { get; set; }
[StringLength(10)]
public string CName { get; set; }
[StringLength(10)]
public string ENmae { get; set; }
[StringLength(10)]
public string CateCode { get; set; }
public virtual CustomerCate CustomerCate { get; set; }
public virtual ICollection<CustomerEvent> CustomerEvent { get; set; }
}
[Table("CustomerCate")]
public partial class CustomerCate
{
public CustomerCate()
{
Customer = new HashSet<Customer>();
}
[Key]
[StringLength(10)]
public string Code { get; set; }
[Required]
[StringLength(15)]
public string Name { get; set; }
public virtual ICollection<Customer> Customer { get; set; }
}
[Table("CustomerEvent")]
public partial class CustomerEvent
{
[Key]
[StringLength(36)]
public string EventID { get; set; }
[StringLength(15)]
public string CustNo { get; set; }
[Required]
[StringLength(300)]
public string Desc { get; set; }
public DateTime CreateDate { get; set; }
public virtual Customer Customer { get; set; }
}
實作一個繼承 DbContext 的 EF 存取容器
public class CRMEF : DbContext
{
public CRMEF()
: base("name=EF")
{
}
public virtual DbSet<Customer> Customer { get; set; }
public virtual DbSet<CustomerCate> CustomerCate { get; set; }
public virtual DbSet<CustomerEvent> CustomerEvent { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Customer>().ToTable("Customer");
modelBuilder.Entity<CustomerCate>().ToTable("CustomerCate");
modelBuilder.Entity<CustomerEvent>().ToTable("CustomerEvent");
modelBuilder.Entity<Customer>().HasMany(e => e.CustomerEvent)
.WithOptional(e => e.Customer)
.HasForeignKey(e => e.CustNo);
modelBuilder.Entity<CustomerCate>().HasMany(e => e.Customer)
.WithOptional(e => e.CustomerCate)
.HasForeignKey(e => e.CateCode);
base.OnModelCreating(modelBuilder);
}
}
我也設計一個前端界面
在寫入測試資料 Button，先寫入基本資料以利後面的解說
private void btnTestData_Click(object sender, EventArgs e){
try
{
using (CRMEF ef = new CRMEF())
{
CustomerCate cate1 = new CustomerCate() { Code = "Retail", Name = "零售商" };
CustomerCate cate2 = new CustomerCate() { Code = "Direct", Name = "直客" };
CustomerCate cate3 = new CustomerCate() { Code = "EC", Name = "網路消費者" };
Customer cust1 = new Customer() { CustomerCate = cate1, No = "001", CName = "智慧零售商", ENmae = "Smart" };
CustomerEvent custEvent1 = new CustomerEvent() { Customer = cust1, EventID = Guid.NewGuid().ToString(), Desc = "詢問新產品目錄", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent1);
CustomerEvent custEvent2 = new CustomerEvent() { Customer = cust1, EventID = Guid.NewGuid().ToString(), Desc = "購買10套 網智數位 CRM 套裝軟體", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent2);
Customer cust2 = new Customer() { CustomerCate = cate2, No = "002", CName = "柯P", ENmae = "柯P" };
CustomerEvent custEvent3 = new CustomerEvent() { Customer = cust2, EventID = Guid.NewGuid().ToString(), Desc = "尋找物聯網的合作廠商", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent3);
Customer cust3 = new Customer() { CustomerCate = cate3, No = "003", CName = "連勝吻", ENmae = "連勝吻" };
CustomerEvent custEvent4 = new CustomerEvent() { Customer = cust3, EventID = Guid.NewGuid().ToString(), Desc = "網路行銷推廣", CreateDate = DateTime.Now };
ef.CustomerEvent.Add(custEvent4);
ef.SaveChanges();
MessageBox.Show("Test Insert OK");
}
}
catch { }
}
執行後，我們可以看到後端資料庫就存在一下資料
現在有了案例的資料後，我就可以開始解釋何謂 Lazy Loading，在上述案例我們如果需要列出所有客戶以及他相關的活動事件，以及此客戶歸屬的類別，我把它寫在 "Lazy Loading" Button Click:
- private void btnLazyLoading_Click(object sender, EventArgs e)
- {
- this.richTextBox1.Clear();
- this.richTextBox1.AppendText("使用 Lazy Loading 讀取，系統預設行為! \r\n");
- this.richTextBox1.AppendText("------------------------------------------------------------------------- \r\n");
- using (CRMEF ef = new CRMEF())
- {
- var customers = ef.Customer;
- foreach (var cust in customers)
- {
- this.richTextBox1.AppendText("客戶編號 : " + cust.No + " 客戶姓名 ：" + cust.ENmae + "\t 類型 ：" + cust.CustomerCate.Name + "\r\n");
- this.richTextBox1.AppendText("--------------------------------事件記錄------------------------------------ \r\n");
- foreach (var evt in cust.CustomerEvent)
- {
- this.richTextBox1.AppendText(evt.Desc + "\r\n");
- }
- this.richTextBox1.AppendText("\r\r\n");
- }
- }
- }
執行後結果如下
看樣子結果都是沒錯的，但各位我要告訴你們，你知道 上述結果 Entity Framewok 是幫我們產生多少次 SQL語法嗎？ , 以及來回跟後端資料庫查詢幾次嗎？
答案是 7 次 ，也就是說白話一點，就是 EF要來回跟後端資料庫溝通要得資料7次，才可以把我們要呈現的資料，完全取回來，各位如果客戶資料不是現在案例的3筆，而100筆...1000筆....甚至 10000筆以上呢？
我分別把我追蹤後的SQL 語法寫下：
第 1 次 SQL :
SELECT
[Extent1].[No] AS [No],
[Extent1].[CName] AS [CName],
[Extent1].[ENmae] AS [ENmae],
[Extent1].[CateCode] AS [CateCode]
FROM [dbo].[Customer] AS [Extent1]
[Extent1].[No] AS [No],
[Extent1].[CName] AS [CName],
[Extent1].[ENmae] AS [ENmae],
[Extent1].[CateCode] AS [CateCode]
FROM [dbo].[Customer] AS [Extent1]
第 2 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'Retail'
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'Retail'
第 3 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'001'
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'001'
第 4 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'Direct'
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'Direct'
第 5 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'002'
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'002'
第 6 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'EC'
[Extent1].[Code] AS [Code],
[Extent1].[Name] AS [Name]
FROM [dbo].[CustomerCate] AS [Extent1]
WHERE [Extent1].[Code] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(10)',@EntityKeyValue1=N'EC'
第 7 次 SQL :
exec sp_executesql N'SELECT
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'003'
[Extent1].[EventID] AS [EventID],
[Extent1].[CustNo] AS [CustNo],
[Extent1].[Desc] AS [Desc],
[Extent1].[CreateDate] AS [CreateDate]
FROM [dbo].[CustomerEvent] AS [Extent1]
WHERE [Extent1].[CustNo] = @EntityKeyValue1',N'@EntityKeyValue1 nvarchar(15)',@EntityKeyValue1=N'003'
這就是 Entitfy Framework 的預設行為 Lazy Loading , 上述 C# 語法我們先列舉了所有客戶的清單，此時 EF 就會產生 第一個 SQL 語法 是讀取後端 [dbo].[Customer] Table 的 SQL ，
而我們再依序根據每筆客戶分別讀取了客戶類型，此時 EF 又會去跟後端資料庫要求讀取 [dbo].[CustomerCate] Table ，所以有幾筆客戶資料，他就會來回跟資料庫讀取 [dbo].[CustomerCate] Table，各位這就是效能瓶頸所在
接下來我們繼續讀取每筆客戶的系統事件，此時 EF 又要懶惰 (Lazy Loading)的再去跟後端資料庫取得 [dbo].[CustomerEvent] 資料表，看吧！，系統如果資料量大一點，這樣大部分人使用 EF （Entitfy Framework) ，一定死了，被客戶抱怨死了，
此時通常工程師或開發人員就會抱怨 Entity Framewok好爛，效能好差，還是用ADO.Net好....這也是我公司工程師常跟我說類似的話，但如果你知道底層的原理，這都是可以操控自如的，完全可以任意操控EF，使它可以不用笨到來來回回奔走，
至於如何做呢？ 就讓我留著下一回好好地告訴大家。。。。
