執行 EntityFramework Stored Procedure 與如何接收多個回傳Table

透過EF與Linq的寫法有時無法處理太大量與過度複雜的運算,所以我們會把一些複雜的處理邏輯或效能考量的部份拉出來放到Stored Procedure裡面,這篇主要是要介紹如何透過EF執行Stored Procedure,並接收回傳的多個Table資料。

這次的資料沿用著上篇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