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()),'')

 

相關連結