posts - 615, comments - 10488, trackbacks - 594, articles - 0
  博客园 :: 首页 :: 新随笔 :: 联系 :: 订阅 订阅 :: 管理

公告

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

Posted on 2009-02-12 08:17 webabcd 阅读(...) 评论(...) 编辑 收藏
[索引页]
[源码下载]


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


作者:webabcd


介绍
以Northwind为示例数据库,ADO.NET Entity Framework之详解Entity SQL
  • Linq 方法上也可以使用 esql
  • 查询表达式
    • select, from, where, order by, group by, having
    • cross join, inner join, left outer join, right outer join, full outer join
    • case when then else end
  • 集合运算符
    • anyelement(expression) - 从集合中提取任意元素
    • except - 从左侧表达式的结果中删除其与右侧表达式结果中的相同项,并返回此结果
    • flatten(collection) - 将多个集合组成的集合转换为一个集合
    • intersect - 返回运算符两侧查询结果的相同项
    • [not] exists(expression) - 确定查询结果是否存在
    • [not] in {,} - 确定某值是否在某集合中
    • overlaps - 确定运算符两侧查询结果是否具有相同项
    • set(expression) - 移除重复项
    • union - 将运算符两侧查询结果连接成一个集合(移除重复项)
    • union all - 将运算符两侧查询结果连接成一个集合(包括重复项)
    • top(n) - 取前 n 条记录
  • 分页运算符
    • skip n - 需要跳过的项数,结合 order by 使用
    • limit n - 需要选择的项数,结合 order by 使用
  • 类型运算符
    • cast(expression as data_type) - 将表达式转换为另一种数据类型(使用 EntityCommand 执行查询,返回 EDM 类型;使用 ObjectQuery 执行查询,返回 CLR 类型)
    • oftype - 从查询表达式返回指定类型的对象集合,需 EDM 中继承关系的支持
    • is of - 确定表达式的类型是否为指定类型或指定类型的某个子类型,需 EDM 中继承关系的支持
    • treat - 将指定基类型的对象视为指定派生类型的对象,需 EDM 中继承关系的支持
  • 常用运算符
    • 算术运算符
      • +
      • -(减或负)
      • *
      • /
      • %
    • 比效运算符
      • >, >=, <, <=, <>, !=
      • is null, is not null
      • between and, not between and
      • like, not like
    • 通配符(应用于 like 和 not like)
      • % - 零个或零个以上的任意字符
      • _ - 任意单个字符
      • [] - 在指定范围 [a-f] 或集合 [abcdef] 中的任意单个字符
      • [^] - 不在指定范围 [^a-f] 或集合 [^abcdef] 中的任意单个字符
    • 逻辑运算符
      • and, &&
      • or, ||
      • not, !
    • 其他字符
      • -- - 注释
      • . - 成员访问
      • ; - 分行
      • + - 串联字符串
  • 函数
    • 函数 - 聚合函数
      • Avg(expression) - 非 null 的平均值
      • Count(expression) - 记录总数(Int64)
      • BigCount(expression) - 记录总数(Int32)
      • Max(expression) - 非 null 的最大值
      • Min(expression) - 非 null 的最小值
      • Sum(expression) - 非 null 的总和值
      • StDev(expression) - 非 null 的标准偏差值(相对于平均值的标准偏差)
    • 函数 - 数学函数
      • Abs(value) - 取绝对值
      • Ceiling(value) - 取不小于参数的最小整数
      • Floor(value) - 取不大于参数的最大整数
      • Round(value) - 取参数的整数部分
    • 函数 - 字符串函数
      • Left(string, length) - 从左侧开始,取 string 的前 length 个字符
      • Right( tring, length) - 从右侧开始,取 string 的前 length 个字符
      • LTrim(string) - 去掉 string 的左侧的空白
      • RTrim(string) - 去掉 string 的右侧的空白
      • Trim(string) - 去掉 string 的两侧的空白
      • ToLower(string) - 将 string 全部转换为小写
      • ToUpper(string) - 将 string 全部转换为大写
      • Concat(string1, string2) - 串联 string1 和 string2
      • Replace(string1, string2, string3) - 将 string1 中的所有 string2 都替换为 string3
      • Reverse(string) - 取 string 的反序
      • Substring(string, start, length) - 从 string 的 start 位置开始取 length 个字符,索引从 1 开始
      • IndexOf(string1, string2) - string1 在 string2 中的位置,索引从 1 开始,若找不到则返回 0
    • 函数 - 日期和时间函数
      • Year(expression) - 取时间的年的部分
      • Month(expression) - 取时间的月的部分
      • Day(expression) - 取时间的日的部分
      • Hour(expression) - 取时间的时的部分
      • Minute(expression) - 取时间的分的部分
      • Second(expression) - 取时间的秒的部分
      • Millisecond(expression) - 取时间的毫秒的部分(0 - 999)
      • CurrentDateTime() - 取服务器的当前时间
      • CurrentUtcDateTime() - 取服务器的 UTC 当前时间
      • CurrentDateTimeOffset() - 返回值类型为 DateTimeOffset , 取当前时间及相对于 UTC 时间的差值
    • 函数 - 按 位 运算的函数
      • BitWiseAnd(value1, value2) - 取 value1 和 value2 的位与结果
      • BitWiseOr(value1, value2) - 取 value1 和 value2 的位或结果
      • BitWiseXor(value1, value2) - 取 value1 和 value2 的位异或结果
      • BitWiseNot(value) - 取 value 的位求反结果
    • 函数 - 其它函数
      • NewGuid() - 返回新生成的 GUID
  • 不常用运算符
    • row, multiset, createref, deref, key, ref, navigate


示例
EntitySQL.aspx.cs
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
{
    
protected 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
        
*/

    }

}



OK
[源码下载]