使用者定義函數

我們可以在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]
文章標籤
創作者介紹

吉米.NET

Jimmy 發表在 痞客邦 PIXNET 留言(0) 人氣()