[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 拉進來使用

利用 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 名稱，可以參考下圖輸入

驗證在 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 語法

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

參考資料
• DbFunctionAttribute(String, String) Constructor
• DbFunctions Class
• 如何：呼叫自訂資料庫函式
• 在 EF 中使用自定義的 SQL Function
• 在 LINQ to Entities 查詢中呼叫函式