Udostępnij za pośrednictwem


How to: Use Table-Valued User-Defined Functions (LINQ to SQL)

A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table, you can use a table-valued function anywhere in SQL that you can use a table. You can also treat the table-valued function just as you would a table.

Example

The following SQL function explicitly states that it returns a TABLE. Therefore, the returned rowset structure is implicitly defined.

CREATE FUNCTION ProductsCostingMoreThan(@cost money)
RETURNS TABLE
AS
RETURN
    SELECT ProductID, UnitPrice
    FROM Products
    WHERE UnitPrice > @cost

LINQ to SQL maps the function as follows:

   <FunctionAttribute(Name:="dbo.ProductsCostingMoreThan", IsComposable:=True)> _
Public Function ProductsCostingMoreThan(<Parameter(DbType:="Money")> ByVal cost As System.Nullable(Of Decimal)) As IQueryable(Of ProductsCostingMoreThanResult)
       Return Me.CreateMethodCallQuery(Of ProductsCostingMoreThanResult)(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), cost)
   End Function
    [Function(Name="dbo.ProductsCostingMoreThan", IsComposable=true)]
    public IQueryable<ProductsCostingMoreThanResult> ProductsCostingMoreThan([Parameter(DbType="Money")] System.Nullable<decimal> cost)
    {
        return this.CreateMethodCallQuery<ProductsCostingMoreThanResult>(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), cost);
    }

The following SQL code shows that you can join to the table that the function returns and otherwise treat it as you would any other table:

SELECT p2.ProductName, p1.UnitPrice
FROM dbo.ProductsCostingMoreThan(80.50)
AS p1 INNER JOIN Products AS p2 ON p1.ProductID = p2.ProductID

In LINQ to SQL, the query would be rendered as follows:

Dim q = _
From p In db.ProductsCostingMoreThan(80.5), p1 In db.Products _
Where p.ProductID = p1.ProductID _
Select p.ProductID, p1.UnitPrice
var q =
from p in db.ProductsCostingMoreThan(80.50m)
join s in db.Products on p.ProductID equals s.ProductID
select new { p.ProductID, s.UnitPrice };

See Also

Other Resources

User-Defined Functions (LINQ to SQL)