ObjectQuery 类支持对 实体数据模型 (EDM) 执行 LINQ to Entities 和 Entity SQL 查询。ObjectQuery 还实现了一组查询生成器方法，这些方法可用于按顺序构造等效于 Entity SQL 的查询命令。下面是 ObjectQuery 的查询生成器方法以及等效的 Entity SQL 语句：
Distinct,Except,GroupBy,Intersect,OfType,OrderBy,Select,SelectValue,Skip,Top,Union,UnionAll,Where每个查询生成器方法返回 ObjectQuery 的一个新实例。使用这些方法可以构造查询，而查询的结果集基于前面 ObjectQuery 实例序列的操作。下面来看具体的代码片断：> Execute方法:
using (var edm = new NorthwindEntities()) { string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query = edm.CreateQuery (esql); ObjectResult results = query.Execute(MergeOption.NoTracking); Assert.AreEqual(results.Count(), 10); foreach (Customers c in query) Console.WriteLine(c.CustomerID); } 其中需要说明的是: MergeOption这个枚举类型的参数项,MergeOption有四种值分别是：
> AppendOnly： 只追加新实体，不修改以前获取的现有实体。这是默认行为。
> OverwriteChanges： 将 ObjectStateEntry 中的当前值替换为存储区中的值。这将使用服务器上的数据重写在本地所做的更改。
> PreserveChanges： 将替换原始值，而不修改当前值。这对于在发生开放式并发异常之后强制成功保存本地值非常有用。
> NoTracking： 将不修改 ObjectStateManager，不会获取与其他对象相关联的关系，可以改善性能。
> GetResultType方法：返回查询结果的类型信息.例如：
using (var edm = new NorthwindEntities()) { string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query = edm.CreateQuery (esql); Console.WriteLine(query.GetResultType().ToString()); //输出结果为： //NorthWindModel.Customers } > ToTraceString方法：获取当前执行的SQL语句。
> Where
实例代码如下：
using (var edm = new NorthwindEntities()){ string esql = "select value c from NorthwindEntities.Customers as c "; ObjectQuery query1 = edm.CreateQuery (esql); //使用ObjectParameter的写法 query1 = query1.Where("it.CustomerId=@customerid"); query1.Parameters.Add(new ObjectParameter("customerid", "ALFKI")); //也可以这样写 //ObjectQuery query2 = edm.Customers.Where("it.CustomerID='ALFKI'"); foreach (var c in query1) Console.WriteLine(c.CustomerID); //显示查询执行的SQL语句 Console.WriteLine(query1.ToTraceString()); } > First/ FirstOrDefault
实例代码如下：
using (var edm = new NorthwindEntities()){ string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query = edm.CreateQuery (esql); Customers c1 = query.First(); Customers c2 = query.FirstOrDefault(); Console.WriteLine(c1.CustomerID); Assert.IsNotNull(c2); Console.WriteLine(c2.CustomerID);} > Distinct
实例代码如下：
using (var edm = new NorthwindEntities()){ string esql = "select value c.City from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query = edm.CreateQuery (esql); query = query.Distinct(); foreach (string c in query) { Console.WriteLine("City {0}", c); }} > Except:返回两个查询的差集。实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query1 = edm.CreateQuery (esql1); string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10"; ObjectQuery query2 = edm.CreateQuery (esql2); query1 = query1.Except(query2); foreach (Customers c in query1) { Console.WriteLine(c.Country); //输出:UK }} > Intersect：返回两个查询的交集。实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query1 = edm.CreateQuery (esql1); string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10"; ObjectQuery query2 = edm.CreateQuery (esql2); query1 = query1.Intersect(query2); foreach (Customers c in query1) { Console.WriteLine(c.Country); }} > Union/UnionAll:返回两个查询的合集，包括重复项。其中UnionAll必须是相同类型或者是可以相互转换的
> Include:可通过此方法查询出与相关的实体对象。实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c WHERE c.CustomerID ='HANAR'"; ObjectQuery query1 = edm.CreateQuery (esql1); query1 = query1.Include("Orders"); foreach (Customers c in query1) { Console.WriteLine("{0},{1}", c.CustomerID, c.Orders.Count); //输出：HANAR,14 }} > OfType： 根据制定类筛选元素创建一个新的类型。此类型是要在实体模型中已定义过的。
> OrderBy
实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query1 = edm.CreateQuery (esql1); query1.OrderBy("it.country asc,it.city asc"); //也可以这样写 //query1.OrderBy("it.country asc"); //query1.OrderBy("it.city asc"); foreach (Customers c in query1) { Console.WriteLine("{0},{1}", c.Country, c.City); }} > Select
实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query1 = edm.CreateQuery (esql1); ObjectQuery records = query1.Select("it.customerid,it.country"); foreach (DbDataRecord c in records) { Console.WriteLine("{0},{1}", c[0], c[1]); } Console.WriteLine(records.ToTraceString()); //SQL输出： //SELECT TOP (10) //1 AS [C1], //[Extent1].[CustomerID] AS [CustomerID], //[Extent1].[Country] AS [Country] //FROM [dbo].[Customers] AS [Extent1] //ORDER BY [Extent1].[CustomerID] ASC} 实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10"; ObjectQuery query1 = edm.CreateQuery (esql1); ObjectQuery records = query1.SelectValue ("it.customerid"); foreach (string c in records) { Console.WriteLine("{0}", c); } Console.WriteLine(records.ToTraceString()); //SQL输出： //SELECT TOP (10) //[Extent1].[CustomerID] AS [CustomerID] //FROM [dbo].[Customers] AS [Extent1] //ORDER BY [Extent1].[CustomerID] ASC} 实例代码如下：
using (var edm = new NorthwindEntities()){ string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID "; ObjectQuery query1 = edm.CreateQuery (esql1); query1 = query1.Skip("it.customerid asc", "10"); query1 = query1.Top("10"); foreach (Customers c in query1) { Console.WriteLine("{0}", c.CustomerID); } Console.WriteLine(query1.ToTraceString()); //SQL输出： //SELECT TOP (10) //[Extent1].[CustomerID] AS [CustomerID] //FROM [dbo].[Customers] AS [Extent1] //ORDER BY [Extent1].[CustomerID] ASC}
posted on 2017-05-11 15:53 阅读( ...) 评论( ...)