ADO.NET Entity Framework学习笔记(5)ESQL查询语句

比起 LINQ to SQL，EF 除了提供 LINQ 查询方式, 还提供了 Entity SQL language

ESQL 类似 Hibernate 的 HSQL，ESQL 与SQL 语言的语法相似，以字符串的方式执行

 

esql的查询结果集 ObjectQuery

ObjectQuery<实体>

myContext context = new myContext();

string esql = "SELECT VALUE DBItemList FROM myContext.DBItemList";

// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);

foreach (DBItemList r in query)

{

Console.WriteLine(r.NameID);

}

myContext context = new myContext();

string esql = "SELECT VALUE it FROM myContext.DBItemList as it";

// ObjectQuery<DBItemList> query = new ObjectQuery<DBItemList>(esql, context);

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);

foreach (DBItemList r in query)

{

Console.WriteLine(r.NameID);

}

ObjectQuery<DbDataRecord>

myContext context = new myContext();

string esql = "SELECT it.NameID FROM myContext.DBItemList as it";

//ObjectQuery<DbDataRecord> query = new ObjectQuery<DbDataRecord>(esql, context);

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)

{

Console.WriteLine(r["NameID"].ToString());

}

ObjectQuery<简单类型>

myContext context = new myContext();

string esql = "SELECT value count(it.NameID) FROM myContext.DBItemList as it";

// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);

ObjectQuery<int> query = context.CreateQuery<int>(esql);

foreach (int n in query)

{

Console.WriteLine(n);

}

myContext context = new myContext();

string esql = "SELECT value it.NameID FROM myContext.DBItemList as it";

// ObjectQuery<int> query = new ObjectQuery<int>(esql, context);

ObjectQuery<string> query = context.CreateQuery<string>(esql);

foreach (string n in query)

{

Console.WriteLine(n);

}

 

esql的使用

可以在

• ObjectQuery的Linq方法,
• 构造ObjectQuery,
• context.CreateQuery返方法,

中使用esql,并得到返回的榄查询结果ObjectQuery

it关键字

[it] 出现在 ESQL 中, 由 ObjectQuery<T>.Name 属性设定，用于标示源查询对象(ObjectQuery)的名称，

类似于 "SELECT * FROM Tab as it WHERE it.ItemValue =14" 。

可以将这个默认值 "it" 改成其他字符串。

myContext context = new myContext();

context.DBItemList.Name = "wxd";

ObjectQuery<DBItemList> list = context.DBItemList.Where("wxd.ItemValue=5");

myContext context = new myContext();

var sql = "SELECT VALUE DBItemList FROM myContext.DBItemList";

var query = new ObjectQuery<DBItemList>(sql, context);

query.Name = "wxd";

ObjectQuery<DBItemList> list = query.Where("wxd.ItemValue=@v", new ObjectParameter("v", 5));

value 关键字

value 后只能返回一个成员

myContext context = new myContext();

string esql = "SELECT value AVG(it.ItemValue) FROM myContext.DBItemList as it";

ObjectQuery<int> query = context.CreateQuery<int>(esql);

foreach (int n in query)

{

Console.WriteLine(n);

}

/* print:

3

*/

string esql = "select value it.ItemID from myContext.DBItemList as it";

ObjectQuery<string> query = context.CreateQuery<string>(esql);

foreach (string r in query)

{

Console.WriteLine(r);

}

myContext context = new myContext();

string esql = "select value row( it.ItemValue ,it.NameID,'wxd' as wxwinter) from myContext.DBItemList as it";

ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

foreach (DbDataRecord r in query)

{

Console.WriteLine("{0},{1},{2}", r["ItemValue"], r["NameID"], r["wxwinter"]);

}

查询参数的使用

myContext context = new myContext();

string esql = "SELECT VALUE it FROM myContext.DBItemList as it where it.ItemValue=@v1 or it.NameID=@v2";

ObjectParameter v1 = new ObjectParameter("v1", 3);

ObjectParameter v2 = new ObjectParameter("v2", "n01");

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql,v1,v2);

foreach (DBItemList r in query)

{

Console.WriteLine("{0},{1}",r.NameID,r.ItemValue);

}

中文字段

使用[]将字段括起来

myContext context = new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.值 ==22.22");

System.Console.WriteLine(query.CommandText);

foreach (typeTest r in query)

{

Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值);

}

myContext context = new myContext();

ObjectQuery<typeTest> query = context.typeTest.Where("it.[值] ==22.22");

System.Console.WriteLine(query.CommandText);

foreach (typeTest r in query)

{

Console.WriteLine("{0},{1},{2},{3},{4},{5}", r.a, r.b, r.c, r.d, r.e, r.值);

}

 

得到esql与sql字串

myContext context = new myContext();

string esql = "SELECT VALUE it FROM myContext.DBItemList as it";

ObjectQuery<DBItemList> query = context.CreateQuery<DBItemList>(esql);

Console.WriteLine(query.CommandText);

Console.WriteLine(query.ToTraceString())

SELECT VALUE it FROM myContext.DBItemList as it

SELECT

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM [dbo].[DBItemList] AS [Extent1]

CommandText属性

得到esql字串

ToTraceString方法

得到sql字串

 

ObjectQuery的Linq方法

Where

用字符串为条件进行查询

ObjectQuery<T> Where(string predicate, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DBItemList> list = context.DBItemList.Where("(it.ItemValue=5 or it .ItemValue=5) and it.NameID='n01'");

SELECT

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM [dbo].[DBItemList] AS [Extent1]

WHERE (([Extent1].[ItemValue] = 5) OR ([Extent1].[ItemValue] = 5)) AND ([Extent1].[NameID] = 'n01')

OrderBy

排序

ObjectQuery<T> OrderBy(string keys, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.OrderBy("it.ItemValue,it.ItemID desc");

foreach (var r in query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM [dbo].[DBItemList] AS [Extent1]

ORDER BY [Extent1].[ItemValue] ASC, [Extent1].[ItemID] DESC

Select

射影

ObjectQuery<DbDataRecord> Select(string projection, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DbDataRecord> list = context.DBItemList.Select(" it.ItemValue as a,it.NameID ");

SELECT

1 AS [C1],

[Extent1].[ItemValue] AS [ItemValue],

[Extent1].[NameID] AS [NameID]

FROM [dbo].[DBItemList] AS [Extent1]

SelectValue(projection)

返回只有一组字段的数组

ObjectQuery<TResultType> SelectValue<TResultType>(string projection, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<int> query = context.DBItemList.SelectValue<int>("it.ItemValue + it.AutoID");

foreach (var r in query)

{

Console.WriteLine(r);

}

SELECT

[Extent1].[ItemValue] + [Extent1].[AutoId] AS [C1]

FROM [dbo].[DBItemList] AS [Extent1]

Top(count)

集合的前n个元素

count : 前n个元素

ObjectQuery<T> Top(string count, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Top("3"); ;

foreach (var r in query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT TOP (3)

[c].[AutoId] AS [AutoId],

[c].[NameID] AS [NameID],

[c].[ItemID] AS [ItemID],

[c].[ItemValue] AS [ItemValue]

FROM [dbo].[DBItemList] AS [c]

Skip(keys,count)

跳过集合的前n个元素,

keys : 用于排序的字段

count : 要跳过的记录个数

ObjectQuery<T> Skip(string keys, string count, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5");

foreach (var r in query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number]

    FROM [dbo].[DBItemList] AS [Extent1]

) AS [Extent1]

WHERE [Extent1].[row_number] > 5

ORDER BY [Extent1].[ItemValue] ASC

分页 Skip Top

Skip与Top一起使用

myContext context = new myContext();

ObjectQuery<DBItemList> query = context.DBItemList.Skip("it.ItemValue", "5").Top("3"); ;

foreach (var r in query)

{

Console.WriteLine("{0},{1},{2},{3}", r.AutoId, r.ItemID, r.NameID, r.ItemValue);

}

SELECT TOP (3)

[Extent1].[AutoId] AS [AutoId],

[Extent1].[NameID] AS [NameID],

[Extent1].[ItemID] AS [ItemID],

[Extent1].[ItemValue] AS [ItemValue]

FROM ( SELECT [Extent1].[AutoId] AS [AutoId], [Extent1].[NameID] AS [NameID], [Extent1].[ItemID] AS [ItemID], [Extent1].[ItemValue] AS [ItemValue], row_number() OVER (ORDER BY [Extent1].[ItemValue] ASC) AS [row_number]

    FROM [dbo].[DBItemList] AS [Extent1]

) AS [Extent1]

WHERE [Extent1].[row_number] > 5

ORDER BY [Extent1].[ItemValue] ASC

GroupBy(keys,projection)

分组

keys: GROUP BY的字段

projection : Select 的内容

ObjectQuery<DbDataRecord> GroupBy(string keys, string projection, params ObjectParameter[] parameters);

myContext context = new myContext();

ObjectQuery<DbDataRecord> query = context.DBItemList.GroupBy("it.ItemID", "it.ItemID,Sum(it.ItemValue) as ValueSum");

foreach (var r in query)

{

Console.WriteLine("{0},{1}", r["ItemID"], r["ValueSum"]);

}

/*

a,23

b,8

c,23

*/

SELECT

1 AS [C1],

[GroupBy1].[K1] AS [ItemID],

[GroupBy1].[A1] AS [C2]

FROM ( SELECT

    [Extent1].[ItemID] AS [K1],

    SUM([Extent1].[ItemValue]) AS [A1]

    FROM [dbo].[DBItemList] AS [Extent1]

    GROUP BY [Extent1].[ItemID]

) AS [GroupBy1]

SELECT it.ItemID,Sum(it.ItemValue) as ValueSum

FROM ( [DBItemList] ) AS it

GROUP BY it.ItemID

Include(path)

加载关联数据,参数为实体的[导航属性]的字串,调用Include("导航属性")后,关联数据会加载,这样就不用在[实体.导航属性]上调用Load()方法

ObjectQuery<T> Include(string path);

myContext context = new myContext();

var r = context.DBItem.Include("DBItemList");

foreach (var dbitem in r)

{

foreach (var dbitemlist in dbitem.DBItemList)

{

Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);

}

}

效果与下例相同

myContext context = new myContext();

var r = context.DBItem;

foreach (var dbitem in r)

{

dbitem.DBItemList.Load();

foreach (var dbitemlist in dbitem.DBItemList)

{

Console.WriteLine("{0},{1}", dbitemlist.NameID, dbitemlist.ItemValue);

}

}

 

esql注释,成员访问,分行

注释	--
成员访问	.
分行	;

 

esql运算符

算术运算符

加	+
减	-
乘	*
除	/
模	%
负	-

比效运算符

等于	=
大于	>
大于等于	>=
空判断	IS NOT NULL IS NULL
小于	<
小天等于	<=
不等于	border-bottom: 0.5pt solid; padding-left: 7px; pa 分享到： 转摘：实现千万级数据的分页的通用存储过程 | ADO.NET Entity Framework学习笔记(4)Objec ... 2010-12-01 12:52 浏览 1098 评论(0) 分类:编程语言 查看更多 相关推荐 ADO.NET EntityFramework 完整版教程（从初级到高级） Entity Framework完整版教程，从初级到中级，再到高级，循序渐进。 ADO.NET Entity Framework 学习ADO.NET Entity Framework的好文档。 ado.net entity framework extension 微软ado.net最新技术，实体框架(entity framework)扩展 ADO.NET sql、LINQ to sql、ADO.NET Entity Framework(EF)数据库连接性能比较 数据ADO.NET sql、LINQ to sql、ADO.NET Entity Framework(EF)数据库连接性能比较，主要比较了插入与读取的时间，读取里可以进行模糊检索 ADO.NET Entity Framework4.1 ADO.NET Entity Framework 是微软以 ADO.NET 为基础所发展出来的对象关系对应 (O/R Mapping) 解决方案，该插件为4.1版本 ADO.NET Entity Framework使用封装示范代码 ADO.NET Entity Framework使用封装示范代码,这个中封装可以提高开发效率 ADO.NET Entityframework 書籍 ADO.NET Entityframework dbcontext a ADO.NET Entity Framework 增删改查 ADO.NET Entity Framework 增删改查语句句法 ADO.NET Entity Framework概述 ADO.NET Entity Framework概述PPT讲解，已经学习，好东西、分享 ado.net entity framework 电子书 最近ado.net entity framework 电子书，入门学习的最佳书籍 ADO.NET Entity Framework 入门示例向导 ADO.NET Entity Framework 入门示例向导 ADO.NET EntityFramework入门教程 ADO.NET EntityFramework入门教程，分别讲解Storage Provider ，Mapping Layer ，Object Services，LINQ to Entities 四层如何设计使用 利用ADO.net entity framework+Linq实现的学习源代码 利用ADO.net entity framework+Linq实现的学习源代码---利用ADO.net entity framework+Linq实现的学习源代码 ADO.NET Entity Framework 教程 本文档主要介绍.NET开发中两项新技术，.NET平台语言中的语言集成查询技术 ­ LINQ，与ADO.NET  中新增的数据访问层设计技术ADO.NET Entity Framework。ADO.NET的LINQ to Entity部分以LINQ  为基础，为了完整... 利用ado.net entity framework快速开发信息管理 利用ado.net entity framework快速开发信息管理---利用ado.net entity framework快速开发信息管理 Using ADO.NET Entity Framework in Domain-Driven ADO.NET Entity Framework Domain-Driven ADO.NET EntityFramework 实体完整版教程 ADO.NET EntityFramework 实体完整版教程，从初级到中级，再到高级，循序渐进。 ADO.NET Entity Framework 的分页类代码.rar //作者：黄颢鹏，Email:[e-mail],转载请注作者名 ADO.NET Entity Framework 的分页类代码.rar WPF+ADO.net Entity Framework 抽奖程序源码 WPF+ADO.net Entity Framework 抽奖程序源码 C#操作SAS 2011-01-21 18:37 1162 using  System; using  System ... TreeView控件保存恢复结点展开状态 2011-01-21 18:36 1278 using  System; using  System ... ADO.NET Entity Framework学习笔记(4)ObjectQuery对象 2010-12-01 12:50 1432 ObjectQuery<T>数据加载方式 1. ... ADO.NET Entity Framework学习笔记(3)ObjectContext对象 2010-12-01 11:48 1070 说明 ObjectContext提供了管理数据的功能 ... ADO.NET Entity Framework学习笔记(2)建模 2010-12-01 11:46 1340 模型结构   [概念模型]中要有[实体键], [实体键] ... ADO.NET Entity Framework学习笔记(1)介绍 2010-12-01 11:44 1496 LINQ to Entities 一种 ... Web Service简介 2010-12-01 11:08 768 1.1 、 Web Service 基本概念 ... 决定何时使用 DataGrid、DataList 或 Repeater 2010-11-26 17:28 928 本页内容  简介   数据 Web 控件之间的相似 ... C#中抽象类和接口的区别 2010-11-26 17:27 654 对于抽象类以及接口的� ... 学习asp.net比较完整的流程 2010-11-26 10:34 696 如果你已经有较多的面向对象开发经验，跳过以下这两步： 　　 ... Global site tag (gtag.js) - Google Analytics