Entity Framework(實體框架)之Entity SQL
介紹
以Northwind為示例數據庫，ADO.NET Entity Framework之詳解Entity SQL
Linq 方法上也可以使用 esql
查詢表達式
select, from, where, order by, group by, having cross join, inner join, left outer join, right outer join, full outer join case when then else end
集合運算符
anyelement(expression) - 從集合中提取任意元素
except - 從左側表達式的結果中刪除其與右側表達式結果中的相同項，並返回此結果
flatten(collection) - 將多個集合組成的集合轉換為一個集合
intersect - 返回運算符兩側查詢結果的相同項
[not] exists(expression) - 確定查詢結果是否存在
[not] in {,} - 確定某值是否在某集合中
overlaps - 確定運算符兩側查詢結果是否具有相同項
set(expression) - 移除重復項
union - 將運算符兩側查詢結果連接成一個集合（移除重復項）
union all - 將運算符兩側查詢結果連接成一個集合（包括重復項）
top(n) - 取前 n 條記錄
分頁運算符
skip n - 需要跳過的項數，結合 order by 使用
limit n - 需要選擇的項數，結合 order by 使用
類型運算符
cast(expression as data_type) - 將表達式轉換為另一種數據類型（使用 EntityCommand 執行查詢 ，返回 EDM 類型；使用 ObjectQuery 執行查詢，返回 CLR 類型）
oftype - 從查詢表達式返回指定類型的對象集合，需 EDM 中繼承關系的支持
is of - 確定表達式的類型是否為指定類型或指定類型的某個子類型，需 EDM 中繼承關系的支持
treat - 將指定基類型的對象視為指定派生類型的對象，需 EDM 中繼承關系的支持
常用運算符
算術運算符
+ -(減或負) * / %
比效運算符
>, >=, <, <=, <>, != is null, is not null between and, not between and like, not like
通配符（應用於 like 和 not like）
% - 零個或零個以上的任意字符
_ - 任意單個字符
[] - 在指定范圍 [a-f] 或集合 [abcdef] 中的任意單個字符
[^] - 不在指定范圍 [^a-f] 或集合 [^abcdef] 中的任意單個字符
邏輯運算符
and, &&
or, ||
not, !
其他字符
-- - 注釋
. - 成員訪問
; - 分行
+ - 串聯字符串
函數
函數 - 聚合函數
Avg(expression) - 非 null 的平均值
Count(expression) - 記錄總數(Int64)
BigCount(expression) - 記錄總數(Int32)
Max(expression) - 非 null 的最大值
Min(expression) - 非 null 的最小值
Sum(expression) - 非 null 的總和值
StDev(expression) - 非 null 的標准偏差值（相對於平均值的標准偏差）
函數 - 數學函數
Abs(value) - 取絕對值
Ceiling(value) - 取不小於參數的最小整數
Floor(value) - 取不大於參數的最大整數
Round(value) - 取參數的整數部分
函數 - 字符串函數
Left(string, length) - 從左側開始，取 string 的前 length 個字符
Right( tring, length) - 從右側開始，取 string 的前 length 個字符
LTrim(string) - 去掉 string 的左側的空白
RTrim(string) - 去掉 string 的右側的空白
Trim(string) - 去掉 string 的兩側的空白
ToLower(string) - 將 string 全部轉換為小寫
ToUpper(string) - 將 string 全部轉換為大寫
Concat(string1, string2) - 串聯 string1 和 string2
Replace(string1, string2, string3) - 將 string1 中的所有 string2 都替換為 string3
Reverse(string) - 取 string 的反序
Substring(string, start, length) - 從 string 的 start 位置開始取 length 個字符，索引從 1 開始
IndexOf(string1, string2) - string1 在 string2 中的位置，索引從 1 開始，若找不到則返回 0
函數 - 日期和時間函數
Year(expression) - 取時間的年的部分
Month(expression) - 取時間的月的部分
Day(expression) - 取時間的日的部分
Hour(expression) - 取時間的時的部分
Minute(expression) - 取時間的分的部分
Second(expression) - 取時間的秒的部分
Millisecond(expression) - 取時間的毫秒的部分（0 - 999）
CurrentDateTime() - 取服務器的當前時間
CurrentUtcDateTime() - 取服務器的 UTC 當前時間
CurrentDateTimeOffset() - 返回值類型為 DateTimeOffset ， 取當前時間及相對於 UTC 時間的差 值
函數 - 按 位 運算的函數
BitWiseAnd(value1, value2) - 取 value1 和 value2 的位與結果
BitWiseOr(value1, value2) - 取 value1 和 value2 的位或結果
BitWiseXor(value1, value2) - 取 value1 和 value2 的位異或結果
BitWiseNot(value) - 取 value 的位求反結果
函數 - 其它函數
NewGuid() - 返回新生成的 GUID
不常用運算符
row, multiset, createref, deref, key, ref, navigate
示例
EntitySQL.aspx.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Data.Common;
using VS2008SP1.Business;
public partial class EntityFramework_EntitySQL : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
// esql 概述
Demo();
// 在 Linq 方法上使用 esql
Demo2();
// esql 查詢表達式的 demo
Demo3();
// 集合運算符的 Demo
Demo4();
// 分頁運算符的 Demo
Demo5();
}
}
/**//// <summary>
/// esql 概述
/// </summary>
void Demo()
{
using (var ctx = new NorthwindEntities())
{
// 下面 esql 中的 NorthwindEntities 為 EntityContainer 的名稱
// [] - 遇到特殊的段名稱時（如漢字），用此括起來
string esql = "select c.[CategoryId], c.[CategoryName] from
NorthwindEntities.Categories as c";
ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>
(esql);
// it - ObjectQuery<T> 的默認名稱
// query.Where("it.CategoryId=1").Execute(MergeOption.NoTracking);
// Name - 可以修改 ObjectQuery<T> 的名稱，以後再引用該
ObjectQuery<T> 時則使用此名稱
query.Name = "cate";
// 可以在 Linq 方法上使用 esql，後跟任意個 ObjectParameter 類型的參數
query = query.Where("cate.CategoryId=@CategoryId", new ObjectParameter
("CategoryId", 1));
/**//*
exec sp_executesql N'SELECT
1 AS [C1],
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName]
FROM [dbo].[Categories] AS [Extent1]
WHERE [Extent1].[CategoryID] = @CategoryId',N'@CategoryId int',@CategoryId=1
*/
}
using (var ctx = new NorthwindEntities())
{
// value - 後面只能跟一個成員
string esql = "select value c.CategoryId from Categories as c where
c.CategoryId=@CategoryId or c.CategoryId=@CategoryId2";
ObjectParameter op = new ObjectParameter("CategoryId", 1);
ObjectParameter op2 = new ObjectParameter("CategoryId2", 2);
// 配置 esql 的參數的方法
ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>
(esql, op);
query.Parameters.Add(op2);
/**//*
exec sp_executesql N'SELECT
[Extent1].[CategoryID] AS [CategoryID]
FROM [dbo].[Categories] AS [Extent1]
WHERE ([Extent1].[CategoryID] = @CategoryId) OR ([Extent1].[CategoryID] =
@CategoryId2)',N'@CategoryId int,@CategoryId2 int',@CategoryId=1,@CategoryId2=2
*/
}
using (var ctx = new NorthwindEntities())
{
// 使用 SqlServer 命名空間，以使用 SqlServer 的 LEN 函數為例
string esql = "using SqlServer;select LEN(p.ProductName) as PriceCount from
Products as p";
// string esql = "select SqlServer.LEN(p.ProductName) as PriceCount from
Products as p";
/**//*
SELECT
1 AS [C1],
LEN([Extent1].[ProductName]) AS [C2]
FROM [dbo].[Products] AS [Extent1]
*/
// 使用 System 命名空間，以使用 .NET(CLR) 的 String 類型為例
esql = "select value cast(c.CategoryId as System.String) from Categories as c";
// esql = "using System;select value cast(c.CategoryId as String) from
Categories as c";
/**//*
SELECT
CAST( [Extent1].[CategoryID] AS nvarchar(max)) AS [C1]
FROM [dbo].[Categories] AS [Extent1]
*/
}
}
/**//// <summary>
/// 在 Linq 方法上使用 esql
/// </summary>
void Demo2()
{
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)");
// 相當於在 Categories 上 Load 進來 Products
ctx.Categories.Include("it.Products");
}
}
/**//// <summary>
/// esql 查詢表達式的 demo
/// </summary>
void Demo3()
{
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 ";
/**//*
注：其中 [C2] 會自動被映射到 ProductCount
SELECT
[Project1].[C2] AS [C1],
[Project1].[CategoryID] AS [CategoryID],
[Project1].[C1] AS [C2]
FROM ( SELECT
[GroupBy1].[A2] AS [C1],
[GroupBy1].[K1] AS [CategoryID],
1 AS [C2]
FROM ( SELECT
[Extent2].[CategoryID] AS [K1],
COUNT(1) AS [A1],
COUNT(1) AS [A2]
FROM [dbo].[Products] AS [Extent1]
LEFT OUTER JOIN [dbo].[Categories] AS [Extent2] ON [Extent1].[CategoryID] =
[Extent2].[CategoryID]
GROUP BY [Extent2].[CategoryID]
) AS [GroupBy1]
WHERE [GroupBy1].[A1] > 10
) AS [Project1]
ORDER BY [Project1].[CategoryID] DESC
*/
string esql2 =
@"select
p.ProductName, c.CategoryName
from
Products as p
inner join
Categories as c
on
p.Categories.CategoryId = c.CategoryId";
/**//*
SELECT
1 AS [C1],
[Extent1].[ProductName] AS [ProductName],
[Extent2].[CategoryName] AS [CategoryName]
FROM [dbo].[Products] AS [Extent1]
INNER JOIN [dbo].[Categories] AS [Extent2] ON EXISTS (SELECT
cast(1 as bit) AS [C1]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
INNER JOIN [dbo].[Categories] AS [Extent3] ON 1 = 1
WHERE ([Extent1].[CategoryID] = [Extent3].[CategoryID]) AND ([Extent3].
[CategoryID] = [Extent2].[CategoryID])
)
*/
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";
/**//*
SELECT
1 AS [C1],
[Extent1].[ProductID] AS [ProductID],
CASE WHEN ([Extent1].[ProductID] < 10) THEN '小於10的ID' WHEN ([Extent1].
[ProductID] < 20) THEN '小於20大於等於10的ID' ELSE '大於等於20的ID' END AS [C2]
FROM [dbo].[Products] AS [Extent1]
*/
}
/**//// <summary>
/// 集合運算符的 Demo
/// </summary>
void Demo4()
{
using (var ctx = new NorthwindEntities())
{
string esql = "flatten(select value c.Products from NorthwindEntities.Categories
as c)";
/**//*
SELECT
[Extent1].[Discontinued] AS [Discontinued],
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
[Extent1].[QuantityPerUnit] AS [QuantityPerUnit],
[Extent1].[ReorderLevel] AS [ReorderLevel],
[Extent1].[UnitPrice] AS [UnitPrice],
[Extent1].[UnitsInStock] AS [UnitsInStock],
[Extent1].[UnitsOnOrder] AS [UnitsOnOrder]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryID] IS NOT NULL
*/
string esql2 = "select p.ProductId from Products as p where p.ProductId in
{1,2,3}";
/**//*
SELECT
1 AS [C1],
[Extent1].[ProductID] AS [ProductID]
FROM [dbo].[Products] AS [Extent1]
WHERE ([Extent1].[ProductID] = 1) OR ([Extent1].[ProductID] = 2) OR ([Extent1].
[ProductID] = 3)
*/
string esql3 = "anyelement(select value c from NorthwindEntities.Categories as
c)";
/**//*
SELECT
[Element1].[CategoryID] AS [CategoryID],
[Element1].[CategoryName] AS [CategoryName],
[Element1].[Description] AS [Description],
[Element1].[Picture] AS [Picture]
FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]
LEFT OUTER JOIN (SELECT TOP (1)
[Extent1].[CategoryID] AS [CategoryID],
[Extent1].[CategoryName] AS [CategoryName],
[Extent1].[Description] AS [Description],
[Extent1].[Picture] AS [Picture]
FROM [dbo].[Categories] AS [Extent1] ) AS [Element1] ON 1 = 1
*/
}
}
/**//// <summary>
/// 分頁運算符的 Demo
/// </summary>
void Demo5()
{
string esql =
@"select p.ProductId from Products as p
order by p.ProductId skip 10 limit 3";
/**//*
SELECT TOP (3)
[Project1].[C1] AS [C1],
[Project1].[ProductID] AS [ProductID]
FROM ( SELECT [Project1].[ProductID] AS [ProductID], [Project1].[C1] AS [C1],
row_number() OVER (ORDER BY [Project1].[ProductID] ASC) AS [row_number]
FROM ( SELECT
[Extent1].[ProductID] AS [ProductID],
1 AS [C1]
FROM [dbo].[Products] AS [Extent1]
) AS [Project1]
) AS [Project1]
WHERE [Project1].[row_number] > 10
ORDER BY [Project1].[ProductID] ASC
*/
}
}
