程式師世界 >> 編程語言 >> .NET網頁編程 >> 關於.NET >> Linq To Sql進階系列（四）User Define Function篇

Linq To Sql進階系列（四）User Define Function篇

編輯：關於.NET

User Define Function, 用戶自定義函數，簡稱UDF. 關於sql server中的udf，請大家參考 http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/一文。本文主要闡述，在Linq To Sql 中，如何使用UDF.

1，UDF 簡介

UDF可以分為兩中類型。一種為Scalar Valued Function，簡稱為SVF，是返回值類型的UDF. 另一種為Table Valued Function 簡稱為TVF，是返回一個table的UDF. 人們通常喜歡拿UDF和Store Procedure 做比較。其實，他們各有千秋。UDF最多只能返回一個RowSet,而Store Procedure可以是多個。Store Procedure支持CUD操作，而UDF不支持。但是UDF在sql 中支持內聯查詢，這個又是Sprocs所不能及的。因此Linq To Sql 也支持UDF的內聯查詢。

2，SVF

看下面這個例子。返回某個類別產品最小的單元價格。

CREATE FUNCTION [dbo].[MinUnitPriceByCategory] (@categoryID INT ) RETURNS Money AS BEGIN 　　　 -- Declare the return variable here 　　　 DECLARE @ResultVar Money 　　　 -- Add the T-SQL statements to compute the return value here 　　　 SELECT @ResultVar = MIN(p.UnitPrice) FROM Products as p WHERE p.CategoryID = @categoryID 　　　 -- Return the result of the function 　　　 RETURN @ResultVar END

用OR Designer（請參考OR工具介紹）將其映射為Dbml。如下

　 <Function Name="dbo.MinUnitPriceByCategory" Method="MinUnitPriceByCategory" IsComposable="true"> 　　　 <Parameter Name="categoryID" Type="System.Int32" DbType="Int" /> 　　　 <Return Type="System.Decimal" /> 　 </Function>

在這裡，筆者將帶著大家開始習慣用dbml來維護數據庫的映射，而不是code.在beta2發布後，有人很快就能發現mapping code無法編譯了。因為接口改動了。好，回歸正題。

無論是UDF還是Sprocs都會被映射為Function. 而IsComposable="true"是UDF獨有的一個特性，是標志UDF身份的，Linq用它來區別Sprocs和UDF。這個字段說明，該函數是支持內聯查詢的。Name則是其在數據庫中的名稱。再來看其生成的code.

[Function(Name="dbo.MinUnitPriceByCategory", IsComposable=true)] public System.Nullable<decimal> MinUnitPriceByCategory([Parameter(DbType="Int")] System.Nullable<int> categoryID) { 　　return ((System.Nullable<decimal>)(this.ExecuteMethodCall(this, ((MethodInfo) (MethodInfo.GetCurrentMethod())), categoryID).ReturnValue)); }

Linq To Sql將Sprocs和UDF映射成DataContext類裡的方法的形式，這樣用戶就可以像調用函數那樣，調用該UDF。因為這個例子是SVF，所以，返回decimal類型的值。再來看它的應用。剛才說過，可以像函數那樣調用它。比如：

int result = db.IntSVF(variable);　 int result = db.IntSVF(constant);

再就讓我們來看幾個內聯的查詢的。所謂內聯（in-line），就是說，你可以把UDF當作一個表（TVF ），或一個變量(SVF)，寫在sql語句裡。比如：

SELECT * FROM Products AS t0 WHERE t0.UnitPrice = dbo.MinUnitPriceByCategory(t0.CategoryID)

在這個sql語句中，就調用了上面那個UDF。同樣Linq To Sql也支持這樣操作。可以寫為

var q = from p in db.Products 　　where p.UnitPrice == db.MinUnitPriceByCategory(p.CategoryID) 　　select p;

大家可以看看其生成的Sql是不是和上面的一樣。再舉一個UDF的例子

CREATE FUNCTION [dbo].[TotalProductUnitPriceByCategory] (@categoryID int) RETURNS Money AS BEGIN 　　　 -- Declare the return variable here 　　　 DECLARE @ResultVar Money 　　　 -- Add the T-SQL statements to compute the return value here 　　　 SELECT @ResultVar = (Select SUM(UnitPrice) 　　　　　　　　　　　　　　　　　　　　　　　 from Products 　　　　　　　　　　　　　　　　　　　　　　　 where CategoryID = @categoryID) 　　　 -- Return the result of the function 　　　 RETURN @ResultVar END

計算某類產品的單價總和。這次，我們在select字句裡調用它

SELECT CategoryID, [dbo].[TotalProductUnitPriceByCategory](CategoryID) AS [TotalUnitPrice] 　　　 FROM Categories

其同樣功能的Linq語句為：

var q = from c in db.Categories 　　　　select new {c.CategoryID, TotalUnitPrice = db.TotalProductUnitPriceByCategory (c.CategoryID)};

其實，對於SVF,可以放在除from等與table有關語句之外的任何地方。比如Order by, Group by等。同樣Linq全部支持。如例

var q =　　　　 from p in db.Products 　　　　　　　　　　　 where p.UnitsOnOrder >= db.SVF(p.UnitsInStock) 　　　　　　　　　　　 group p by db.SVF(p.CategoryID) into g 　　　　　　　　　　 order　 by db.SVF(g.Key) 　　　　 select db.SVF(g.Key);

當然，這個純粹是給個例子，並沒有太多實際意義。

3，系統函數的映射

目前為止，無論是OR designer還是SqlMetal均不支持對系統函數的映射。筆者也只是嘗試著，手工映射，並成功調用。我們拿Var函數舉例。Var是求方差。讓我們來模仿上面那個dbml來改寫自己的dbml 。我們將要多money類型做求var值。並且希望能夠調用sql server提供的var函數。那就需要我們將映射的名稱改成var，並且改動參數和返回值類型。其最後的dbml為：

　 <Function Name="Var" Method="Var" IsComposable="true"> 　　　 <Parameter Name="para" Type="System.Decimal" DbType="Money" /> 　　　 <Return Type="System.Decimal" /> 　 </Function>

其生成的code為：

[Function(IsComposable=true)] public System.Nullable<decimal> Var([Parameter(DbType="Money")] System.Nullable<decimal> para) { 　　return ((System.Nullable<decimal>)(this.ExecuteMethodCall(this, ((MethodInfo) (MethodInfo.GetCurrentMethod())), para).ReturnValue)); }

將該方法，放在DataContext的一個partial類中，我們並不想破壞其原來的mapping 文件，所以，單獨放在一個partial類中。而後，我們嘗試著Linq To Sql中調用該函數

var q = (from p in db.Products 　　　　　　　　select db.Var(p.UnitPrice)).ToList();

其生成的sql為

SELECT CONVERT(Decimal(29,4),Var([t0].[UnitPrice])) AS [value] FROM [dbo].[Products] AS [t0] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel

我們就這樣騙過了Linq To Sql的Run-Time。成功調用sql server提供var函數。再比如，有人習慣於用NEWID()隨機排序，達到取隨機記錄的目的。其原始sql為：

SELECT TOP 10 * FROM TABLE1 ORDER BY NEWID();

那用Linq To Sql該如何來做這個事情呢？不好意思，目前還不支持對系統函數的映射。那就手工來做吧。

因為NewId返回uniqueidentifier類型，我們將這個函數定義為

[Function(Name = "NewID", IsComposable = true)] public Guid NewID() { 　　return ((Guid)(this.ExecuteMethodCall(this, ((MethodInfo) (MethodInfo.GetCurrentMethod()))).ReturnValue)); }

調用時，可以

var q = db.Table1.OrderBy(p => db.NewID()).Take(10).ToList();

這只是一個小技巧，並不說明，所有的函數都可以這麼做。

4 TVF

返回一個table 的UDF稱為TVF.看下面例子

CREATE FUNCTION [dbo].[ProductsUnderThisUnitPrice] (@price Money ) RETURNS TABLE AS RETURN 　　　 SELECT * 　　　 FROM Products as P 　　　 Where p.UnitPrice < @price

TVF在sql中支持from，join，union等操作。同樣，這些操作在Linq To Sql中一樣支持。該TVF的 dbml為：

ElementType子項說明了其返回一個table.映射為類的名稱為ProductsUnderThisUnitPriceResult。其映射的code中，不光是一個函數，還有一個對應的返回類。當然，這個返回類的定義，可以由用戶自己指定。此處不講。我們使用默認的類。我們先來看一個from的例子

var q = from p in db.ProductsUnderThisUnitPrice(10.25M) select p;

你可以就把該udf當作一個普通的表來使用。再舉一個join操作Linq To Sql的例子

var q = from c in db.Categories join p in db.ProductsUnderThisUnitPrice(8.50M) on c.CategoryID equals p.CategoryID into prods from p in prods select new {c.CategoryID, c.CategoryName, p.ProductName, p.UnitPrice};

因為，sql中支持TVF的in-line操作，所以Linq To Sql完全支持其對等的操作。他們所生成的sql語句不再列出。

總結：

通過本文，我們可以看出Linq To Sql完全融入了Sql中UDF，包括對其內聯操作的支持。對於某些特殊需求，用戶可以手工將函數映射為code，但這並不說明，任何函數都適用。