ADO.NET Entity Framework 實戰筆記（五）：Entity SQL 進階函數與類型系統全解析 (下)

承接上篇，本文深入探討 Entity SQL (ESQL) 的統計彙總函數、數學/日期/字串函數、查詢陳述式 (SELECT/WHERE/GROUP BY/HAVING/JOIN/CASE)、類型系統 (簡單型別/REF/ROW/集合/Object/CAST/OFTYPE/TREAT/IS)、命名空間引入 SQL Server 函數與 .NET 型別、以及關聯導航查詢。所有範例均附帶對應生成的 T-SQL，助你精通 ESQL 並理解其轉譯機制。

一、統計彙總函數

聯合使用與 GROUP BY 結合

// 多個彙總同時查詢，回傳 DbDataRecord
string esql = "SELECT MAX(it.ItemValue) AS Max, MIN(it.ItemValue) AS Min FROM ctx.DBItemList AS it";
var q = ctx.CreateQuery<DbDataRecord>(esql);
foreach (var r in q) Console.WriteLine($"Max:{r[\"Max\"]}, Min:{r[\"Min\"]}");
// Max:6, Min:1

// GROUP BY 分組彙總
esql = "SELECT it.ItemID AS ItemID, MAX(it.ItemValue) AS Max, MIN(it.ItemValue) AS Min FROM ctx.DBItemList AS it GROUP BY it.ItemID AS ItemID";
// 產生 T-SQL 含 GROUP BY [Extent1].[ItemID]

二、數學、日期、字串常用函數

三、查詢陳述式完整語法

SELECT 投影

// 回傳 DbDataRecord (匿名記錄)
string esql = "SELECT it.ItemValue AS a, it.NameID FROM [DBItemList] AS it";
var q = ctx.CreateQuery<DbDataRecord>(esql);
foreach (var r in q) Console.WriteLine($"{r[\"a\"]}, {r[\"NameID\"]}");

// 回傳實體集合 (需 VALUE 關鍵字)
esql = "SELECT VALUE it FROM [DBItemList] AS it";
var q2 = ctx.CreateQuery<DBItemList>(esql);

WHERE 篩選

esql = "SELECT VALUE it FROM [DBItemList] AS it WHERE (it.ItemValue=5 OR it.ItemValue=5) AND it.NameID='n01'";
// 產生 T-SQL: WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue] = 5)) AND ([Extent1].[NameID] = 'n01')

GROUP BY / HAVING

esql = "SELECT it.ItemID, SUM(it.ItemValue) AS ValueSum FROM [DBItemList] AS it GROUP BY it.ItemID";
esql = "SELECT it.ItemID, COUNT(it.ItemValue) AS ValueSum FROM DBItemList AS it GROUP BY it.ItemID HAVING SUM(it.ItemValue) > 5";
// HAVING 轉譯為外層 WHERE [GroupBy1].[A1] > 5

ORDER BY

esql = "SELECT VALUE it FROM [DBItemList] AS it ORDER BY it.ItemValue, it.ItemID DESC";
// T-SQL: ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC

JOIN 類型支援

• CROSS JOIN、INNER JOIN、LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN

CASE 條件表達式

esql = @"SELECT it.ItemID, it.ItemValue,
         (CASE WHEN it.ItemValue = 1 THEN '差'
               WHEN it.ItemValue BETWEEN 2 AND 4 THEN '好'
               ELSE '其他' END) AS ItemValueRemarks
         FROM ctx.DBItemList AS it";

四、ESQL 類型系統

簡單型別常值

• Null：IS NULL / IS NOT NULL
• Boolean：True / False
• 數值：123, 123.456, 23.34
• 字串：'abcd', N'中文', \"abcd\"
• DateTime：DATETIME'2007-11-11 22:22', cast('1977-11-11' as System.DateTime)
• Time：TIME'22:11'
• DateTimeOffset：DATETIMEOFFSET'2007-11-11 22:11 +02:00'
• Binary：BINARY'00ffaabb', X'ABCabc'
• Guid：Guid'0321AF86-0AA5-4a86-A086-1D789FA54AA3'

建構型別：REF、ROW、集合、Object

// REF: 取得實體參考
esql = "SELECT REF(it).ItemID FROM DBItem AS it";

// ROW: 建立匿名記錄
esql = "SELECT VALUE ROW(it.ItemValue, it.NameID, 'wxd' AS wxwinter) FROM ctx.DBItemList AS it";
esql = "SELECT ROW(it.ItemValue, it.NameID) AS wxd, it.ItemID FROM ctx.DBItemList AS it";

// 集合建構子
esql = "{1,2,3}"; // 或 MULTISET(1,2,3)
esql = "{ROW(1 AS a, 'wxd' AS wxwinter), ROW(2 AS a, 'lzm' AS wxwinter)}";

// Object: 直接在 SELECT 中建構 CLR 物件 (需對應型別有建構函式)
esql = "SELECT [WindowsFormsApplication8].[DBItemEx](it.ItemID + 'b') AS myObject FROM DBItem AS it";

型別轉換與繼承相關操作

// CAST 範例
esql = "SELECT VALUE CAST(it.ItemValue AS System.String) FROM ctx.DBItemList AS it";
esql = "USING System; SELECT VALUE CAST(it.ItemValue AS String) FROM ctx.DBItemList AS it";

// OFTYPE / TREAT / IS 範例 (假設 DBItemEx 繼承 DBItem)
esql = "OFTYPE((SELECT VALUE it FROM DBItem AS it), [NS].[DBItemEx])";
esql = "SELECT TREAT(it AS [NS].[DBItemEx]) FROM DBItem AS it";
esql = "SELECT TREAT(it AS [NS].[DBItemEx]) FROM DBItem AS it WHERE it IS OF ([NS].[DBItemEx])";

五、命名空間：引入 SQL Server 函數與 .NET 型別

// 1. USING SqlServer; 之後可直接呼叫 LEN、DATEADD 等
esql = "USING SqlServer; SELECT it.ItemValue, LEN(it.NameID) AS NameIDLEN FROM ctx.DBItemList AS it";

// 2. 完整限定名 SqlServer.LEN(...)
esql = "SELECT it.ItemValue, SqlServer.LEN(it.NameID) AS NameIDLEN FROM ctx.DBItemList AS it";
// 產生 T-SQL: LEN([Extent1].[NameID]) AS [C2]

// 3. USING System; 使用 .NET 型別進行 CAST
esql = "USING System; SELECT VALUE CAST(it.ItemValue AS String) FROM ctx.DBItemList AS it";
esql = "SELECT VALUE it FROM ctx.typeTest WHERE it.d = CAST('1977-11-11' AS System.DateTime)";

六、關聯導航與 KEY 函數

透過 KEY 函數與關聯集合進行導航查詢，等同於 LINQ 的 context.DBItem.First(p=>p.ItemID==\"a\").DBItemList.CreateSourceQuery()：

string esql = @"SELECT VALUE [TargetEntity] FROM
    (SELECT VALUE x FROM [ctx].[FK_DBItemList_DBItem] AS x
     WHERE KEY(x.[DBItem]) = ROW(@EntityKeyValue1 AS EntityKeyValue1)) AS [AssociationEntry]
    INNER JOIN [ctx].[DBItemList] AS [TargetEntity]
     ON KEY([AssociationEntry].[DBItemList]) = KEY(REF([TargetEntity]))";

var q = ctx.CreateQuery<DBItemList>(esql, new ObjectParameter("EntityKeyValue1", "a"));
foreach (var r in q) Console.WriteLine($"{r.AutoId}, {r.ItemValue}, {r.NameID}");

總結

Entity SQL 提供了接近 T-SQL 又具備物件導向特性的查詢語言，支援豐富的函數、型別系統、繼承多型操作、命名空間擴展，並能無縫銜接資料庫原生函數。掌握 ESQL 能在 LINQ to Entities 難以表達的複雜查詢 (如動態 SQL、資料庫特有函數、大量資料彙總) 場景中發揮關鍵作用。