Entity Framework的View Primary Key錯置疑雲
1 | 12,181 |
底下是一個重現問題的環境描述,細節頗多,我盡量長話短說。
假設在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,留待下回再深入研究。
Comments
# by KKBruce
1. VS2010應該是EF4.x 版本,用 NuGet 可以試試 EF 5.x RC 版本,看是否一樣。 2. Linq 出來的 SQL 對嗎?