Entity Framework(實體框架)之Entity SQL

介紹
以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();
        }
    }

/**////

/// esql 概述
///

    void Demo()
    {
        using (var ctx = new NorthwindEntities())
        {
            // 下面 esql 中的 NorthwindEntities 為 EntityContainer 的名稱
            // [] - 遇到特殊的段名稱時（如漢字），用此括起來
            string esql = "select c.[CategoryId], c.[CategoryName] from NorthwindEntities.Categories as c";

ObjectQuery query = ctx.CreateQuery(esql);

// it - ObjectQuery 的預設名稱
// query.Where("it.CategoryId=1").Execute(MergeOption.NoTracking);

            // Name - 可以修改 ObjectQuery 的名稱，以後再引用該 ObjectQuery 時則使用此名稱
            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 p = new ObjectParameter("CategoryId", 1);
ObjectParameter op2 = new ObjectParameter("CategoryId2", 2);

            // 配置 esql 的引數的方法
            ObjectQuery query = ctx.CreateQuery(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]
            */
        }
    }

/**////

/// 在 Linq 方法上使用 esql
///

    void Demo2()
    {
        using (var ctx = new NorthwindEntities())
        {
            var where = ctx.Categories.Where("it.CategoryId = 1");
            var rderby = ctx.Categories.OrderBy("it.CategoryId desc");
            var select = ctx.Categories.Select("it.CategoryId as ID");
            var selectvalue = ctx.Categories.SelectValue("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");
        }
    }

/**////

/// esql 查詢表示式的 demo
///

    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]
        */
    }

/**////

/// 集合運算子的 Demo
///

    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
            */
        }
    }

/**////

/// 分頁運算子的 Demo
///

    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
        */
    }
}

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12639172/viewspace-548883/，如需轉載，請註明出處，否則將追究法律責任。

Entity Framework(實體框架)之Entity SQL

相關文章