再接再厉VS 2008 sp1 + .NET 3.5 sp1(6) - 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

{

void Page_Load() 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

// 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);

[Extent1].[CategoryID] AS [CategoryID]

WHERE ([Extent1].[CategoryID] = @CategoryId) OR ([Extent1].[CategoryID] = @CategoryId2)',N'@CategoryId int,@CategoryId2 int',@CategoryId=1,@CategoryId2=2

// 使用 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

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";

CAST( [Extent1].[CategoryID] AS nvarchar(max)) AS [C1]

/// 在 Linq 方法上使用 esql

void Demo2()

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");

/// esql 查询表达式的 demo

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

Products as p

inner join

Categories as c

p.Categories.CategoryId = c.CategoryId";

/**//*

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 =

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";

[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]

/// 集合运算符的 Demo

void Demo4()

string esql = "flatten(select value c.Products from NorthwindEntities.Categories as c)";

[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}";

[Extent1].[ProductID] AS [ProductID]

WHERE ([Extent1].[ProductID] = 1) OR ([Extent1].[ProductID] = 2) OR ([Extent1].[ProductID] = 3)

string esql3 = "anyelement(select value c from NorthwindEntities.Categories as c)";

[Element1].[CategoryID] AS [CategoryID],

[Element1].[CategoryName] AS [CategoryName],

[Element1].[Description] AS [Description],

[Element1].[Picture] AS [Picture]

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

/// 分页运算符的 Demo

void Demo5()

@"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]

[Extent1].[ProductID] AS [ProductID],

1 AS [C1]

FROM [dbo].[Products] AS [Extent1]

) AS [Project1]

WHERE [Project1].[row_number] > 10

ORDER BY [Project1].[ProductID] ASC

}

本文转自webabcd 51CTO博客，原文链接：http://blog.51cto.com/webabcd/341557，如需转载请自行联系原作者

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

继续阅读

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

C经典书籍笔记——C陷阱与缺陷②(语法陷阱之优先级)一、错误案列二、优先级规律

NOSQL安全攻击

mybatis_入门程序Mybatis入门

线性表之顺序表的实现

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

C++判断素数、求最大公约数代码判断一个数是否为素数求两个数的最大公约数

SequoiaDB巨杉数据库C++驱动概述