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<Customers> 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 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 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);
- }
- }
实例代码如下:
- 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
- }
实例代码如下:
- 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
- }
实例代码如下:
- 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
- }