Entity Data Model (EDM) 深度剖析 (中)：Entity SQL 查詢技術全攻略——ObjectQuery 實體、基本型別、匿名型別與參數化查詢

Entity SQL (ESQL) 是基於概念模型的查詢語言，延伸標準 SQL 加入概念層級查詢能力，不綁定特定資料庫語法，支援跨資料庫部署。ESQL 為文字型、集合導向、延遲繫結，受 T-SQL 影響深。本文示範透過 ObjectQuery<T> 執行 ESQL 的四大查詢模式。

1. 查詢實體型別集合

方式一：CreateQuery 工廠方法

NorthwindEntities context = new NorthwindEntities();
var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp";
var query = context.CreateQuery<Employee>(sql);

foreach (var emp in query) // 延遲執行：首次迭代才發送 SQL
    Console.WriteLine("{0} {1} {2} {3}", emp.EmployeeID, emp.FirstName, emp.LastName, emp.Country);

VALUE 關鍵字表示回傳完整實體列。CreateQuery 回傳 ObjectQuery<T>，採延遲載入。

方式二：直接建構 ObjectQuery

var sql = "NorthwindEntities.Employees"; // 簡略語法
ObjectQuery<Employee> query = new ObjectQuery<Employee>(sql, context);

加入 WHERE 條件

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp WHERE emp.Country = 'USA'";

2. 參數化查詢：三種寫法

參數在 ESQL 外定義，查詢中以 @ 前綴，建構 ObjectParameter 加入查詢。

寫法一：加入 Parameters 集合

var sql = "SELECT VALUE emp FROM NorthwindEntities.Employees AS emp WHERE emp.Country = @country";
var query = context.CreateQuery<Employee>(sql);
query.Parameters.Add(new ObjectParameter("country", "USA"));

寫法二：ObjectQuery 建構子傳參數

ObjectQuery<Employee> query = new ObjectQuery<Employee>(sql, context);
query.Parameters.Add(new ObjectParameter("country", "USA"));

寫法三：CreateQuery 多載直接傳參數

var query = context.CreateQuery<Employee>(sql, new ObjectParameter("country", "USA"));

寫法四：Where 擴充方法 + it 關鍵字

var query = context.CreateQuery<Employee>("SELECT VALUE emp FROM NorthwindEntities.Employees AS emp")
    .Where("it.Country = @country", new ObjectParameter("country", "USA"));

it 代表當前查詢結果集的每個元素。

3. 查詢基本型別 (Primitive Type)

SELECT 僅回傳單一值，CreateQuery<T> 指定基本型別 (int、string 等)。

// 回傳 EmployeeID 集合
var sql = "SELECT VALUE emp.EmployeeID FROM NorthwindEntities.Employees AS emp WHERE emp.Country = @country";
var query = context.CreateQuery<int>(sql, new ObjectParameter("Country", "USA"));
foreach (var id in query) Console.WriteLine(id);

// 回傳單一字串
var sql2 = "SELECT VALUE emp.Country FROM NorthwindEntities.Employees AS emp WHERE emp.EmployeeID = @id";
var country = context.CreateQuery<string>(sql2, new ObjectParameter("id", 1)).First();

Query Builder 替代方案：SelectValue

string country = context.Employees.SelectValue<string>("it.Country", new ObjectParameter("id", 1)).First();

4. 查詢匿名型別

調整投影欄位，回傳 DbDataRecord (支援任意列舉型別資料繫結)。

// 依位置存取
var sql = "SELECT emp.LastName, emp.FirstName FROM NorthwindEntities.Employees AS emp";
var query = context.CreateQuery<DbDataRecord>(sql);
foreach (var emp in query) Console.WriteLine("{0} {1}", emp[0], emp[1]);

// 依別名存取
var sql2 = "SELECT emp.LastName AS FamilyName, emp.FirstName FROM NorthwindEntities.Employees AS emp";
var query2 = context.CreateQuery<DbDataRecord>(sql2);
foreach (var emp in query2) Console.WriteLine("{0} {1}", emp["FamilyName"], emp["FirstName"]);

下一篇將介紹 EntityClient、LINQ to Entities、Eager Loading、變更追蹤與並發控制。