再接再厉VS 2008 sp1 + .NET 3.5 sp1(6) - Entity Framework(实体框架)之Entity SQL

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.Objects.DataClasses; 
using System.Data.Common; 

using VS2008SP1.Business; 

public partial  class EntityFramework_EntitySQL : System.Web.UI.Page 
{ 
void Page_Load() void Page_Load(object sender, EventArgs e) 
        { 
                 if (!Page.IsPostBack) 
                { 
                        // esql 概述 
                        Demo(); 

                        // 在 Linq 方法上使用 esql 
                        Demo2(); 

                        // esql 查询表达式的 demo 
                        Demo3(); 

                        // 集合运算符的 Demo 
                        Demo4(); 

                        // 分页运算符的 Demo 
                        Demo5(); 
                } 
        } 

        /**//// <summary> 
        /// esql 概述 
        /// </summary> 
        void Demo() 
        { 
                using (var ctx =  new NorthwindEntities()) 
                { 
                        // 下面 esql 中的 NorthwindEntities 为 EntityContainer 的名称 
                        // [] - 遇到特殊的段名称时（如汉字），用此括起来 
                         string esql =  "select c.[CategoryId], c.[CategoryName] from NorthwindEntities.Categories as c"; 

                        ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>(esql); 

                        // it - ObjectQuery<T> 的默认名称 
                        // query.Where( "it.CategoryId=1"). Execute(MergeOption.NoTracking); 

                        // Name - 可以修改 ObjectQuery<T> 的名称，以后再引用该 ObjectQuery<T> 时则使用此名称 
                        query.Name =  "cate"; 
                        // 可以在 Linq 方法上使用 esql，后跟任意个 ObjectParameter 类型的参数 
                        query = query.Where( "cate.CategoryId=@CategoryId",  new ObjectParameter( "CategoryId", 1)); 

                        /**//* 
                        exec sp_executesql N 'SELECT    
                        1  AS [C1],    
                        [Extent1].[CategoryID]  AS [CategoryID],    
                        [Extent1].[CategoryName]  AS [CategoryName] 
                        FROM [dbo].[Categories]  AS [Extent1] 
                        WHERE [Extent1].[CategoryID] = @CategoryId ',N'@CategoryId int',@CategoryId=1    
                        */ 
                } 

                using (var ctx =  new NorthwindEntities()) 
                { 
                        // value - 后面只能跟一个成员 
                         string esql =  "select value c.CategoryId from Categories as c where c.CategoryId=@CategoryId or c.CategoryId=@CategoryId2"; 

                        ObjectParameter op =  new ObjectParameter( "CategoryId", 1); 
                        ObjectParameter op2 =  new ObjectParameter( "CategoryId2", 2); 

                        // 配置 esql 的参数的方法 
                        ObjectQuery<DbDataRecord> query = ctx.CreateQuery<DbDataRecord>(esql, op); 
                        query.Parameters.Add(op2); 

                        /**//* 
                        exec sp_executesql N 'SELECT    
                        [Extent1].[CategoryID]  AS [CategoryID] 
                        FROM [dbo].[Categories]  AS [Extent1] 
                        WHERE ([Extent1].[CategoryID] = @CategoryId)  OR ([Extent1].[CategoryID] = @CategoryId2) ',N'@CategoryId int,@CategoryId2 int',@CategoryId=1,@CategoryId2=2    
                        */ 
                } 

                using (var ctx =  new NorthwindEntities()) 
                { 
                        // 使用 SqlServer 命名空间，以使用 SqlServer 的  LEN 函数为例 
                         string esql =  "using SqlServer;select LEN(p.ProductName) as PriceCount from Products as p"; 
                        //  string esql =  "select SqlServer.LEN(p.ProductName) as PriceCount from Products as p"; 

                        /**//* 
                         SELECT    
                        1  AS [C1],    
                         LEN([Extent1].[ProductName])  AS [C2] 
                        FROM [dbo].[Products]  AS [Extent1]    
                        */ 


                        // 使用 System 命名空间，以使用 .NET(CLR) 的  String 类型为例 
                        esql =  "select value cast(c.CategoryId as System.String) from Categories as c"; 
                        // esql =  "using System;select value cast(c.CategoryId as String) from Categories as c"; 

                        /**//* 
                         SELECT    
                        CAST( [Extent1].[CategoryID]  AS nvarchar(max))  AS [C1] 
                        FROM [dbo].[Categories]  AS [Extent1] 
                        */ 
                } 
        } 

        /**//// <summary> 
        /// 在 Linq 方法上使用 esql 
        /// </summary> 
        void Demo2() 
        { 
                using (var ctx =  new NorthwindEntities()) 
                { 
                        var where = ctx.Categories.Where( "it.CategoryId = 1"); 
                        var orderby = ctx.Categories.OrderBy( "it.CategoryId desc"); 
                        var  select = ctx.Categories. Select( "it.CategoryId as ID"); 
                        var selectvalue = ctx.Categories.SelectValue< string>( "cast(it.CategoryId as System.String) + '_' + it.CategoryName"); 
                        var top = ctx.Categories.Top( "3"); 
                        var skip = ctx.Categories.Skip( "it.CategoryId desc",  "3"); 
                        var groupby = ctx.Products.GroupBy( "it.Categories.CategoryId",  "it.Categories.CategoryId, count(1)"); 

                        // 相当于在 Categories 上 Load 进来 Products 
                        ctx.Categories.Include( "it.Products"); 
                } 
        } 

        /**//// <summary> 
        /// esql 查询表达式的 demo 
        /// </summary> 
        void Demo3() 
        { 
                 string esql = 
                     @" select    
                                it.Categories.CategoryId, Count(1)  as ProductCount 
                            from    
                                Products  as it    
                            group by    
                                it.Categories.CategoryId    
                            having    
                                count(1) > 10 
                            order by    
                                it.Categories.CategoryId desc "; 
                /**//*    
                注：其中 [C2] 会自动被映射到 ProductCount 
                 SELECT    
                [Project1].[C2]  AS [C1],    
                [Project1].[CategoryID]  AS [CategoryID],    
                [Project1].[C1]  AS [C2] 
                FROM (  SELECT    
                        [GroupBy1].[A2]  AS [C1],    
                        [GroupBy1].[K1]  AS [CategoryID],    
                        1  AS [C2] 
                        FROM (  SELECT    
                                [Extent2].[CategoryID]  AS [K1],    
                                COUNT(1)  AS [A1],    
                                COUNT(1)  AS [A2] 
                                FROM    [dbo].[Products]  AS [Extent1] 
                                 LEFT OUTER JOIN [dbo].[Categories]  AS [Extent2]  ON [Extent1].[CategoryID] = [Extent2].[CategoryID] 
                                GROUP BY [Extent2].[CategoryID] 
                        )     AS [GroupBy1] 
                        WHERE [GroupBy1].[A1] > 10 
                )     AS [Project1] 
                ORDER BY [Project1].[CategoryID] DESC    
                */ 


                 string esql2 = 
                        @" select    
                                p.ProductName, c.CategoryName 
                            from    
                                Products  as p 
                            inner join 
                                Categories  as c 
                             on    
                                p.Categories.CategoryId = c.CategoryId"; 
                /**//* 
                 SELECT    
                1  AS [C1],    
                [Extent1].[ProductName]  AS [ProductName],    
                [Extent2].[CategoryName]  AS [CategoryName] 
                FROM    [dbo].[Products]  AS [Extent1] 
                INNER JOIN [dbo].[Categories]  AS [Extent2]  ON    EXISTS ( SELECT    
                        cast(1  as bit)  AS [C1] 
                        FROM     (  SELECT cast(1  as bit)  AS X )  AS [SingleRowTable1] 
                        INNER JOIN [dbo].[Categories]  AS [Extent3]  ON 1 = 1 
                        WHERE ([Extent1].[CategoryID] = [Extent3].[CategoryID])  AND ([Extent3].[CategoryID] = [Extent2].[CategoryID]) 
                )    
                */ 


                 string esql3 = 
                        @" select    
                                p.ProductId,    
                                ( 
                                     case    
                                        when p.ProductId < 10  then  '小于10的ID' 
                                        when p.ProductId < 20  then  '小于20大于等于10的ID' 
                                         else  '大于等于20的ID' 
                                     end 
                                )  as Comment 
                            from Products  as p"; 
                /**//* 
                 SELECT    
                1  AS [C1],    
                [Extent1].[ProductID]  AS [ProductID],    
                 CASE WHEN ([Extent1].[ProductID] < 10)  THEN  '小于10的ID' WHEN ([Extent1].[ProductID] < 20) THEN '小于20大于等于10的ID' ELSE '大于等于20的ID' END AS [C2] 
                FROM [dbo].[Products]  AS [Extent1]    
                */ 
        } 

        /**//// <summary> 
        /// 集合运算符的 Demo 
        /// </summary> 
        void Demo4() 
        { 
                using (var ctx =  new NorthwindEntities()) 
                { 
                         string esql =  "flatten(select value c.Products from NorthwindEntities.Categories as c)"; 
                        /**//* 
                         SELECT    
                        [Extent1].[Discontinued]  AS [Discontinued],    
                        [Extent1].[ProductID]  AS [ProductID],    
                        [Extent1].[ProductName]  AS [ProductName],    
                        [Extent1].[QuantityPerUnit]  AS [QuantityPerUnit],    
                        [Extent1].[ReorderLevel]  AS [ReorderLevel],    
                        [Extent1].[UnitPrice]  AS [UnitPrice],    
                        [Extent1].[UnitsInStock]  AS [UnitsInStock],    
                        [Extent1].[UnitsOnOrder]  AS [UnitsOnOrder] 
                        FROM [dbo].[Products]  AS [Extent1] 
                        WHERE [Extent1].[CategoryID]  IS  NOT  NULL 
                        */ 


                         string esql2 =  "select p.ProductId from Products as p where p.ProductId in {1,2,3}"; 
                        /**//* 
                         SELECT    
                        1  AS [C1],    
                        [Extent1].[ProductID]  AS [ProductID] 
                        FROM [dbo].[Products]  AS [Extent1] 
                        WHERE ([Extent1].[ProductID] = 1)  OR ([Extent1].[ProductID] = 2)  OR ([Extent1].[ProductID] = 3)    
                        */ 


                         string esql3 =  "anyelement(select value c from NorthwindEntities.Categories as c)"; 
                        /**//* 
                         SELECT    
                        [Element1].[CategoryID]  AS [CategoryID],    
                        [Element1].[CategoryName]  AS [CategoryName],    
                        [Element1].[Description]  AS [Description],    
                        [Element1].[Picture]  AS [Picture] 
                        FROM     (  SELECT cast(1  as bit)  AS X )  AS [SingleRowTable1] 
                         LEFT OUTER JOIN    ( SELECT TOP (1)    
                                [Extent1].[CategoryID]  AS [CategoryID],    
                                [Extent1].[CategoryName]  AS [CategoryName],    
                                [Extent1].[Description]  AS [Description],    
                                [Extent1].[Picture]  AS [Picture] 
                                FROM [dbo].[Categories]  AS [Extent1] )  AS [Element1]  ON 1 = 1 
                        */ 
                } 
        } 

        /**//// <summary> 
        /// 分页运算符的 Demo 
        /// </summary> 
        void Demo5() 
        { 
                 string esql = 
                        @" select p.ProductId from Products  as p    
                            order by p.ProductId skip 10 limit 3"; 
                /**//* 
                 SELECT TOP (3)    
                [Project1].[C1]  AS [C1],    
                [Project1].[ProductID]  AS [ProductID] 
                FROM (  SELECT [Project1].[ProductID]  AS [ProductID], [Project1].[C1]  AS [C1], row_number() OVER (ORDER BY [Project1].[ProductID] ASC)  AS [row_number] 
                        FROM (  SELECT    
                                [Extent1].[ProductID]  AS [ProductID],    
                                1  AS [C1] 
                                FROM [dbo].[Products]  AS [Extent1] 
                        )     AS [Project1] 
                )     AS [Project1] 
                WHERE [Project1].[row_number] > 10 
                ORDER BY [Project1].[ProductID] ASC 
                */ 
        } 
}