再接再厉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

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]

WHERE [Project1].[row_number] > 10

ORDER BY [Project1].[ProductID] ASC

}

OK
[源码下载]

posted @ 2009-02-12 08:17 webabcd 阅读(5812) 评论(22) 编辑收藏举报

刷新页面返回顶部

再接再厉VS 2008 sp1 + .NET 3.5 sp1(6) - Entity Framework(实体框架)之Entity SQL

公告