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

cobra - 2008-3-23 20:21:00

文/lyj 出处/博客园

用户定义函数

我们可以在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]

您可能对 [Linq] 的这些文章也感兴趣：

Linq 使用总结

扩展LINQ to SQL以支持批量删除

微软将遗弃Linq to SQL?

PLINQ改名为Parallel Extensions发布

深入浅出学Linq：Linq to SQL How do I(2)

使用linq to xml 快速创建RSS

LINQ体验(18)——LINQ to SQL语句之视图和继承支持

LINQ and Pipeline Pattern

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

Jimmy Nilsson谈LINQ to SQL

cobra - 2008-3-23 20:25:00

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])

cobra - 2008-3-23 20:26:00

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]

cobra - 2008-3-23 20:28:00

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]

拼吾爱程序人生