The Will Will Web | 解決 SQL Server 檢視表 (Views) 無法匯入 EDMX 的問題

The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

解決 SQL Server 檢視表 (Views) 無法匯入 EDMX 的問題

前陣子在教學的過程中,有位學員問了一個 Entity Framework 的問題,他在 SQL Server 資料庫中建立了一個報表用的檢視表 (Views),然後想要匯入到 Entity Framework 的 EDMX 實體資料模型中,但卻怎樣都匯不進去,主要原因就出在 Entity Framework 無法自動推斷該檢視表的主索引鍵為何,所以才會無法匯入,而本篇文章將說明匯入 SQL Server 檢視表 (Views) 到 Entity Framework 的注意事項。

我們先看一下,我們已經先建立好一個 Fabrics.edmx 實體資料模型檔,然後接著建立一個檢視表名為 vw_TableCount,而這個��視表在定義的時候,最明顯的特徵就是沒有 FROM 子句。

CREATE VIEW [dbo].[vw_TableCount]
AS SELECT
(SELECT COUNT(*) FROM dbo.Client)
AS ClientNum,
(SELECT COUNT(*) FROM dbo.Product)
AS ProductNum,
(SELECT COUNT(*) FROM dbo.[Order])
AS OrderNum

接著在 ADO.NET 實體資料模型的設計工具中,在空白處按下滑鼠右鍵,選擇【從資料庫更新模型

將這個 View 給勾選起來,並按下【完成】

此時你會發現在 EDMX 設計工具中,完全沒有出現這個被新增的 vw_TableCount 檢視表。

但如果你改以 XML 編輯器開啟 Fabrics.edmx 檔案,可以看出這個無法匯入的原因:

錯誤訊息如下:

<!--產生期間發現錯誤:
警告 6013: 資料表/檢視 'G:\PROJECTS\xxx\APP_DATA\FABRICS.MDF.dbo.vw_TableCount' 未定義主索引鍵,也無法推斷有效的主索引鍵此資料表/檢視已被排除如果要使用此實體,您必須檢閱您的結構描述,加入正確的索引鍵,並將它取消註解
<EntityType Name="vw_TableCount">
<Property Name="ClientNum" Type="int" />
<Property Name="ProductNum" Type="int" />
<Property Name="OrderNum" Type="int" />
</EntityType>-->

由於 Entity Framework 的運作,必須要有主索引建 (Primary Key) 才能正常運作,當 Entity Framework 無法推斷主索引件是誰時,那就會完全無法運作了。

在我們這個檢視表的輸出結果,你其實很清楚,這張檢視表最多只會有一筆資料輸出,不會有資料重複的問題。所以,我們現在可以有兩個方法解決此問題:

  1. 修改 EDMX 的 XML 內容 ( SSDL, CSDL, MSL ),並自行指定 SSDL 中主索引鍵是哪個欄位。
  2. 修改 SQL 檢視表的語法,明確提示 Entity Framework,告訴它哪個欄位才是 Primary Key

這裡我們會以「第二種」方法為主,因為第一種修正方式很有可能 EDMX 會被改壞或下次忘記這樣改。

在 EDMX 匯入 SQL Server 檢視表的時候,有兩種明確提示的方法:

  1. SELECT 子句中,如果有欄位使用 ISNULL 函式,那麼該欄位會自動成為 EF 的主索引鍵
  2. SELECT 子句中,如果有欄位使用 NULLIF 函式,那麼該欄位一定不會成為 EF 的主索引鍵

在我們這個例子裡,因為錯誤訊息顯示「未定義主索引鍵,也無法推斷有效的主索引鍵」,所以我們需要的是上述第 1 種明確提示方法,也就是在 SELECT 子句中採用 ISNULL 函式即可!

現在我們將 View 修改如下:

CREATE VIEW [dbo].[vw_TableCount]
AS SELECT
ISNULL(NULL, 1) AS ID,
(SELECT COUNT(*) FROM dbo.Client)
AS ClientNum,
(SELECT COUNT(*) FROM dbo.Product)
AS ProductNum,
(SELECT COUNT(*) FROM dbo.[Order])
AS OrderNum

我們將該檢視表額外新增一個 ID 欄位,並且固定輸出一個數字 1 即可。接著在 ADO.NET 實體資料模型的設計工具中,在空白處按下滑鼠右鍵,選擇【從資料庫更新模型】,並將這個檢視表給勾選起來,按下【完成】,該檢視表就會正確被匯入 Fabrics.edmx 實體資料模型檔中!

即便如此,你還是可以再次打開 EDMX 檔案,只是現在的錯誤訊息變成如下:

<!--產生期間發現錯誤:
警告 6002: 資料表/檢視 'G:\PROJECTS\xxx\APP_DATA\FABRICS.MDF.dbo.vw_TableCount' 未定義主索引鍵已推斷此索引鍵,並將定義建立成唯讀的資料表/檢視。-->

由於 Visual Studio 的 ADO.NET 實體資料模型的設計工具已經幫你自動「推斷此索引鍵」,所以 vw_TableCount 這個實體 ( Entity ) 已經可以正常使用了!

當然,如果你的 Entity Framework 遇到其他類似的問題,有些則是可以匯入檢視表,但執行結果會有問題,都可以利用這個小技巧來解決。

 

備註:本篇文章的範例程式已經放上 GitHub:https://github.com/doggy8088/EFViewPrimaryKey

 

重點摘要

在 EDMX 匯入 SQL Server 檢視表的時候,有兩種明確提示的方法:

  1. SELECT 子句中,如果有欄位使用 ISNULL 函式,那麼該欄位會自動成為 EF 的主索引鍵
  2. SELECT 子句中,如果有欄位使用 NULLIF 函式,那麼該欄位一定不會成為 EF 的主索引鍵

其他可能的小技巧:

  • 若檢視表中有用到 UNION 語法,請注意不要讓主索引鍵重複了!
  • 如果你確信你的檢視表要回傳完整的結果,也可以考慮用以下語法輸出 ID 欄位的值:
    ISNULL(newid(), '')
  • 如果會用到 UNION 語法串接多個結果集,有部分結果集沒有主索引鍵的話,也可以用上一點的技巧,不過記得要做適當轉型,確保不同結果集的主索引鍵欄位型別一致,例如:
    ISNULL(CONVERT(VARCHAR(36), newid()),'')

 

相關連結