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.
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.
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.
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.
[!CODE [DP L2E MapToDBFunction#1](../CodeSnippet/VS_Snippets_Data/dp l2e maptodbfunction#1)]
Next, declare a function in the store schema definition language (SSDL) of your .edmx file. the following code declares the
AvgStudentGrade function in SSDL:
[!CODE [DP L2E MapToDBFunction#2](../CodeSnippet/VS_Snippets_Data/dp l2e maptodbfunction#2)]
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.
[!CODE [DP L2E MapToDBFunction#3](../CodeSnippet/VS_Snippets_Data/dp l2e maptodbfunction#3)]
Finally, call the method in a LINQ to Entities query. The following code displays students' last names and average grades to the console:
[!CODE [DP L2E MapToDBFunction#4](../CodeSnippet/VS_Snippets_Data/dp l2e maptodbfunction#4)]