ADO.NET Entity Framework学习笔记(4)ObjectQuery对象

最新推荐文章于 2019-01-30 23:48:40 发布
最新推荐文章于 2019-01-30 23:48:40 发布
阅读量150 收藏
点赞数
分类专栏： .NET 文章标签： .net Linq Security C C++
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/woshiliulidiyi/article/details/83772341
版权

ObjectQuery<T>数据加载方式

1. ObjectQuery<T> 提供了一个管理[实体对像]集合

2. ObjectQuery<T>继承System.Data.Objects.ObjectQuery, ObjectQuery对ObjectContext进行了封装，

3.可以通过ObjectContext.CreateQuery<T>("esql")的方式创建ObjectQuery<T>

4.可以通过new ObjectQuery<T>(ObjectContext,"esql")的方式创建ObjectQuery<T>,跟据 SQL字串的不同，会得到具体的ObjectQuery<值类型>，或ObjectQuery<DbDataRecord>或 ObjectQuery<实体>

访问方式

Context.CreateQuery<T>()

string econString = @"

metadata=res://*/myModel.csdl

|res://*/myModel.ssdl

|res://*/myModel.msl

;

provider=System.Data.SqlClient;

provider connection string=""

Data Source=.;

Initial Catalog=LingTestDB;

Integrated Security=True;

MultipleActiveResultSets=True;

""

";

EntityConnection econ = new EntityConnection(econString);

ObjectContext context = new ObjectContext(econ);

context.DefaultContainerName = "myContext";

ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("DBItem");

foreach (var r in queryTab)

{

System.Console.WriteLine("{0},{1}", r.ItemID, r.ItemMatter);

}

string econString = @"

metadata=res://*/myModel.csdl

|res://*/myModel.ssdl

|res://*/myModel.msl

;

provider=System.Data.SqlClient;

provider connection string=""

Data Source=.;

Initial Catalog=LingTestDB;

Integrated Security=True;

MultipleActiveResultSets=True;

""

";

EntityConnection econ = new EntityConnection(econString);

ObjectContext context = new ObjectContext(econ);

ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("select value it from myContext.DBItem as it where it.ItemID='a'");

foreach (var r in queryTab)

{

System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter);

}

ObjectQuery<DbDataRecord>

string econString = @"

metadata=res://*/myModel.csdl

|res://*/myModel.ssdl

|res://*/myModel.msl

;

provider=System.Data.SqlClient;

provider connection string=""

Data Source=.;

Initial Catalog=LingTestDB;

Integrated Security=True;

MultipleActiveResultSets=True;

""

";

EntityConnection econ = new EntityConnection(econString);

ObjectContext context = new ObjectContext(econ);

ObjectQuery<DbDataRecord> queryTab = new ObjectQuery<DbDataRecord>("select it.ItemID,it.ItemMatter from myContext.DBItem as it", context);

foreach (var r in queryTab)

{

System.Console.WriteLine("{0},{1}",r[0].ToString(),r[1].ToString());

}

ObjectQuery<简单类型>

string econString = @"

metadata=res://*/myModel.csdl

|res://*/myModel.ssdl

|res://*/myModel.msl

;

provider=System.Data.SqlClient;

provider connection string=""

Data Source=.;

Initial Catalog=LingTestDB;

Integrated Security=True;

MultipleActiveResultSets=True;

""

";

EntityConnection econ = new EntityConnection(econString);

ObjectContext context = new ObjectContext(econ);

ObjectQuery<int> queryTab = new ObjectQuery<int>("select value Count(it.ItemID) from myContext.DBItem as it", context);

foreach (var r in queryTab)

{

System.Console.WriteLine("个数:{0}", r.ToString() );

}

 

Execute方法与ObjectResult

Execute方法

string econString = @"

metadata=res://*/myModel.csdl

|res://*/myModel.ssdl

|res://*/myModel.msl

;

provider=System.Data.SqlClient;

provider connection string=""

Data Source=.;

Initial Catalog=LingTestDB;

Integrated Security=True;

MultipleActiveResultSets=True;

""

";

EntityConnection econ = new EntityConnection(econString);

ObjectContext context = new ObjectContext(econ);

context.DefaultContainerName = "myContext";

ObjectQuery<DBItem> queryTab = context.CreateQuery<DBItem>("DBItem");

ObjectResult<DBItem> resultTab = queryTab.Execute(MergeOption.NoTracking);

foreach (var r in resultTab)

{

System.Console.WriteLine("{0},{1}",r.ItemID,r.ItemMatter);

}

ObjectResult<T> 结果集

ObjectQuery<T>.Execute()方法返回ObjectResult<T>对象

 

类型转换

OfType(TResultType)

ObjectQuery<TResultType> OfType<TResultType>();

myContext context = new myContext();

ObjectQuery<DBItemEx> v = context.DBItem.OfType<DBItemEx>();

 

Linq方法

All

判断集合中是否所有元素都满足某一条件

myContext context = new myContext();

bool b= context.DBItemList.All(p => p.ItemValue >= 0);

SELECT

CASE WHEN ( NOT EXISTS (SELECT

    cast(1 as bit) AS [C1]

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

    WHERE ( NOT ([Extent1].[ItemValue] >= 1)) OR (CASE WHEN ([Extent1].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT ([Extent1].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL)

)) THEN cast(1 as bit) WHEN ( EXISTS (SELECT

    cast(1 as bit) AS [C1]

    FROM [dbo].[DBItemList] AS [Extent2]

    WHERE ( NOT ([Extent2].[ItemValue] >= 0)) OR (CASE WHEN ([Extent2].[ItemValue] >= 0) THEN cast(1 as bit) WHEN ( NOT ([Extent2].[ItemValue] >= 0)) THEN cast(0 as bit) END IS NULL)

)) THEN cast(0 as bit) END AS [C1]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

Any

判断集合中是否有元素满足某一条件

myContext context = new myContext();

bool b = context.DBItemList.Any(p => p.ItemValue == 4);

SELECT

CASE WHEN ( EXISTS (SELECT

    cast(1 as bit) AS [C1]

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

    WHERE 4 = [Extent1].[ItemValue]

)) THEN cast(1 as bit) WHEN ( NOT EXISTS (SELECT

    cast(1 as bit) AS [C1]

    FROM [dbo].[DBItemList] AS [Extent2]

    WHERE 4 = [Extent2].[ItemValue]

)) THEN cast(0 as bit) END AS [C1]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

Take

获取集合的前n个元素

myContext context = new myContext();

IQueryable<DBItemList> list = context.DBItemList.Take(3);

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

跳过集合的前n个元素,

Linq To Entity 要求必须先OrderBy

myContext context = new myContext();

IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue).Skip(5);

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

First

集合的第一个元素,集合中没有会报错,

myContext context = new myContext();

DBItemList f1 = context.DBItemList.First();

DBItemList fi = context.DBItemList.First(p => p.ItemValue == 5);

SELECT TOP (1)

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

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

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

[Extent1].[ItemValue] AS [ItemValue]

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

WHERE 5 = [Extent1].[ItemValue]

FirstOrDefault

集合中的第一个元素,没有则返回类型默认值,对象型默认值为null

myContext context = new myContext();

DBItemList fi = context.DBItemList.FirstOrDefault(p => p.ItemValue ==5);

if (fi != null)

{

Console.WriteLine(fi.ItemValue);

}

Where

用LinqExpressions为条件进行查询

myContext context = new myContext();

IQueryable<DBItemList> list= context.DBItemList.Where(p => p.ItemValue == 5);

SELECT

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

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

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

[Extent1].[ItemValue] AS [ItemValue]

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

WHERE 5 = [Extent1].[ItemValue]

Distinct

过滤集合中的相同项

ObjectQuery<T> Distinct()

myContext context = new myContext();

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

ObjectQuery<DbDataRecord> dlist= list.Distinct();

SELECT

[Distinct1].[C1] AS [C1],

[Distinct1].[ItemValue] AS [ItemValue]

FROM ( SELECT DISTINCT

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

    1 AS [C1]

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

) AS [Distinct1]

OrderBy,OrderByDescending

排序升,排序降

myContext context = new myContext();

IQueryable<DBItemList> list = context.DBItemList.OrderBy(p=>p.ItemValue);

IQueryable<DBItemList> list = context.DBItemList.OrderByDescending(p=>p.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

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

ThenBy,ThenByDescending

ThenBy,ThenByDescending 方法必须跟在 OrderBy 方法或对 ThenBy 方法的另一次调用之后

当用OrderBy,OrderByDescending指定主排序字段后,可用ThenBy呀ThenByDescending指定次排序字段

myContext context = new myContext();

IQueryable<DBItemList> query = context.DBItemList.OrderBy(p=>p.ItemValue).ThenByDescending(p => p.ItemID);

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

Average,Sum

平均值,求和

myContext context = new myContext();

double d = context.DBItemList.Average(p => p.ItemValue);

double s = context.DBItemList.Sum(p => p.ItemValue);

SELECT

[GroupBy1].[A1] AS [C1]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

LEFT OUTER JOIN (SELECT

    AVG( CAST( [Extent1].[ItemValue] AS float)) AS [A1]

    FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1

SELECT

[GroupBy1].[A1] AS [C1]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

LEFT OUTER JOIN (SELECT

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

    FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1

Max,Min

集合最大值，最小值

myContext context = new myContext();

var mx = context.DBItemList.Max(p => p.ItemValue);

var mi = context.DBItemList.Min(p => p.ItemValue);

SELECT

[GroupBy1].[A1] AS [C1]

FROM ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

LEFT OUTER JOIN (SELECT

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

    FROM [dbo].[DBItemList] AS [Extent1] ) AS [GroupBy1] ON 1 = 1

SELECT

[GroupBy1].[A1] AS [C1]

FROM ( SELECT cast(1 as bit) AS X

woshiliulidiyi
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
EF_ObjectQuery查询及方法
12-14
EF_ObjectQuery查询及方法 基于EF
Query Object模式Demo
03-10
关于Query Object模式的设计和使用
ADO.NET EntityFramework 完整版教程（从初级到高级）
03-03
Entity Framework完整版教程，从初级到中级，再到高级，循序渐进。
ADO.NET Entity Framework4.1
07-29
ADO.NET Entity Framework 是微软以 ADO.NET 为基础所发展出来的对象关系对应 (O/R Mapping) 解决方案，该插件为4.1版本
ADO.NET Entity Framework
04-29
学习ADO.NET Entity Framework的好文档。
Microsoft ADO.NET Entity Framework Step by Step 无水印pdf
10-01
Microsoft ADO.NET Entity Framework Step by Step 英文无水印pdf pdf所有页面使用FoxitReader和PDF-XChangeViewer测试都可以打开 本资源转载自网络，如有侵权，请联系上传者或csdn删除 本资源转载自网络，如...
ADO.NET sql、LINQ to sql、ADO.NET Entity Framework(EF)数据库连接性能比较
11-05
数据ADO.NET sql、LINQ to sql、ADO.NET Entity Framework(EF)数据库连接性能比较，主要比较了插入与读取的时间，读取里可以进行模糊检索
ADO.NET Entity Framework概述
12-01
ADO.NET Entity Framework概述PPT讲解，已经学习，好东西、分享
ADO.NET Entity Framework 教程
04-10
中新增的数据访问层设计技术ADO.NET Entity Framework。ADO.NET的LINQ to Entity部分以LINQ  为基础，为了完整性本文档首先介绍LINQ技术 Entity Framework 教程 目录  预备知识 2  LINQ技术 2  LINQ...
ADO.NET EntityFramework入门教程
11-08
ADO.NET EntityFramework入门教程，分别讲解Storage Provider ，Mapping Layer ，Object Services，LINQ to Entities 四层如何设计使用
ADO.NET Entity Framework使用封装示范代码
12-07
ADO.NET Entity Framework使用封装示范代码,这个中封装可以提高开发效率
ADO.NET Entityframework 書籍
05-29
ADO.NET Entityframework dbcontext a
【转载】分页插件中关于PageInfo
beidaol的专栏
01-30 1264
转自：https://blog.csdn.net/ilikejj0/article/details/81110009 1、PageInfo的源代码 private int pageNum; private int pageSize; private int size; //其中最后这个list存放的信息就是使用第三个构造方法，所传进去的List，通常是在数据库中查询到的结果,即是查询出...
ASP.NET设计模式之Query Object模式
我不是大牛，我只是个搬运工！
03-10 4342
当我们应用Repository模式作为数据仓储时，如果我们不使用Query Object模式，那么我们的Repository将会充满大量的检索方法，如下： public interface IReadOnlyRepository where T : IAggregateRoot { T FindBy(TId id); IEnumerable FindAll(); IEn
.NET - ADO.NET Entity Framework : Querying metadata
代码之美
08-01 2918
引自： http://www.scip.be/index.php?Page=ArticlesNET24   A few months ago I posted several articles about the ADO.NET Entity Framework beta 3. In the meanwhile the Entity Framework has been officia
.net entityframework
最新发布
07-13
.NET Entity Framework（EF）是微软提供的一种对象关系映射（ORM）框架，用于简化和加速开发者与数据库之间的交互。它允许开发者使用面向对象的方式来操作数据库，而无需直接编写SQL语句。以下是一些与.NET Entity Framework相关的基本知识： 1. 什么是.NET Entity Framework？ .NET Entity Framework是一个开源的ORM框架，它允许开发者通过定义实体类和关系来操作数据库。它提供了一个对象关系映射器，可以将数据库表映射到.NET实体类，使开发者可以使用面向对象的思维来操作数据库。 2. Entity Framework有哪些核心组件？ Entity Framework包括以下核心组件： - DbContext：表示数据库上下文，用于管理实体对象与数据库之间的交互。 - DbSet：表示数据库中的实体集合，用于查询、插入、更新和删除实体。 - Entity：表示映射到数据库表的实体类。 - LINQ to Entities：用于编写查询语句，将LINQ查询转换为SQL语句并执行。 3. 如何使用Entity Framework进行数据库操作？ 使用Entity Framework进行数据库操作通常包括以下步骤： - 定义实体类：创建表示数据库表的实体类。 - 创建DbContext：继承自DbContext的类，表示数据库上下文。 - 配置映射关系：使用Fluent API或属性注解来配置实体类与数据库表之间的映射关系。 - 执行数据库操作：使用DbContext的方法（如SaveChanges、Add、Remove等）来执行对数据库的增删改查操作。 4. Entity Framework支持哪些数据库提供程序？ Entity Framework支持多种数据库提供程序，包括SQL Server、MySQL、Oracle、SQLite等。可以根据需要选择合适的数据库提供程序。 5. 如何进行查询操作？ 使用Entity Framework可以使用LINQ查询语法或方法链式调用来进行查询操作。通过DbContext的DbSet属性获取实体集合，然后可以使用LINQ查询来过滤、排序和投影数据。 6. 如何进行数据迁移？ 数据迁移是Entity Framework提供的一种机制，用于管理数据库结构的变更。可以使用命令行工具或Package Manager Console来创建和应用数据迁移脚本，以保持数据库结构与代码模型的一致性。 .NET Entity Framework是一个功能强大且广泛使用的ORM框架，它简化了与数据库的交互，提高了开发效率。建议深入学习和掌握Entity Framework的各种特性和用法，以便在实际项目中充分发挥其优势。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值