ADO.Net Entity Framework ObjectQuery对象

ADO.Net Entity Framework  ObjectQuery对象

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Objects;
using System.Data.Common;

namespace LinqDemo
{
    public partial class EntitySQL : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            NorthwindEntities entities = new NorthwindEntities();
            //分?页3
            //可é以?通¨过y在ú ORDER BY 子ó句?中D使1用? SKIP 和í LIMIT 子ó子ó句?执′行D物?理í分?页3。￡若?要a以?确·定¨的?方?式?执′行D物?理í分?页3，?应|使1用? SKIP 和í LIMIT。￡如?果?您ú只?是?希￡望?以?非?确·定¨的?方?式?限T制?结á果?中D的?行D数y，?则ò应|使1用? TOP。￡TOP 和í SKIP/LIMIT 是?互￥斥a的?
            //string queryString = @"Select value c from NorthwindEntities.Customers as c order by c.CustomerID skip 3 limit 10";
            //var query = entities.CreateQuery<Customers>(queryString);


            //聚?合? 
            //Enity SQL不?支§持? * ，?所ù以?esql不?支§持?count(*)，?而?是?使1用?count(0)，?例y如?:
            //string queryString = @"Select count(0) from NorthwindEntities.Customers ";
            //ObjectQuery<string> query = entities.CreateQuery<string>(queryString);

            //Top 
            //SELECT 子ó句?可é以?在ú可é选?的? ALL/DISTINCT 修T饰?符?之?后ó具?有D可é选?的? TOP 子ó子ó句?。￡TOP 子ó子ó句?指?定¨查é询ˉ结á果?中D将?只?返μ回?第ú一?组é行D。￡esql代ú码?如?下?：o
            //string queryString = @"Select top(10) c.CustomerID from NorthwindEntities.Customers as c order by c.CustomerID";
            //ObjectQuery<string> query = entities.CreateQuery<string>(queryString);

            //类à型í转a换? 
            //string queryString = @"select cast(c.region as string) from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
            //ObjectQuery<string> query = entities.CreateQuery<string>(queryString);

            //加ó参?数y的?写′法¨
            //string esql = "select value c from NorthwindEntities.Customers as c ";
            //ObjectQuery<Customers> query1 = entities.CreateQuery<Customers>(esql);       
            ////Where 条?件t
            //query1 = query1.Where("it.CustomerId=@customerid");
            ////加ó参?数y
            //query1.Parameters.Add(new ObjectParameter("customerid", "ALFKI"));
            ////显?示?查é询ˉ执′行D的?SQL语?句?
            //Label1.Text =   query1.ToTraceString();

            //获?得?第ú一?条?数y据Y
            //string esql = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
            //ObjectQuery<Customers> query = entities.CreateQuery<Customers>(esql);
            //Customers c1 = query.First();
            //Customers c2 = query.FirstOrDefault();

            //单￥独à一?个?列D
            //string esql = "select value c.City from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
            //ObjectQuery<string> query = entities.CreateQuery<string>(esql);
            ////query = query.Distinct();
            //foreach (string c in query)
            //{
            //    Label1.Text += c;
            //}




            //集ˉ合?关?系μ方?法¨

            //Except:返μ回?两?个?查é询ˉ的?差?集ˉ。￡实μ例y代ú码?如?下?：o
            //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);
            //使1用?第ú一?个?结á构1集ˉ减?去￥第ú二t个?结á果?集ˉ
            //               query1 = query1.Except(query2);
            //               foreach (Customers c in query1)
            //               {
            //                   Console.WriteLine(c.Country);
            //                   //输?出?:UK
            //               }
            //       }
            // 更ü多à并￠交?差?，?都?有D相à应|的?方?法¨  javascript:void(0)


            // OrderBy方?法¨
            //string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
            //ObjectQuery<Customers> query1 = entities.CreateQuery<Customers>(esql1);
            //query1.OrderBy("it.country asc,it.city asc");



            // ObjectQuery<DbDataRecord> 泛o型í类à实μ例y

            //string esql1 = "select value c from NorthwindEntities.Customers as c order by c.CustomerID limit 10";
            //ObjectQuery<Customers> query1 = entities.CreateQuery<Customers>(esql1);
            //ObjectQuery<DbDataRecord> records = query1.Select("it.customerid,it.country");
            //foreach (DbDataRecord c in records)
            //{
            //    Console.WriteLine("{0},{1}", c["customerid"], c[1]);
            //}
            //Label1.Text =  records.ToTraceString();
            //GridView1.DataSource = records;
            //GridView1.DataBind();


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

                //查é询ˉ泛o型í结á果?
                ObjectQuery<string> records = query1.SelectValue<string>("it.customerid");

                foreach (string c in records)
                {

                    Console.WriteLine("{0}", c);

                }

                Console.WriteLine(records.ToTraceString());

                //SQL输?出?：o

                //SELECT TOP (10) 

                //[Extent1].[CustomerID] AS [CustomerID]

                //FROM [dbo].[Customers] AS [Extent1]

                //ORDER BY [Extent1].[CustomerID] ASC

            }

            //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输?出?：o

                //SELECT TOP (10) 

                //[Extent1].[CustomerID] AS [CustomerID]

                //FROM [dbo].[Customers] AS [Extent1]

                //ORDER BY [Extent1].[CustomerID] ASC

            }




            GridView1.DataSource = query1;
            GridView1.DataBind();

        }
    }
}