Linq To Sql进阶系列(四)User Define Function篇
User Define Function, 用户自定义函数,简称UDF. 关于sql server中的udf,请大家参考一文。本文主要阐述,在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
看下面这个例子。返回某个类别产品最小的单元价格。
CREATEFUNCTION[dbo].[MinUnitPriceByCategory]
(@categoryIDINT
)
RETURNSMoney
AS
BEGIN
-- Declare the return variable here
DECLARE@ResultVarMoney
-- 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(请参考 )将其映射为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的例子
CREATEFUNCTION[dbo].[TotalProductUnitPriceByCategory]
(@categoryIDint)
RETURNSMoney
AS
BEGIN
-- Declare the return variable here
DECLARE@ResultVarMoney
-- Add the T-SQL statements to compute the return value here
SELECT@ResultVar= (SelectSUM(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
orderby 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为
SELECTCONVERT(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为:
SELECTTOP10*FROM TABLE1 ORDERBYNEWID();
那用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.看下面例子
CREATEFUNCTION[dbo].[ProductsUnderThisUnitPrice]
(@priceMoney
)
RETURNSTABLE
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,但这并不说明,任何函数都适用。
来源: http://lib.csdn.net/article/dotnet/41960