[EF] 使用 View

公司有個供應商相關邏輯是，供應商會有很多聯絡人，但必須設定一個主要聯絡人，也因此 Supplier Table 理論上應該有主要聯絡人相關資料，同事提出該盲點來討論後是利用 LINQ 來解決，那我就練習一下在 Entity Framework 使用 View 來 try 看看囉

完整情境為一個儀器會有多個儀器供應商，使用者需求是要在畫面顯示供應商主要聯絡人相關聯絡資訊

vwSupplier TSQL 語法

CREATE VIEW [dbo].[vwSupplier]
AS
 SELECT
  S.SplNO , 
  S.SplName ,
  SC.Contact ,
  SC.Mobilephone , 
  SC.Telephone , 
  SC.Fax , 
  SC.Email
 FROM Supplier AS S
  LEFT JOIN SupplierContact AS SC ON S.SplNO = SC.SplNO 
           AND SC.[Sign] = 1
GO

利用 edm 把 InstruSPLNO 和 vwSupplier 抓進來

vwSupplier 實體索引鍵是 SPLNO 和 SPLName，其實只要 SPLNO 就行，把 SPLName 移除

加入兩者關聯 (Association)

InstruSPLNO 和 vwSupplier 為多對 1

關聯 (Association) 設定完成

完成關聯 (Association) 後，還必須建立參考限制式 (Refrence Constraint)，除了點選屬性 => 參考限制式外，也可以直接點擊 關聯 (Association) 的圖示，也可以進入該畫面

設定參考限制式 (Refrence Constraint)

C# Code

using System.Data.Entity;

namespace EFView
{
    public partial class Form1 : Form
    {
        EFDbContext context;
        BindingSource bsInstru;

        public Form1()
        {
            InitializeComponent();
            context = new EFDbContext();
            bsInstru = new BindingSource();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            context.InstruSPLNO.Include(c => c.vwSupplier).Load();
            bsInstru.DataSource = context.InstruSPLNO.Local.ToBindingList();

            dgvInstruSPLNO.AutoGenerateColumns = false;
            dgvInstruSPLNO.DataSource = bsInstru;
        }

        private void dgvInstruSPLNO_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            var row = dgvInstruSPLNO.Rows[e.RowIndex];

            string ColSPLName = "ColSPlName";
            string ColContact = "ColContact";

            if (e.RowIndex >= 0 &&
                row != null &&
                (dgvInstruSPLNO.Columns[ColSPLName].Index == e.ColumnIndex ||
                dgvInstruSPLNO.Columns[ColContact].Index == e.ColumnIndex))
            {
                var instruSPLNO = (InstruSPLNO)row.DataBoundItem;
                if (instruSPLNO != null && instruSPLNO.vwSupplier != null)
                {
                    if (dgvInstruSPLNO.Columns[e.ColumnIndex].Name == ColSPLName)
                    {
                        e.Value = instruSPLNO.vwSupplier.SplName;
                    }
                    else
                    {
                        e.Value = instruSPLNO.vwSupplier.Contact;
                    }
                }
            }
        }
    }
}

執行結果

利用 SQL Profile 來了解產生的 TSQL

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[INO] AS [INO], 
    [Extent1].[Kind] AS [Kind], 
    [Extent1].[SPLNO] AS [SPLNO], 
    [Extent1].[OrderBy] AS [OrderBy], 
    [Extent2].[SplNO] AS [SPLNO1], 
    [Extent2].[SplName] AS [SplName], 
    [Extent2].[Contact] AS [Contact], 
    [Extent2].[Mobilephone] AS [Mobilephone], 
    [Extent2].[Telephone] AS [Telephone], 
    [Extent2].[Fax] AS [Fax], 
    [Extent2].[Email] AS [Email]
    FROM  [dbo].[InstruSPLNO] AS [Extent1]
    LEFT OUTER JOIN (
SELECT 
    [vwSupplier].[SplNO] AS [SplNO], 
    [vwSupplier].[SplName] AS [SplName], 
    [vwSupplier].[Contact] AS [Contact], 
    [vwSupplier].[Mobilephone] AS [Mobilephone], 
    [vwSupplier].[Telephone] AS [Telephone], 
    [vwSupplier].[Fax] AS [Fax], 
    [vwSupplier].[Email] AS [Email]
    FROM [dbo].[vwSupplier] AS [vwSupplier]) AS [Extent2] ON [Extent1].[SPLNO] = [Extent2].[SplNO]

練習時把 vwSupplier 加進 edm 內後，出現下面警告文字，且推斷的索引鍵也並不是最理想的

錯誤 6002: 資料表/檢視 'IC.dbo.vwSupplier' 未定義主索引鍵。已推斷此索引鍵，並將定義建立成唯讀的資料表/檢視

好奇查了資料，下面兩位大神已經整理好重點啦

• 解決 SQL Server 檢視表 (Views) 無法匯入 EDMX 的問題
• Entity Framework的View Primary Key錯置疑雲

索引鍵推論的潛規則

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

• 在 SELECT 子句中，如果有欄位使用 ISNULL 函式，那麼該欄位會自動成為 EF 的主索引鍵。

• 在 SELECT 子句中，如果有欄位使用 NULLIF 函式，那麼該欄位一定不會成為 EF 的主索引鍵。