使用者定義函數
我們可以在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]
留言列表