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

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实例序列的操作。下面来看具体的代码片断：

l　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有四种值分别是：

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

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

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

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

l　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

}

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

l　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());



}

l　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);

}

l　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);

}

}

l　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

}

}

l　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);

}

}

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

l　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

}



}

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

l　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);

}

}

l　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

}

l　SelectValue

实例代码如下：

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

}

l　Skip/Top

实例代码如下：

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

}

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

原文：http://www.shangxueba.com/jingyan/104556.html