The Will Will Web

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

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

📅 2013/11/07 23:45 📁 Entity Framework

前陣子在教學的過程中，有位學員問了一個 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()),'')

 

相關連結

留言評論