如何:调用自定义数据库函数How to: Call Custom Database Functions

本主题介绍如何从 LINQ to Entities 查询中调用在数据库中定义的自定义函数。This topic describes how to call custom functions that are defined in the database from within LINQ to Entities queries.

从 LINQ to Entities 查询中调用的数据库函数在数据库中执行。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

  1. 在数据库中创建自定义函数。Create a custom function in your database.

    有关在 SQL Server 中创建自定义函数的详细信息,请参阅 CREATE FUNCTION (transact-sql) For more information about creating custom functions in SQL Server, see CREATE FUNCTION (Transact-SQL).

  2. 在您的 .edmx 文件的存储架构定义语言 (SSDL) 中声明一个函数。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.

    有关详细信息,请参阅 Function 元素 (SSDL) For more information, see Function Element (SSDL).

  3. 将相应的方法添加到应用程序代码中的类,并将 EdmFunctionAttribute 应用于该方法。注意,该特性的 NamespaceNameFunctionName 参数分别是概念模型的命名空间名称和概念模型中的函数名称。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. LINQ 的函数名称解析区分大小写。Function name resolution for LINQ is case sensitive.

  4. 在 LINQ to Entities 查询中调用此方法。Call the method in a LINQ to Entities query.

示例Example

下面的示例演示如何从 LINQ to Entities 查询中调用自定义数据库函数。The following example demonstrates how to call a custom database function from within a LINQ to Entities query. 本示例使用 School 模型。The example uses the School model. 有关 School 模型的信息,请参阅 创建 School 示例数据库生成 school .edmx 文件For information about the School model, see Creating the School Sample Database and Generating the School .edmx File.

下面的代码将 AvgStudentGrade 函数添加到 School 示例数据库。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. 但是,下面的代码特定于在 SQL 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

接下来,使用存储架构定义语言( (SSDL) 的 .edmx 文件)声明函数。Next, declare a function in the store schema definition language (SSDL) of your .edmx file. 下面的代码 AvgStudentGrade 在 SSDL 中声明函数: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

现在,创建一个方法并将其映射到在 SSDL 中声明的函数。Now, create a method and map it to the function declared in the SSDL. 通过使用 EdmFunctionAttribute 将以下类中的方法映射到在 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

最后,在 LINQ to Entities 查询中调用此方法。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

请参阅See also