LINQ to SQL Advanced Series (iv) User Define function article

User Define function, custom functions, short UDF. For a UDF in SQL Server, please refer to the http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/article. This article focuses on how to use UDF in LINQ to SQL.

1,UDF Introduction

The UDF can be divided into two types. A scalar valued Function, referred to as SVF, is a UDF that returns a value type. The other is the table valued Function referred to as TVF, which is the UDF that returns a table. People usually prefer to compare UDF with store Procedure. In fact, they have their own advantages. The UDF can only return one rowset, while the store procedure may be multiple. The Store procedure supports CUD operations and UDF is not supported. But the UDF supports inline queries in SQL, which is sprocs. Therefore LINQ to SQL also supports inline queries for UDF.

2,svf

Look at the example below. Returns the smallest unit price for a category product.

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

Map it to dbml with or Designer (refer to the or tool). As follows

　 <Function Name="dbo.MinUnitPriceByCategory" Method="MinUnitPriceByCategory" IsComposable="true">
　　　 <Parameter Name="categoryID" Type="System.Int32" DbType="Int" />
　　　 <Return Type="System.Decimal" />
　 </Function>