ADO.NET Entity Framework 学习初级篇5--ObjectQuery查询及方法

ObjectQuery 类支持对 实体数据模型 (EDM) 执行 LINQ to Entities 和 Entity SQL 查询。ObjectQuery 还实现了一组查询生成器方法，这些方法可用于按顺序构造等效于 Entity SQL 的查询命令。下面是 ObjectQuery 的查询生成器方法以及等效的 Entity SQL 语句：

1. Distinct,Except,GroupBy,Intersect,OfType,OrderBy,Select,SelectValue,Skip,Top,Union,UnionAll,Where

复制代码

每个查询生成器方法返回 ObjectQuery 的一个新实例。使用这些方法可以构造查询，而查询的结果集基于前面 ObjectQuery 实例序列的操作。下面来看具体的代码片断：

l          Execute方法:

1. using (var edm = new NorthwindEntities())
3.         {
5.                 string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
9.                 ObjectResult<Customers> results = query.Execute(MergeOption.NoTracking);
11.                 Assert.AreEqual(results.Count(), 10);
13.                 foreach (Customers c in query)
15.                     Console.WriteLine(c.CustomerID);
17.             }

复制代码

其中需要说明的是: MergeOption这个枚举类型的参数项,MergeOption有四种值分别是：

l          AppendOnly： 只追加新实体，不修改以前获取的现有实体。这是默认行为。

l          OverwriteChanges： 将 ObjectStateEntry 中的当前值替换为存储 区中的值。这将使用服务器上的数据重写在本地所做的更改。

l          PreserveChanges： 将替换原始值，而不修改当前值。这对于在发生开放式并发异常之后强制成功保存本地值非常有用。

l          NoTracking： 将不修改 ObjectStateManager，不会获取与其他对象相关联的关系，可以改善性能 。

l          GetResultType方法：返回查询结果的类型信息.例如：

1. using (var edm = new NorthwindEntities())
3.             {
5.                 string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
9.                 Console.WriteLine(query.GetResultType().ToString());
11.                 //输出结果为：
13.                 //NorthWindModel.Customers
15.             }

复制代码

l          ToTraceString方法：获取当前执行的SQL语句。

l          Where

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql = "select value c from NorthwindEntities.Customers as c ";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql);
9.                 //使用ObjectParameter的写法             
11.                 query1 = query1.Where("it.CustomerId=@customerid");
13.                 query1.Parameters.Add(new ObjectParameter("customerid", "ALFKI"));
15.                 //也可以这样写
17.                 //ObjectQuery<Customers> query2 = edm.Customers.Where("it.CustomerID='ALFKI'");
19.                 foreach (var c in query1)
21.                     Console.WriteLine(c.CustomerID);
23.                 //显示查询执行的SQL语句
25.                 Console.WriteLine(query1.ToTraceString());
27.              
29.             }

复制代码

l          First/ FirstOrDefault

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query = edm.CreateQuery<Customers>(esql);
9.                 Customers c1 = query.First();
11.                 Customers c2 = query.FirstOrDefault();
13.                 Console.WriteLine(c1.CustomerID);
15.                 Assert.IsNotNull(c2);
17.                 Console.WriteLine(c2.CustomerID);
19.       }

复制代码

l          Distinct

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql = "select value c.City from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<string> query = edm.CreateQuery<string>(esql);
9.                 query = query.Distinct();
11.                 foreach (string c in query)
13.                 {
15.                     Console.WriteLine("City {0}", c);
17.                 }
19.     }

复制代码

l          Except:返回两个查询的差集。实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";
11.                 ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(esql2);
13.                 query1 = query1.Except(query2);
15.                 foreach (Customers c in query1)
17.                 {
19.                     Console.WriteLine(c.Country);
21.                     //输出:UK
23.                 }
25.         }

复制代码

l          Intersect：返回两个查询的交集。实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";
11.                 ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(esql2);
13.                 query1 = query1.Intersect(query2);
15.                 foreach (Customers c in query1)
17.                 {
19.                     Console.WriteLine(c.Country);
21.                 }
23.       }

复制代码

l          Union/UnionAll:返回两个查询的合集，包括重复项。其中UnionAll必须是相同类型或者是可以相互转换的。

l          Include:可通过此方法查询出与相关的实体对象。实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c WHERE c.CustomerID ='HANAR'";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 query1 = query1.Include("Orders");
11.                 foreach (Customers c in query1)
13.                 {
15.                     Console.WriteLine("{0},{1}", c.CustomerID, c.Orders.Count);
17.                     //输出：HANAR,14
19.                 }
23.     }

复制代码

l          OfType： 根据制定类筛选元素创建一个新的类型。此类型是要在实体模型中已定义过的。

l          OrderBy

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 query1.OrderBy("it.country asc,it.city asc");
11. //也可以这样写               
13. //query1.OrderBy("it.country asc");
15.                 //query1.OrderBy("it.city asc");
17.                 foreach (Customers c in query1)
19.                 {
21.                     Console.WriteLine("{0},{1}", c.Country, c.City);
23.                 }
25.       }

复制代码

l          Select

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 ObjectQuery<DbDataRecord> records = query1.Select("it.customerid,it.country");
11.                 foreach (DbDataRecord c in records)
13.                 {
15.                     Console.WriteLine("{0},{1}", c[0], c[1]);
17.                 }
19.                 Console.WriteLine(records.ToTraceString());
21.                 //SQL输出：
23.                 //SELECT TOP (10)
25.                 //1 AS [C1],
27.                 //[Extent1].[CustomerID] AS [CustomerID],
29.                 //[Extent1].[Country] AS [Country]
31.                 //FROM [dbo].[Customers] AS [Extent1]
33.                 //ORDER BY [Extent1].[CustomerID] ASC
35.       }

复制代码

l          SelectValue

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 ObjectQuery<string> records = query1.SelectValue<string>("it.customerid");
11.                 foreach (string c in records)
13.                 {
15.                     Console.WriteLine("{0}", c);
17.                 }
19.                 Console.WriteLine(records.ToTraceString());
21.                 //SQL输出：
23.                 //SELECT TOP (10)
25.                 //[Extent1].[CustomerID] AS [CustomerID]
27.                 //FROM [dbo].[Customers] AS [Extent1]
29.                 //ORDER BY [Extent1].[CustomerID] ASC
31.       }

复制代码

l          Skip/Top

实例代码如下：

1. using (var edm = new NorthwindEntities())
3.     {
5.                 string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID ";
7.                 ObjectQuery<Customers> query1 = edm.CreateQuery<Customers>(esql1);
9.                 query1 = query1.Skip("it.customerid asc", "10");
11.                 query1 = query1.Top("10");
13.                 foreach (Customers c in query1)
15.                 {
17.                     Console.WriteLine("{0}", c.CustomerID);
19.                 }
21.                 Console.WriteLine(query1.ToTraceString());
23.                 //SQL输出：
25.                 //SELECT TOP (10)
27.                 //[Extent1].[CustomerID] AS [CustomerID]
29.                 //FROM [dbo].[Customers] AS [Extent1]
31.                 //ORDER BY [Extent1].[CustomerID] ASC
33. }

复制代码

本节，简单的介绍一下与ObjectQuery查询相关的语法，我个人觉得查询写法比较多，需要在日常的编程中去发现，在这里就不一一复述了。下节，将介绍EntityClient相关的内容。（文/ ♂风车车.Net ）