VS 2008 SP1 + .NET 3.5 SP1 (6) - Entity Framework Entity SQL 完整指南

Entity SQL (ESQL) 概述

Entity SQL 是針對概念模型 (EDM) 的查詢語言，語法類似 T-SQL 但操作實體與關聯。可透過 ObjectQuery 或 EntityCommand 執行，亦可混用於 LINQ 方法中。

查詢運算子與函數分類

查詢運算式

select, from, where, order by, group by, having, cross join, inner join, left/right/full outer join, case when then else end

集合運算子

• anyelement(expression) - 從集合取任一元素
• except, intersect, union, union all - 集合差集、交集、聯集
• flatten(collection) - 扁平化多層集合
• exists, in, overlaps, set, top(n), skip, limit

型別運算子

• cast(expression as data_type) - 型別轉換
• oftype, is of, treat - 繼承體系下的型別判斷與轉換

常用純量函數

實際程式碼範例 - EntitySQL.aspx.cs

基本查詢與參數化

using (var ctx = new NorthwindEntities()) {
    string esql = "select c.[CategoryId], c.[CategoryName] from NorthwindEntities.Categories as c";
    ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>(esql);
    query.Name = "cate";
    // 在 LINQ 方法上使用 ESQL 片段
    query = query.Where("cate.CategoryId=@CategoryId", new ObjectParameter("CategoryId", 1));
}

using (var ctx = new NorthwindEntities()) {
    string esql = "select value c.CategoryId from Categories as c where c.CategoryId=@CategoryId or c.CategoryId=@CategoryId2";
    ObjectParameter op = new ObjectParameter("CategoryId", 1), op2 = new ObjectParameter("CategoryId2", 2);
    ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>(esql, op);
    query.Parameters.Add(op2);
}

呼叫資料庫函數與 CLR 型別

// 使用 SQL Server LEN 函數
string esql = "using SqlServer; select LEN(p.ProductName) as PriceCount from Products as p";
// 或 select SqlServer.LEN(p.ProductName) ...
// 使用 CLR System.String
esql = "select value cast(c.CategoryId as System.String) from Categories as c";

LINQ 方法混用 ESQL

using (var ctx = new NorthwindEntities()) {
    var where = ctx.Categories.Where("it.CategoryId = 1");
    var orderby = ctx.Categories.OrderBy("it.CategoryId desc");
    var select = ctx.Categories.Select("it.CategoryId as ID");
    var selectvalue = ctx.Categories.SelectValue<string>("cast(it.CategoryId as System.String) + '_' + it.CategoryName");
    var top = ctx.Categories.Top("3");
    var skip = ctx.Categories.Skip("it.CategoryId desc", "3");
    var groupby = ctx.Products.GroupBy("it.Categories.CategoryId", "it.Categories.CategoryId, count(1)");
    ctx.Categories.Include("it.Products"); // 急性載入
}

查詢運算式範例

// GroupBy + Having + OrderBy
string esql = @"select it.Categories.CategoryId, Count(1) as ProductCount 
                from Products as it 
                group by it.Categories.CategoryId 
                having count(1) > 10 
                order by it.Categories.CategoryId desc";

// Inner Join
string esql2 = @"select p.ProductName, c.CategoryName 
                 from Products as p 
                 inner join Categories as c 
                 on p.Categories.CategoryId = c.CategoryId";

// Case When
string esql3 = @"select p.ProductId, 
                 (case when p.ProductId < 10 then '小於10的ID'
                       when p.ProductId < 20 then '小於20大於等於10的ID'
                       else '大於等於20的ID' end) as Comment
                 from Products as p";

集合運算子與分頁

using (var ctx = new NorthwindEntities()) {
    // flatten 扁平化
    string esql = "flatten(select value c.Products from NorthwindEntities.Categories as c)";
    // IN 運算子
    string esql2 = "select p.ProductId from Products as p where p.ProductId in {1,2,3}";
    // anyelement 取任一
    string esql3 = "anyelement(select value c from NorthwindEntities.Categories as c)";
}

// 分頁：skip 10 limit 3
string esql = @"select p.ProductId from Products as p order by p.ProductId skip 10 limit 3";

總結

Entity SQL 提供強大且靈活的查詢能力，特別適合動態查詢建構、複雜報表與跨資料庫函數呼叫。完整原始碼請見：https://www.bianceng.net/dotnet/201212/805.htm (註：原文連結可能有誤，請以實際專案為準)。