星期六, 1月 04, 2020

[EF] 使用 Function

看見網路問題才發現到,原來 EF 可以使用 SQL Server 內的使用者自訂函數,利用 AdventureWorks2017 來筆記一下 ,兩個重點
  • 呼叫 SQL Server 的使用者自訂函數
  • 利用 EF 6.0 的 DbFunctions 來使用 SQL Server 日期函數
使用 AdventureWork2017 內現有的 Function - ufnGetSalesOrderStatusText
CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
GO
使用 [ADO.NET 實體資料模型] 把 [SalesOrderHeader] Table 和 [ufnGetSalesOrderStatusText] Function 拉進來使用

[EF] 使用 Function-1

利用 partial class 擴充 DbContext 類別
using System.Data.Entity;

namespace EFSQLFunction
{
    public partial class EFDBContext
    {                  
        // SQL Server 名稱為 ufnGetSalesOrderStatusText,在 VS 內為滿足命名規則,把前綴詞的 ufn 移除
        [DbFunction("AdventureWorks2017Model.Store" , "ufnGetSalesOrderStatusText")]
        public string GetSalesOrderStatusText(byte Status)
        {
            throw new NotSupportedException("Direct calls are not Support");
        }
    }
}
DbFunctionAttrubute 參數一為 namespace、參數二為 Function 名稱,可以參考下圖輸入

[EF] 使用 Function-3

驗證在 LINQ 內使用 Function 能轉成 TSQL 語法
using System.Data.Entity;

namespace EFSQLFunction
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EFDBContext context = new EFDBContext())
            {
                context.Database.Log = Console.Write;

                DateTime target = new DateTime(2011, 5, 31);
                context.SalesOrderHeader
                    // 透過 DbFunctions 來使用內建 Function
                    .Where(w => w.OrderDate >= target
                    && w.OrderDate <= DbFunctions.AddDays(target, 7))
                    .Select(s => new
                    {
                        s.SalesOrderNumber,
                        s.Status,
                        // 呼叫自定義的 Function
                        StatusText = context.GetSalesOrderStatusText(s.Status)
                    })
                    .ToList();
            }
        }
    }
}
觀察產生的 TSQL 語法

[EF] 使用 Function-2

無法轉換的話,會拋出下圖的 Exception

[EF] 使用 Function-4

沒有留言:

張貼留言