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<Customers> query = edm.CreateQuery<Customers>(esql);

ObjectResult<Customers> 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<Customers> query = edm.CreateQuery<Customers>(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<Customers> query1 = edm.CreateQuery<Customers>(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<Customers> query = edm.CreateQuery<Customers>(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<string> query = edm.CreateQuery<string>(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<Customers> query1 = edm.CreateQuery<Customers>(esql1);

string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";

ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(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<Customers> query1 = edm.CreateQuery<Customers>(esql1);

string esql2 = "select value c from NorthwindEntities.Customers as c where c.Country='UK' order by c.CustomerID limit 10";

ObjectQuery<Customers> query2 = edm.CreateQuery<Customers>(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<Customers> query1 = edm.CreateQuery<Customers>(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<Customers> query1 = edm.CreateQuery<Customers>(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<Customers> query1 = edm.CreateQuery<Customers>(esql1);

ObjectQuery<DbDataRecord> 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<Customers> query1 = edm.CreateQuery<Customers>(esql1);

ObjectQuery<string> records = query1.SelectValue<string>("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<Customers> query1 = edm.CreateQuery<Customers>(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.cnblogs.com/xray2005/archive/2009/05/13/1455856.html

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-594967/，如需轉載，請註明出處，否則將追究法律責任。

Entity Framework 學習初級篇5--ObjectQuery查詢及方法

相關文章