http://www.prg-cn.com/article-4433-1.html
用户定义函数
我们可以在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]