LINQ体验(15)——LINQ to SQL语句之用户定义函数

来源:互联网 发布:数据调研公司 农业 编辑:程序博客网 时间:2024/05/29 09:50

用户定义函数

我们可以在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 MoneyASBEGIN     -- 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 @ResultVarEND

我们将其拖到设计器中,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 MoneyASBEGIN     -- 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 WHERE p.CategoryID = @categoryID     -- Return the result of the function     RETURN @ResultVarEND

拖到设计器中,生成代码如下:

[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 TABLEASRETURN     SELECT FROM Products as 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 ?? falseselect 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]

作者:李永京YJingLee's Blog
出处:http://lyj.cnblogs.com
 

原创粉丝点击