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相à应|的?方?法¨ http://www.cnblogs.com/xray2005/archive/2009/05/13/1455856.html // 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(); } } }
参考:
http://www.cnblogs.com/xray2005/archive/2009/05/13/1455856.html