再接再厉VS 2008 sp1 + .NET 3.5 sp1(6) - Entity Framework(实体框架)之Entity SQL
作者：webabcd
介绍
以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 */ }
}
OK
[源码下载]
