Linq To Sql进阶系列(四)User Define Function篇

User Define Function, 用户自定义函数,简称UDF. 关于sql server中的udf,请大家参考http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/一文。本文主要阐述,在Linq To Sql中,如何使用UDF.

1,UDF 简介
UDF可以分为两中类型。一种为Scalar Valued Function,简称为SVF,是返回值类型的UDF. 另一种为Table Valued Function 简称为TVF,是返回一个table的UDF. 人们通常喜欢拿UDF和Store Procedure做比较。其实,他们各有千秋。UDF最多只能返回一个RowSet,而Store Procedure可以是多个。Store Procedure支持CUD操作,而UDF不支持。但是UDF在sql 中支持内联查询,这个又是Sprocs所不能及的。因此Linq To Sql 也支持UDF的内联查询。
2,SVF
看下面这个例子。返回某个类别产品最小的单元价格。
CREATE 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
用OR Designer(请参考OR工具介绍 )将其映射为Dbml。如下
  <Function Name="dbo.MinUnitPriceByCategory" Method="MinUnitPriceByCategory" IsComposable="true">
    
<Parameter Name="categoryID" Type="System.Int32" DbType="Int" />
    
<Return Type="System.Decimal" />
  
</Function>
在这里,笔者将带着大家开始习惯用dbml来维护数据库的映射,而不是code.在beta2发布后,有人很快就能发现mapping code无法编译了。因为接口改动了。好,回归正题。
无论是UDF还是Sprocs都会被映射为Function. 而IsComposable="true"是UDF独有的一个特性,是标志UDF身份的,Linq用它来区别Sprocs和UDF。这个字段说明,该函数是支持内联查询的。Name则是其在数据库中的名称。再来看其生成的code.
        [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将Sprocs和UDF映射成DataContext类里的方法的形式,这样用户就可以像调用函数那样,调用该UDF。因为这个例子是SVF,所以,返回decimal类型的值。再来看它的应用。刚才说过,可以像函数那样调用它。比如:
int result = db.IntSVF(variable);  
int result = db.IntSVF(constant); 
再就让我们来看几个内联的查询的。所谓内联(in-line),就是说,你可以把UDF当作一个表(TVF),或一个变量(SVF),写在sql语句里。比如:
SELECT * FROM Products AS t0
                            
WHERE t0.UnitPrice = dbo.MinUnitPriceByCategory(t0.CategoryID)
在这个sql语句中,就调用了上面那个UDF。同样Linq To Sql也支持这样操作。可以写为
            var q = from p in db.Products
                        
where p.UnitPrice == db.MinUnitPriceByCategory(p.CategoryID)
                        
select p;
大家可以看看其生成的Sql是不是和上面的一样。再举一个UDF的例子
CREATE 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
计算某类产品的单价总和。这次,我们在select字句里调用它
SELECT CategoryID, [dbo].[TotalProductUnitPriceByCategory](CategoryID) AS [TotalUnitPrice]
    
FROM Categories
其同样功能的Linq语句为:
            var q = from c in db.Categories
                
select new {c.CategoryID, TotalUnitPrice = db.TotalProductUnitPriceByCategory(c.CategoryID)};
其实,对于SVF,可以放在除from等与table有关语句之外的任何地方。比如Order by, Group by等。同样Linq全部支持。如例
var q =     from p in db.Products
            
where p.UnitsOnOrder >= db.SVF(p.UnitsInStock)
            
group p by db.SVF(p.CategoryID) into g
           
order  by db.SVF(g.Key
     
select db.SVF(g.Key);
当然,这个纯粹是给个例子,并没有太多实际意义。

3,系统函数的映射
目前为止,无论是OR designer还是SqlMetal均不支持对系统函数的映射。笔者也只是尝试着,手工映射,并成功调用。我们拿Var函数举例。Var是求方差。让我们来模仿上面那个dbml来改写自己的dbml。我们将要多money类型做求var值。并且希望能够调用sql server提供的var函数。那就需要我们将映射的名称改成var,并且改动参数和返回值类型。其最后的dbml为:
  <Function Name="Var" Method="Var" IsComposable="true">
    
<Parameter Name="para" Type="System.Decimal" DbType="Money" />
    
<Return Type="System.Decimal" />
  
</Function>
其生成的code为:
        [Function(IsComposable=true)]
        
public System.Nullable<decimal> Var([Parameter(DbType="Money")] System.Nullable<decimal> para)
        
{
            
return ((System.Nullable<decimal>)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), para).ReturnValue));
        }
将该方法,放在DataContext的一个partial类中,我们并不想破坏其原来的mapping 文件,所以,单独放在一个partial类中。而后,我们尝试着Linq To Sql中调用该函数
            var q = (from p in db.Products
                     
select db.Var(p.UnitPrice)).ToList();
其生成的sql为
SELECT CONVERT(Decimal(29,4),Var([t0].[UnitPrice])) AS [value]
FROM [dbo].[Products] AS [t0]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel 
我们就这样骗过了Linq To Sql的Run-Time。 成功调用sql server提供var函数。再比如,有人习惯于用NEWID()随机排序,达到取随机记录的目的。其原始sql为:
SELECT TOP 10 * FROM TABLE1 ORDER BY NEWID();
那用Linq To Sql该如何来做这个事情呢?不好意思,目前还不支持对系统函数的映射。那就手工来做吧。
因为NewId返回uniqueidentifier类型,我们将这个函数定义为
        [Function(Name = "NewID", IsComposable = true)]
        
public Guid NewID()
        
{
            
return ((Guid)(this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))).ReturnValue));
        }

调用时,可以
var q = db.Table1.OrderBy(p => db.NewID()).Take(10).ToList();
这只是一个小技巧,并不说明,所有的函数都可以这么做。

4 TVF
返回一个table 的UDF称为TVF.看下面例子

CREATE FUNCTION [dbo].[ProductsUnderThisUnitPrice]
(
@price Money
)
RETURNS TABLE
AS
RETURN
    
SELECT *
    
FROM Products as P
    
Where p.UnitPrice < @price
TVF在sql中支持from,join,union等操作。同样,这些操作在Linq To Sql中一样支持。该TVF的dbml为:
  <Function Name="dbo.ProductsUnderThisUnitPrice" Method="ProductsUnderThisUnitPrice" IsComposable="true">
    
<Parameter Name="price" Type="System.Decimal" DbType="Money" />
    
<ElementType Name="ProductsUnderThisUnitPriceResult">
      
<Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
      
<Column Name="ProductName" Type="System.String" DbType="NVarChar(40) NOT NULL" CanBeNull="false" />
      
<Column Name="SupplierID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      
<Column Name="CategoryID" Type="System.Int32" DbType="Int" CanBeNull="true" />
      
<Column Name="QuantityPerUnit" Type="System.String" DbType="NVarChar(20)" CanBeNull="true" />
      
<Column Name="UnitPrice" Type="System.Decimal" DbType="Money" CanBeNull="true" />
      
<Column Name="UnitsInStock" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
      
<Column Name="UnitsOnOrder" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
      
<Column Name="ReorderLevel" Type="System.Int16" DbType="SmallInt" CanBeNull="true" />
      
<Column Name="Discontinued" Type="System.Boolean" DbType="Bit NOT NULL" CanBeNull="false" />
    
</ElementType>
  
</Function>
ElementType子项说明了其返回一个table.映射为类的名称为ProductsUnderThisUnitPriceResult。其映射的code中,不光是一个函数,还有一个对应的返回类。当然,这个返回类的定义,可以由用户自己指定。此处不讲。我们使用默认的类。我们先来看一个from的例子
            var q = from p in db.ProductsUnderThisUnitPrice(10.25M)
                    
select p;
你可以就把该udf当作一个普通的表来使用。再举一个join操作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中支持TVF的in-line操作,所以Linq To Sql完全支持其对等的操作。他们所生成的sql语句不再列出。

总结:
通过本文,我们可以看出Linq To Sql完全融入了Sql中UDF,包括对其内联操作的支持。对于某些特殊需求,用户可以手工将函数映射为code,但这并不说明,任何函数都适用。

posted @ 2007-08-10 21:24 Tom Song 阅读(...) 评论(...) 编辑 收藏