如何於LINQ to Entities中使用SQL Server的使用者自訂函數
最近有朋友在BLOG上問到如果要對資料庫中某個日期欄位依照其星期幾來統計出現筆數，用Linq to Entity該怎麼實作?筆者以北風資料庫的Orders資料表當作範例，嘗試解決網友所提出的問題。解決過程如下：
- 下列程式碼示範嘗試利用group子句及into搭配.NET的自訂函數來計算OrderDate對應的星期名稱之數量：
1: static void Main(string[] args)
2: {
3: using (NorthwindEntities context = new NorthwindEntities())
4: {
5: var query = from p in context.Orders
6: group p by GetWeekDay(p.OrderDate.Value) into q
7: select q;
8: foreach (var item in query)
9: {
10: Console.WriteLine(string.Format("WeekDay={0},Count={1}",item.Key,item.Count()));
11:
12: }
13: }
14: Console.ReadKey();
15: }
16:
17: private static string GetWeekDay(DateTime dt)
18: {
19: return dt.DayOfWeek.ToString();
20: }
很不幸的事情並非如此簡單，這樣做會遇到下列的錯誤訊息：
LINQ to Entities 無法辨識方法 'System.String GetWeekDay(System.DateTime)' 方法，而且這個方法無法轉譯成存放區運算式。
從字面上看來不能在LINQ to Entities中使用.Net函式來取的日期所代表的星期名稱，為此筆者將.Net函式改寫成下列的SQL Server User Defined Function（以下簡稱Function）：- 下列程式碼範例為利用DatePart函式取得日期對應的星期名稱：
1: USE [Northwind]
2: GO
3: if exists (select *
4: from sys.objects
5: where type = 'FN'
6: and name = 'ufn_GetWeekDay')
7: drop function [dbo].[ufn_GetWeekDay]
8:
9: go
10:
11: create function [dbo].[ufn_GetWeekDay](@day datetime)
12: returns int
13: begin
14: return DatePart(dw,@day) -1
15: end
16: go
於edmx檔中選擇從資料庫更新模型加入上一步驟建立的Function，特別注意目前ADO.NET Entity Data Model中尚未直接支援SQL Server中的Function，因此ufn_GetWeekDay被更新精靈歸納至預存程序的區塊（如下圖左），加入後從模型瀏覽器的Store區塊可到到ufn_GetWeekDay一樣被歸納到預存程序。
接著將上述的.Net 函式改成下列的樣子：
- 下列程式碼示範利用EdmFunctionAttribute來呼叫SQL Server Function，在此的.Net函式內容可以忽略，因為實際執行是以SQL Server Function為主，因此隨意地回傳-1讓程式碼可以通過編譯即可：
1: [EdmFunction("NorthwindModel.Store", "ufn_GetWeekDay")]
2: private static int GetWeekDay(DateTime dt)
3: {
4: return -1;
5: }
其中EdmFunctionAttribute第一個參數為Function在edmx中所對應的命名空間，也就是edmx中所指的NameSpace：
1: <Schema Namespace="NorthwindModel.Store" Alias="Self" Provider="System.Data.SqlClient"2: ProviderManifestToken="2008"3: xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"4: xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">5: </Schema>
第二個參數為SQL Server Function的名稱，且回傳值得資料型態也必須相同，因此將GetWeekDay函式的回傳資料型態由string改為int。
1: <Function Name="ufn_GetWeekDay" ReturnType="int" Aggregate="false" BuiltIn="false" NiladicFunction="false"2: IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">3: <Parameter Name="day" Type="datetime" Mode="In" />4: </Function>
最後重新執行程式，即可看到下列的結果：
透過SQL Server Profiler查看LINQ to Entities將上述LINQ查詢轉成下列TSQL敘述：
1: SELECT2: [Project2].[C2] AS [C1],3: [Project2].[C1] AS [C2],4: [Project2].[C3] AS [C3],5: [Project2].[OrderID] AS [OrderID],6: [Project2].[CustomerID] AS [CustomerID],7: [Project2].[EmployeeID] AS [EmployeeID],8: [Project2].[OrderDate] AS [OrderDate],9: [Project2].[RequiredDate] AS [RequiredDate],10: [Project2].[ShippedDate] AS [ShippedDate],11: [Project2].[ShipVia] AS [ShipVia],12: [Project2].[Freight] AS [Freight],13: [Project2].[ShipName] AS [ShipName],14: [Project2].[ShipAddress] AS [ShipAddress],15: [Project2].[ShipCity] AS [ShipCity],16: [Project2].[ShipRegion] AS [ShipRegion],17: [Project2].[ShipPostalCode] AS [ShipPostalCode],18: [Project2].[ShipCountry] AS [ShipCountry]19: FROM ( SELECT20: [Distinct1].[C1] AS [C1],21: 1 AS [C2],22: [Extent2].[OrderID] AS [OrderID],23: [Extent2].[CustomerID] AS [CustomerID],24: [Extent2].[EmployeeID] AS [EmployeeID],25: [Extent2].[OrderDate] AS [OrderDate],26: [Extent2].[RequiredDate] AS [RequiredDate],27: [Extent2].[ShippedDate] AS [ShippedDate],28: [Extent2].[ShipVia] AS [ShipVia],29: [Extent2].[Freight] AS [Freight],30: [Extent2].[ShipName] AS [ShipName],31: [Extent2].[ShipAddress] AS [ShipAddress],32: [Extent2].[ShipCity] AS [ShipCity],33: [Extent2].[ShipRegion] AS [ShipRegion],34: [Extent2].[ShipPostalCode] AS [ShipPostalCode],35: [Extent2].[ShipCountry] AS [ShipCountry],36: CASE WHEN ([Extent2].[OrderID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C3]37: FROM (SELECT DISTINCT38: [dbo].[ufn_GetWeekDay]([Extent1].[OrderDate]) AS [C1]39: FROM [dbo].[Orders] AS [Extent1] ) AS [Distinct1]40: LEFT OUTER JOIN [dbo].[Orders] AS [Extent2] ON ([Distinct1].[C1] = ([dbo].[ufn_GetWeekDay]([Extent2].[OrderDate]))) OR (([Distinct1].[C1] IS NULL) AND ([dbo].[ufn_GetWeekDay]([Extent2].[OrderDate]) IS NULL))41: ) AS [Project2]42: ORDER BY [Project2].[C1] ASC, [Project2].[C3] ASC【參考資料】
