程式碼學習不歸路

這次的資料沿用著上篇EntityFramework 基本效能簡介與調校

最近遇到需要有複雜的邏輯計算，為簡化Linq的效能問題，我將計算的邏輯做切割

需要透過Entity Framework去執行Stored Procedure，並將回傳的資料產生報表

以往來說，我會使用Stored Procedure回傳一個自定義的表格(complex type table)

ALTER PROCEDURE [dbo].[spTest]
AS
BEGIN	
	
	select u.userName, d.description, d.currencyCode 
	from users u
	inner join ExpenseDetail  d on u.userID = d.userID
	order by u.userName

END

透過Entity Framework，我們可以把該stored procedure加進來

即會有一個class會根據SQL撈出來的結果被自動建立起來

 public partial class spTest_Result
    {
        public string userName { get; set; }
        public string description { get; set; }
        public string currencyCode { get; set; }
    }

而我們可以很簡單的去呼叫這個stored procedure

 using (var db = new NorthwindEntities())
 {
      List<spTest_Result> result = db.spTest().ToList();
 }

到目前為止對我們而言都是小菜一碟

 

但如果我們今天要的是更複雜的資料，比方說是多個Tables，我們該怎麼處理，修改後如下圖SQL(這裡多了一個getdate來跟上面的資料做區分)

ALTER PROCEDURE [dbo].[spTest]
AS
BEGIN	
	--table 1
	select * from users
	--table 2
	select * from ExpenseDetail
	--table 3
	select u.userName, d.description, d.currencyCode, getdate() as executeDate
	from users u
	inner join ExpenseDetail  d on u.userID = d.userID
	order by u.userName

END

我們將之前的spTest_Result砍掉重新加後，會發生什麼事

會只剩第一個Table的Schema而已，其他的在哪？

目前Entity Framework用designer產生的class並不支援多個Tables的回傳

所以我們必須改成其他寫法才能達到目地

以上Table 1, 2 都是透過Entity Framework幫我們建立出來的，但是table3的話由於是自定義的

所以我們需要建立Model 3要對應的Class

 public class CustomModel
    {
        public string userName { get; set; }
        public string description { get; set; }
        //for test mapping, this column will not being mapped
        public string _currencyCode { get; set; }
        public DateTime executeDate {get;set;}
    }

 using (var db = new NorthwindEntities())
            {

                var cmd = db.Database.Connection.CreateCommand();
                cmd.CommandText = "exec [dbo].[spTest]";
                db.Database.Connection.Open();

                var reader = cmd.ExecuteReader();
                var lstUsers = ((IObjectContextAdapter)db)
                  .ObjectContext
                  .Translate<Users>(reader).ToList();

                reader.NextResult();
                var lstExpenseDetail = ((IObjectContextAdapter)db)
                  .ObjectContext
                  .Translate<ExpenseDetail>(reader).ToList();

                reader.NextResult();
                var lstCustomResult = ((IObjectContextAdapter)db)
                  .ObjectContext
                  .Translate<CustomModel>(reader).ToList();
             
            }

可以透過reader.NextResult() 去取得下一個table resultSet的結果

以第一個Table是抓users來看，這個table只有2個columns

當執行reader.NextResult()要取得的是ExpenseDetail 時，我們可以發現reader的FieldCount變成4了

最後我們可以看Table 3, custom class的結果

可以發現我們在SQL取得的名稱是currencyCode，但我們故意在custom class裡面定義成 _currencyCode

所以在做轉換時會對不到，該欄位就會是NULL, 而其他欄位都可以很正常取出

在做資料mapping的原理，可以參考這篇針對 Web API 回轉的Json to Class，原理其實是一樣的

 

 

既然Entity Framework這麼神，可以回傳多個table，那有什麼缺點呢？

目前為止，我發現最大的缺點應該就是不能update資料回去，但這也是很合理的

因為你的model是透過一個column一個column去做mapping，跟透過Entity Framework去取row的方式不太一樣

                    var reader = cmd.ExecuteReader();
                    var lstUsers = ((IObjectContextAdapter)db)
                      .ObjectContext
                      .Translate<Users>(reader).ToList();

                    //this will NOT work
                    Users user1 = lstUsers.Where(m => m.userID == 1).FirstOrDefault();
                    user1.userName = "test1";
                    db.SaveChanges();
                    
                    //this will work
                    Users user2 = db.Users.Where(m => m.userID == 2).FirstOrDefault();
                    user2.userName = "test1";
                    db.SaveChanges();

若是我們需要把資料update回去的，可能還是透過Entity Framework取資料再用include把資料抓進來較為理想

若是有效能上考量非得用Stored Procedure抓資料產生報表，那用多個表格回傳可能會較適合

如此一來，我們就根據不同的情況決定是要用Entity Framework 的include來做，還是用Stored Procedure來回傳table了

但若是你要做大量的資料運算又要update資料回去，就得自己衡量哪種做法較為適合了。

 

參考資料

http://www.khalidabuhakmeh.com/entity-framework-6-multiple-result-sets-with-stored-procedures