240
Chapter 11: LINQ to SQL Queries
In the following example, a user-defined function will return table values. In SQL Server
Management Studio, create the following user-defined function, which accepts a parameter and returns a
resultset:
CREATE FUNCTION [dbo].[EmployeesByManagerID]
(
@ManagerID int
)
RETURNS TABLE
AS
RETURN
(
select pc.ContactID, pc.FirstName, pc.LastName, emp.Title
from Person.Contact pc
INNER JOIN HumanResources.Employee emp ON pc.ContactID = emp.ContactID
WHERE ManagerID = @ManagerID
)
Next, create a mapping to the UDF you just created. In Visual Studio, add the following code below
the previous stored procedure mapping. This method is annotated with the Function attribute, telling the
DataContext that a user-defined function will be called.
[Function(Name = "[EmployeeInfo]")]
public IQueryable
EmpInfo(System.Nullable ManagerID)
{
MethodCallExpression mce = Expression.Call(Expression.Constant(this),
((MethodInfo)(MethodInfo.GetCurrentMethod())),
new Expression[]
{
Expression.
Pages:
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391