LINQ to SQL語句(21)之用戶定義函數

用戶定義函數

我們可以在LINQ to SQL中使用用戶定義函數。只要把 用戶定義函數拖到O/R設計器中，LINQ to SQL自動使用FunctionAttribute屬性 和ParameterAttribute屬性（如果需要）將其函數指定為方法。這時，我們只需 簡單調用即可。

在這裡注意：使用用戶定義函數的時候必須滿足以下形 式之一，否則會出現InvalidOperationException異常情況。

具有正確映 射屬性的方法調用的函數。這裡使用FunctionAttribute屬性和 ParameterAttribute屬性。

特定於LINQ to SQL的靜態SQL方法。

.NET Framework方法支持的函數。

下面介紹幾個例子：

1.在Select中使用用戶定義的標量函數

所謂標量函數是指返回在 RETURNS 子句中定義的類型的單個數據值。可以使用所有標量數據類型，包括 bigint 和 sql_variant。不支持 timestamp 數據類型、用戶定義數據類型和非 標量類型（如 table 或 cursor）。在 BEGIN...END 塊中定義的函數主體包含 返回該值的 Transact-SQL 語句系列。返回類型可以是除 text、ntext、image 、cursor 和 timestamp 之外的任何數據類型。 我們在系統自帶的 NORTHWND.MDF數據庫中，有3個自定義函數，這裡使用 TotalProductUnitPriceByCategory，其代碼如下：

ALTER 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

我們將其拖到設計器中，LINQ to SQL通過 使用 FunctionAttribute 屬性將類中定義的客戶端方法映射到用戶定義的函數 。請注意，這個方法體會構造一個捕獲方法調用意向的表達式，並將該表達式傳 遞給 DataContext 進行轉換和執行。

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

我們使用時，可以用以下代碼來調用：

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

這時，LINQ to SQL自動生成SQL語句如下：

SELECT [t0].[CategoryID], CONVERT(Decimal(29,4),
[dbo].[TotalProductUnitPriceByCategory]([t0].[CategoryID]))
AS [TotalUnitPrice] FROM [dbo].[Categories] AS [t0]2.在Where從句中 使用用戶定義的標量函數

這個例子使用方法同上一個例子原理基本相同 了，MinUnitPriceByCategory自定義函數如下：

ALTER 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

拖到設計器中 ，生成代碼如下：

[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 查詢中，對生成的用戶定義函數方法 MinUnitPriceByCategory的內聯調用。此函數不會立即執行，這是因為查詢會延 遲執行。延遲執行的查詢中包含的函數直到此查詢執行時才會執行。為此查詢生 成的 SQL 會轉換成對數據庫中用戶定義函數的調用（請參見此查詢後面的生成 的 SQL語句），當在查詢外部調用這個函數時，LINQ to SQL 會用方法調用表達 式創建一個簡單查詢並執行。　

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

它自動生成的SQL語句如下：

SELECT [t0]. [ProductID], [t0].[ProductName], [t0].[SupplierID],
[t0]. [CategoryID],[t0].[QuantityPerUnit], [t0].[UnitPrice],
[t0]. [UnitsInStock], [t0].[UnitsOnOrder],[t0].[ReorderLevel],
[t0]. [Discontinued]FROM [dbo].[Products] AS [t0]
WHERE [t0]. [UnitPrice] =
[dbo].[MinUnitPriceByCategory]([t0].[CategoryID])

3.使用用戶定義的表值函數

表值函數返回單個行集（與 存儲過程不同，存儲過程可返回多個結果形狀）。由於表值函數的返回類型為 Table，因此在 SQL 中可以使用表的任何地方均可以使用表值函數。此外，您還 可以完全像處理表那樣來處理表值函數。

下面的 SQL 用戶定義函數顯式 聲明其返回一個 TABLE。因此，隱式定義了所返回的行集結構。ALTER FUNCTION [dbo].[ProductsUnderThisUnitPrice]
(@price Money
)
RETURNS TABLE
AS
RETURN
　　SELECT *
　　 FROM Products as P
　　Where p.UnitPrice < @price

拖到設計器中，LINQ to SQL 按如下方式映射此函數：

[Function (Name="dbo.ProductsUnderThisUnitPrice",
IsComposable=true)]
public IQueryable<ProductsUnderThisUnitPriceResult>
ProductsUnderThisUnitPrice([Parameter(DbType="Money")]
System.Nullable<decimal> price)
{
　　return this.CreateMethodCallQuery
　　 <ProductsUnderThisUnitPriceResult>(this,
　　((MethodInfo) (MethodInfo.GetCurrentMethod())), price);
}

這時我們 小小的修改一下Discontinued屬性為可空的bool類型。

private System.Nullable<bool> _Discontinued;
public System.Nullable<bool> Discontinued
{
}

我 們可以這樣調用使用了：

var q = from p in db.ProductsUnderThisUnitPrice(10.25M)
　　　　where ! (p.Discontinued ?? false)
　　　　select p;

其生成 SQL語句如下：

SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID],
[t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice],
[t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel],
[t0].[Discontinued]
FROM [dbo]. [ProductsUnderThisUnitPrice](@p0) AS [t0]
WHERE NOT ((COALESCE ([t0].[Discontinued],@p1)) = 1)
-- @p0: Input Money (Size = 0; Prec = 19; Scale = 4) [10.25]
-- @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [0]4.以聯接方式使用用戶定義的表值函數

我們 利用上面的ProductsUnderThisUnitPrice用戶定義函數，在 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 代碼說明對此函數返回 的表執行聯接。

SELECT [t0].[CategoryID], [t0]. [CategoryName],
[t1].[ProductName], [t1].[UnitPrice]
FROM [dbo].[Categories] AS [t0]
CROSS JOIN [dbo]. [ProductsUnderThisUnitPrice](@p0) AS [t1]
WHERE ([t0]. [CategoryID]) = [t1].[CategoryID]
-- @p0: Input Money (Size = 0; Prec = 19; Scale = 4) [8.50]