Entity Framework 4.0 新增 T-SQL 直接查詢支援 - ExecuteStoreQuery

背景痛點

EF 3.5 時代，複雜報表查詢 (多表 Left Join、彙總) 用 LINQ 撰寫極其困難，E-SQL 語法又與 T-SQL 差異大。EF 4.0 引入 ExecuteStoreQuery、ExecuteStoreCommand、ExecuteFunction，完美支援原生 T-SQL，並自動將結果欄位映射至實體屬性。

LINQ 複雜 Left Join 範例 (舊寫法)

public List<Info> getMapUnitList(int MapId) {
    var var1 = from mu in epm.MapUnit
               join us in epm.UnitStatus on mu.Unit equals us.Unit into _UnitStatus
               join cu in epm.ContractUnit on mu.Unit equals cu.Unit into _ContractUnit
               where mu.Map.MapId == MapId
               select new Info() { ... status = _UnitStatus.Select(a => a.Status).FirstOrDefault(), ... };

    var var2 = from v1 in var1
               join ms in epm.MapStatus on new { map = v1.map, status = v1.status } equals new { map = ms.Map, status = ms.Status } into _MapStatus
               select new Info() { ... mapstatus = _MapStatus.FirstOrDefault(), ... };

    var var3 = from v2 in var2
               from ubtl in epm.UnitBoothType
               where ubtl.Unit.UnitId == v2.unit.UnitId
               join cc in epm.CustomerContract on v2.contract equals cc.Contract into _CustomerContract
               select new Info() { ... customer = _CustomerContract.Select(a => a.Customer).FirstOrDefault(), ... };

    return var3.ToList();
}

EF 4.0 ExecuteStoreQuery 簡潔寫法

protected void Page_Load(object sender, EventArgs e) {
    ERPEntities erp = new ERPEntities();
    string query = @"
        SELECT [BillPlanRule].[id] as BillID
             , qu.UnitNumber
             , [BeginDate], [EndDate]
             , ft.value as FeeType
             , f.value as Frequency
             , [Price], [ActualPrice], [Fee]
        FROM [BillPlanRule]
        LEFT JOIN dbo.SysParameter as ft ON [BillPlanRule].[FeeType] = ft.guid
        LEFT JOIN dbo.SysParameter as f  ON [BillPlanRule].[Frequency] = f.guid
        LEFT JOIN dbo.QuotationUnit as qu ON [BillPlanRule].[UGuid] = qu.guid
        WHERE uguid IN (SELECT guid FROM dbo.QuotationUnit
                        WHERE QuotationGuid IN (SELECT guid FROM dbo.Quotation WHERE id=3))
        ORDER BY BillID";

    // 直接對應屬性名稱 (大小寫不敏感)，支援 sql_variant 自動轉換
    ObjectResult<SysParameter> result = erp.ExecuteStoreQuery<SysParameter>(query);
    List<SysParameter> list = result.ToList(); // 此處才真正執行查詢 (延遲執行)
}

public class SysParameter {
    public int? BillID { get; set; }
    public DateTime? BeginDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string FeeType { get; set; }      // DB 為 sql_variant，自動轉字串
    public string Frequency { get; set; }    // DB 為 sql_variant
    public decimal? Price { get; set; }
}

關鍵特性

• 延遲執行：ExecuteStoreQuery 回傳 ObjectResult<T>，實際查詢發生在 ToList() 或迭代時。
• 自動映射：依類別屬性名稱與 SQL 查詢別名 (或欄位名) 對應，不區分大小寫。
• 支援非標準型別：如 sql_variant 自動轉換為 CLR 型別 (字串、數值等)。
• 適合複雜報表：可動態拼接 SQL 字串，彈性極高。

圖示說明

總結

EF 4.0 的 ExecuteStoreQuery 讓開發者在需要時能直接發揮 T-SQL 的威力，彌補 LINQ/E-SQL 在複雜報表查詢上的不足，是實務開發的利器。