`

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

    博客分类：
  • .NET
.netLinqSQLCC++ 
阅读更多

比起 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 ...
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ADO.NET EntityFramework 完整版教程（从初级到高级）

    ### ADO.NET Entity Framework 教程知识点概览 #### 一、Entity Framework 概述 - **背景**：Entity Framework (EF) 是 Microsoft 推出的一款 ORM (Object Relational Mapping) 工具，旨在简化数据访问层的开发，...

    ado.net entity framework extension

    ADO.NET Entity Framework（简称EF）是微软推出的一种对象关系映射（ORM）框架，它允许开发者使用.NET语言（如C#或VB.NET）来操作数据库，而无需编写大量的SQL语句。这个“ado.net entity framework extension”指的...

    ADO.NET Entity Framework

    ADO.NET Entity Framework 是微软开发的一种ORM（对象关系映射）框架，它允许开发者使用.NET语言（如C#或VB.NET）来操作数据库，而无需直接编写SQL语句。这个框架的核心概念是实体数据模型（Entity Data Model，EDM...

    ADO.NET sql、LINQ to sql、ADO.NET Entity Framework(EF)数据库连接性能比较

    本文将深入探讨三种常见的.NET框架下的数据库访问技术：ADO.NET SQL、LINQ to SQL以及ADO.NET Entity Framework（EF），并重点分析它们在数据库连接性能上的差异，特别是在插入和读取操作，包括模糊检索方面的表现。...

    ADO.NET EntityFramework 实体完整版教程

    ### ADO.NET Entity Framework 实体完整版教程 #### EF 基本概况 **Entity Framework (EF)** 是 Microsoft 提供的一种对象关系映射 (ORM) 框架，旨在简化面向数据的应用程序开发过程。EF 的核心优势在于它能够允许...

    ADO.NET Entity Framework 的分页类代码.rar

    总的来说，这个压缩包提供的代码可以帮助开发者在使用ADO.NET Entity Framework时，轻松实现数据库查询的分页功能，提高应用程序的性能和用户体验。通过使用这个分页类，开发者可以避免手动编写复杂的SQL分页查询，...

    ADO.NET Entity Framework使用封装示范代码

    ADO.NET Entity Framework（简称EF）是微软提供的一款强大的数据访问框架，它使得开发者能够以对象关系映射（ORM）的方式操作数据库，极大地简化了数据库编程。在这个"ADO.NET Entity Framework使用封装示范代码"中...

    ADO.NET Entity Framework4.1

    ADO.NET Entity Framework 4.1 是微软开发的一个强大的对象关系映射（ORM）框架，它构建在 ADO.NET 之上，旨在简化数据库应用程序的开发。这个框架允许开发人员使用面向对象的编程方式来操作数据库，而无需直接编写...

    ADO.NET Entity Framework 增删改查

    ADO.NET Entity Framework 增删改查 ADO.NET Entity Framework 是 .NET Framework 3.5 SP1 中引入的一种实体框架，它使开发人员可以通过对象模型（而不是逻辑/关系数据模型）专注于数据。实体框架 EntityFramework ...

    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**（简称 EF）是微软提供的一种用于 .NET 应用程序的数据访问技术，它支持开发者通过面向对象的方式来...

    ado.net entity framework 电子书

    最近ado.net entity framework 电子书，入门学习的最佳书籍

    ADO.NET Entityframework 書籍

    ### ADO.NET与Entity Framework：深入理解DbContext #### 标题理解：“ADO.NET Entityframework 書籍” 本书籍主要介绍了ADO.NET与Entity Framework的核心概念、技术细节及其在现代软件开发中的应用。其中特别强调...

    Ado.Net Entity Framework+WCF的一个Demo

    Ado.Net Entity Framework是微软推出的ORM（对象关系映射）框架，它允许开发人员使用面向对象的编程方式来操作数据库，而无需关心底层的SQL语句。Entity Framework通过Entity Data Model（EDM）将数据库模式转换为...

    ADO.NET EntityFramework入门教程

    ADO.NET EntityFramework入门教程，分别讲解Storage Provider ，Mapping Layer ，Object Services，LINQ to Entities 四层如何设计使用

    Using ADO.NET Entity Framework in Domain-Driven

    ADO.NET Entity Framework Domain-Driven

    ADO.NET Entity Framework 入门示例向导

    ADO.NET Entity Framework 是微软开发的一款强大的ORM（对象关系映射）框架，用于简化数据库操作，使得开发者无需直接编写SQL语句即可与数据库进行交互。它将数据库中的表、视图等对象映射为C#或VB.NET中的实体类，...

    ado.net entity framework实例

    ADO.NET Entity Framework是一个强大的数据访问框架，它允许开发者以对象关系映射（ORM）的方式处理数据库，从而减少了对SQL语句的直接依赖。这个实例涵盖了Entity Framework与SQL Server的集成，帮助我们理解如何...

    ado.net entity framework 进阶书

    通过以上内容的学习，开发者不仅能够掌握如何使用 ADO.NET Entity Framework 4.0 来构建高效的应用程序，还能了解到如何充分利用其提供的新特性和增强功能，从而提高开发效率并创建出更高质量的应用。

Magicbox
Global site tag (gtag.js) - Google Analytics