How to: Call Custom Database Functions
This topic describes how to call custom functions that are defined in the database from within LINQ to Entities queries.
Database functions that are called from LINQ to Entities queries are executed in the database. Executing functions in the database can improve application performance.
The procedure below provides a high-level outline for calling a custom database function. The example that follows provides more detail about the steps in the procedure.
To call custom functions that are defined in the database
Create a custom function in your database.
For more information about creating custom functions in SQL Server, see CREATE FUNCTION (Transact-SQL).
Declare a function in the store schema definition language (SSDL) of your .edmx file. The name of the function must be the same as the name of the function declared in the database.
For more information, see Function Element (SSDL).
Add a corresponding method to a class in your application code and apply a EdmFunctionAttribute to the method Note that the NamespaceName and FunctionName parameters of the attribute are the namespace name of the conceptual model and the function name in the conceptual model respectively. Function name resolution for LINQ is case sensitive.
Call the method in a LINQ to Entities query.
Example
The following example demonstrates how to call a custom database function from within a LINQ to Entities query. The example uses the School model. For information about the School model, see Creating the School Sample Database and Generating the School .edmx File.
The following code adds the AvgStudentGrade function to the School sample database.
Note
The steps for calling a custom database function are the same regardless of the database server. However, the code below is specific to creating a function in a SQL Server database. The code for creating a custom function in other database servers might differ.
USE [School]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[AvgStudentGrade](@studentId INT)
RETURNS DECIMAL(3,2)
AS
BEGIN
DECLARE @avg DECIMAL(3,2);
SELECT @avg = avg(Grade) FROM StudentGrade WHERE StudentID = @studentId;
RETURN @avg;
END
Next, declare a function in the store schema definition language (SSDL) of your .edmx file. the following code declares the AvgStudentGrade function in SSDL:
<Function Name="AvgStudentGrade" ReturnType="decimal" Schema="dbo" >
<Parameter Name="studentId" Mode="In" Type="int" />
</Function>
Now create a method and map it to the function declared in the SSDL. The method in the following class is mapped to the function defined in the SSDL (above) by using an EdmFunctionAttribute. When this method is called, the corresponding function in the database is executed.
<EdmFunction("SchoolModel.Store", "AvgStudentGrade")>
Public Function AvgStudentGrade(ByVal studentId As Integer) _
As Nullable(Of Decimal)
Throw New NotSupportedException("Direct calls are not supported.")
End Function
[EdmFunction("SchoolModel.Store", "AvgStudentGrade")]
public static decimal? AvgStudentGrade(int studentId)
{
throw new NotSupportedException("Direct calls are not supported.");
}
Finally, call the method in a LINQ to Entities query. The following code displays students' last names and average grades to the console:
Using context As New SchoolEntities()
Dim students = From s In context.People _
Where s.EnrollmentDate IsNot Nothing _
Select New With {.name = s.LastName, _
.avgGrade = AvgStudentGrade(s.PersonID)}
For Each student In students
Console.WriteLine("{0}: {1}", _
student.name, _
student.avgGrade)
Next
End Using
using (SchoolEntities context = new SchoolEntities())
{
var students = from s in context.People
where s.EnrollmentDate != null
select new
{
name = s.LastName,
avgGrade = AvgStudentGrade(s.PersonID)
};
foreach (var student in students)
{
Console.WriteLine("{0}: {1}", student.name, student.avgGrade);
}
}