How to: Call Custom Database Functions

Article
09/15/2021

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 1

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

Example 2

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>

Example 3

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 static decimal? AvgStudentGrade(int studentId)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

<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

Example 4

Finally, call the method in a LINQ to Entities query. The following code displays students' last names and average grades to the console:

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);
    }
}

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

Share via