Entity Framework 與 Stored Procedure - 基本的 Select

在企業裡，使用 Stored Procedure (以下簡稱 SP) 相當尋常，常常會看到有為數不少的 SP 在資料庫裡頭，而這些 SP 裡面的 T-SQL 內容是各式各樣，相信有許多朋友見過的 SP 一定是比我還要多，早期有些企業因為要避免過多的 SQL Statement 在程式中出現，所以會將程式中所需要執行操作的 SQL Statement 給移到資料庫裡然後建立 SP，然後再讓程式透過 ADO.NET 或是其他方式來執行，還有一種就是某些資料的存取有一定的規則，而這些為了要統一管理這些規則而避免各個系統有自己一套的處理方式，所以會把資料存取的方式給統一建立 SP，然後各系統在去透過這些 SP 來存取資料，另外還有的就是有些資料的存取是必須要在資料庫裡頭來做，所以建立 SP 來做處理，當然還有很多其他的原因。

現在很多企業很少有專責的 DBA 來管理資料庫（其實在我的工作經歷中，也只有一家公司有這樣的編制也真的有這麼一位 DBA），大多是程式設計師也必須要兼任 DBA 的角色，也因為如此，很多個開發人員也都可以使用到公司裡的資料庫們，這樣也導致一個 DB 中的 SP 會有很多開發人員去使用與維護，經常發生的就是原本運作好好的 SP 會突然無法正常使用，這樣的狀況很多都是 SP 被別人做了修改，而另一種情況就是 DB 裡有多個 SP 的內容都是做一樣的事情，但卻會有很多分身，然後名稱都不同但有很類似，甚至於有些 SP 的內容看起來已經是沒有在使用了，但又不感冒然移除，深怕移除之後讓某個古董級系統產生異常而無法運作。

以上這些都是目前很多企業在使用 SP 的現狀，SP 的內容也是程式，但偏偏很少有去做管理或是做版本控制，導致 DB 內的 SP 隨著時間的推移而越來越多，等到哪一天資料庫要更新版本或是出了狀況時，這些 SP 就讓人相當頭大。

一開始就說了一堆看似與主題無關的內容，但也說明了過多以及過度使用 SP 對於系統開發的影響，我個人是不反對使用 SP，因為有些資料的處理並不適合放在程式中，但我反對過多的商業邏輯放在 SP 裡面去處理，每個系統的商業邏輯不盡相同，所以就必須將商業邏輯給放在個系統的程式裡做處理，在需求變動的情況下，也只要去修改系統的程式即可。

而已經使用 Entity Framework 開發的系統通常很少會再去跟資料庫裡的 SP 打交道，但是有時候是無法避免要跟 SP 做接觸，此時就必須要學習怎麼透過 Entity Framework 去跟這些 SP 溝通。

 

---

操作示範執行環境：

ASP.NET MVC 4 (C#) with Entity Framework 5.0.0, MS SQL Server Express 2008 R2

 

前面東啦西扯地講了一堆，就直接看 EF 裡面怎麼使用 SP，在 Northwind 範例資料庫裡有以下這些的 SP，

接著我們以「usp_GetAllCustomer」「usp_GetCustomerByID」來做練習。

我們建立一個 ASP.NET MVC 專案，然後加入 ADO.NET 實體資料模型，除了加入 Table 外，我們也加入這次要用來練習的兩個 SP ，

建立 ADO.NET 實體資料模型後，並不會在 Model Diagram 裡看到加入的 SP，而是需要在模型瀏覽器（Model Browser）裡的 Function Imports 才看得到，

Model Browser 裡可以看到要使用這些 SP 時於傳入所需要使用到的 Parameter，而要看這些 SP 所回傳來的資料對應內容則是要使用 「Function Import Mapping」，

以下是「usp_GetAllCustomer」的 Mapping Details，

另外透過 SP 存取的資料型別並不是 Entity Type 而是 Complex Type，預設的型別名稱會是以 SP 名稱再加上「_Result」的後綴詞，

 

usp_GetAllCustomer

這個 SP 的內容很簡單，也就是取回全部的 Customer 資料，

ALTER PROCEDURE [dbo].[usp_GetAllCustomer]

AS

BEGIN

    SELECT 

        [CustomerID]

        ,[CompanyName]

        ,[ContactName]

        ,[ContactTitle]

        ,[Address]

        ,[City]

        ,[Region]

        ,[PostalCode]

        ,[Country]

        ,[Phone]

        ,[Fax]

    FROM [Northwind].[dbo].[Customers]

    order by [CustomerID] ASC

END

然後我們在 ASP.NET MVC Controller 程式裡用 EF 的 SP Function 來取得全部的 Customer 資料，

這邊要在提醒一次，傳回來的資料欄位雖然與 Customer 大致一樣，但型別並不是 Entity Type「Customer」，接回來的資料型別以及在 View 所使用 Model 型別為「usp_GetAllCustomer_Result」，

執行結果

 

usp_GetCustomerByID

取得全部資料後，接著我們再來練習怎麼取得單筆資料，以下是 usp_GetCustomerByID 的內容，

ALTER PROCEDURE [dbo].[usp_GetCustomerByID]

(

    @CustomerID NCHAR(5)

)

AS

BEGIN

    SELECT 

        [CustomerID]

        ,[CompanyName]

        ,[ContactName]

        ,[ContactTitle]

        ,[Address]

        ,[City]

        ,[Region]

        ,[PostalCode]

        ,[Country]

        ,[Phone]

        ,[Fax]

    FROM [Northwind].[dbo].[Customers]

    where CustomerID=@CustomerID

END

這邊我們需要傳給 usp_GetCustomerByID 一個 @CustomerID 的參數，程式的內容如下，

一定有人會質疑接回的資料只有一筆而已，為什麼最後還需要再加上 FirstOrDefault() 方法來取出那一筆資料呢？

因為 NorthwindEntities.usp_GetCustomerID() 這個方法所傳回的資料型別為「ObjectResult<T>」，

型別 ObjectResult<T> 繼承了 ObjectResult, IEnumerable<T>, IEnumerable，所以既使我們知道 usp_GetCustomerByID 回傳只有一筆資料，因為回傳的是 ObjectResult<T> 型別，所以要用 FirstOrDefault() 來取得那一筆資料出來，

View

執行結果

 

---

以上就是用兩個簡單範例來練習如何在 EF 去取得 Stored Procedure 的查詢結果，當然實際專案上的 SP 內容與回傳結果不是這麼地單純，所以在幾個基本操作介紹完之後再來探討較進階的處理。

 

以上