Entity Framework的View Primary Key錯置疑雲

底下是一個重現問題的環境描述,細節頗多,我盡量長話短說。

假設在SQL Server上有三個Table,Player、Team及Membership,分別用來儲存人員、團隊及團隊成員隸屬關係,如下圖:

接著,我用Team LEFT JOIN Membership再LEFT JOIN Player的方式,產生一個View,用來產生團隊名稱與成員姓名的清單,如下圖:

接著,我們在Visual Studio 2010中新增一個ADO.NET Entity Data Model(.edmx),將Team、Player、Membership、vwTeam都加進Model。此時發現VS2010自動將vwTeam的TeamId、TeamName設為Primary Key。咦? 怪怪的,我在View上並沒有設定任何Key,應該是VS2010自動加上的,但直覺上也該是TeamName+UserName,用TeamId與TeamName並不合理,這樣真的沒問題嗎?

實際執行,就會發現這個不合理的Primary Key將引發爆炸...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var ctx = new LabEntities())
            {
                foreach (var t in ctx.vwTeam)
                {
                    Console.WriteLine("{0} {1} - {2}", t.TeamId, t.TeamName, t.UserName);
                }
            }
            Console.Read();
        }
    }
}

出現奇怪的結果!!

T001 FBI - Fox
T001 FBI - Fox
T002 Bloggers - Jeffrey
T002 Bloggers - Jeffrey
T002 Bloggers - Jeffrey
T002 Bloggers - Jeffrey
T003 Avengers - Captain America
T003 Avengers - Captain America
T003 Avengers - Captain America
T003 Avengers - Captain America

FBI Team有兩個成員沒錯,但UserName都變成Fox,Bloggers的四個UserName都是Jeffrey,復仇者聯盟則跑出4位美國隊長。成員人數是對的,但所有成員的UserName全被換成該團隊第一名成員的姓名。推測是TeamId、TeamName被標註為Primary Key,具有識別性的UserName卻不是Primary Key之一,所造成的後果。

爬文後,在stackoverflow找到相關討論,看起來是EF的已知Issue,目前大家用的解法是在宣告View欄位時,加上ISNULL()強迫EF將該欄位視為Primary Key;加上NULLIF()強迫EF不要將該欄位當成Primary Key。於是,我在vwTeam的查詢語法動點手腳另存成vwTeam2,在TeamId及UserName套上ISNULL(),在TeamName加上NULLIF(),如此應可強迫EF將TeamId, UserName指定成Primary Key。

在edmx加入vwTeam2,果然如預期,Primary Key出現在TeamId及UserName上:

而foreach (var t in ctx.vwTeam2)的結果,總算也正確了。

T001 FBI - Fox
T001 FBI - Scully
T002 Bloggers - Jeffrey
T002 Bloggers - Darkthread
T002 Bloggers - Captain America
T002 Bloggers - Iron Man
T003 Avengers - Captain America
T003 Avengers - Iron Man
T003 Avengers - Hulk
T003 Avengers - Thor

嚴格說起來,為了EF特地調整View查詢語法的做法笨拙又囉嗦,算不上漂亮的解法。另一種做法是在執行"Update Model from Database..."後手工調整XML修改Primary Key,但缺點是每次由DB同步後都得再重調,也不怎麼高明。不過要解決這個議題,看來也只能兩個爛做法擇一~

所以,這是Entity Framework天殺的Bug,微軟RD該死嗎? 且慢,要刮別人鬍子前,先把自己的刮乾淨!

仔細一想,EF判斷Primary Key乃依據欄位是否Nullable,而vwTeam為了要納入沒有成員的Team,採取Team LEFT JOIN Membership的寫法,UserName本來就可能因JOIN不到無值,既然可能為NULL,又怎麼能當作Primary Key呢? 換句話說,原本期望TeamName + UserName當PK的想法與vwTeam的特質是矛盾的,EF指定TeamId與TeamName為PK並無不當!

試著再宣告一個vwTeam3,但改用Team JOIN Membership JOIN Player:

將vwTeam3加入edmx,這回UserName就被視為Primary Key之一了。

慚愧,純粹是自己擺烏龍,誤會一場,但意外學會EF決定View Primary Key的邏輯,也算有所收獲。但是,整個測試過程仍有一個謎團未解,當vwTeam PK被誤設為TeamId, TeamName時,查詢結果出現成員UserName全被置換成第一名成員的不合理情境,倒蠻像是個Bug,留待下回再深入研究。

歡迎推文分享:
Published 28 June 2012 09:55 PM 由 Jeffrey
Filed under:
Views: 9,764



意見

# KKBruce said on 29 June, 2012 05:19 AM

1. VS2010應該是EF4.x 版本,用 NuGet 可以試試 EF 5.x RC 版本,看是否一樣。

2. Linq 出來的 SQL 對嗎?

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<June 2012>
SunMonTueWedThuFriSat
272829303112
3456789
10111213141516
17181920212223
24252627282930
1234567
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication