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<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           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相关的内容。

posted @ 2009-05-13 14:09  ♂风车车  阅读(23417)  评论(14编辑  收藏  举报