表值函数 (Tvf) Table-Valued Functions (TVFs)

备注

EF5 仅向前 -实体框架5中引入了本页中所述的功能、api 等。EF5 Onwards Only - The features, APIs, etc. discussed in this page were introduced in Entity Framework 5. 如果使用的是早期版本,则部分或全部信息不适用。If you are using an earlier version, some or all of the information does not apply.

视频和分步演练演示了如何使用 Entity Framework Designer 将表值函数映射 (Tvf) 。The video and step-by-step walkthrough shows how to map table-valued functions (TVFs) using the Entity Framework Designer. 它还演示了如何从 LINQ 查询调用 TVF。It also demonstrates how to call a TVF from a LINQ query.

Tvf 当前仅在 Database First 工作流中受支持。TVFs are currently only supported in the Database First workflow.

实体框架版本5中引入了 TVF 支持。TVF support was introduced in Entity Framework version 5. 请注意,若要使用表值函数、枚举和空间类型等新功能,则必须以 .NET Framework 4.5 为目标。Note that to use the new features like table-valued functions, enums, and spatial types you must target .NET Framework 4.5. 默认情况下,Visual Studio 2012 面向 .NET 4.5。Visual Studio 2012 targets .NET 4.5 by default.

Tvf 非常类似于具有一个关键区别的存储过程: TVF 的结果是可组合的。TVFs are very similar to stored procedures with one key difference: the result of a TVF is composable. 这意味着,可以在 LINQ 查询中使用 TVF 的结果,而存储过程的结果不能。That means the results from a TVF can be used in a LINQ query while the results of a stored procedure cannot.

观看视频Watch the video

提供者: Julia KornichPresented By: Julia Kornich

WMV | MP4有 | WMV (ZIP) WMV | MP4 | WMV (ZIP)

先决条件Pre-Requisites

若要完成本演练,你需要:To complete this walkthrough, you need to:

设置项目Set up the Project

  1. 打开 Visual StudioOpen Visual Studio
  2. 在 "文件" 菜单上,指向 "新建",然后单击 "项目"On the File menu, point to New, and then click Project
  3. 在左窗格中,单击 " Visual # C",然后选择控制台模板In the left pane, click Visual C#, and then select the Console template
  4. 输入 TVF 作为项目名称,然后单击 "确定"Enter TVF as the name of the project and click OK

将 TVF 添加到数据库Add a TVF to the Database

  • 选择 " 查看- > SQL Server 对象资源管理器Select View -> SQL Server Object Explorer
  • 如果 LocalDB 不在服务器列表中:右键单击 " SQL Server ",然后选择 " 添加 SQL Server 使用默认 Windows 身份验证 连接到 LocalDB 服务器If LocalDB is not in the list of servers: Right-click on SQL Server and select Add SQL Server Use the default Windows Authentication to connect to the LocalDB server
  • 展开 LocalDB 节点Expand the LocalDB node
  • 在 "数据库" 节点下,右键单击 "School" 数据库节点,然后选择 " 新建查询 ... "Under the Databases node, right-click the School database node and select New Query…
  • 在 T-sql 编辑器中粘贴以下 TVF 定义In T-SQL Editor, paste the following TVF definition
CREATE FUNCTION [dbo].[GetStudentGradesForCourse]

(@CourseID INT)

RETURNS TABLE

RETURN
    SELECT [EnrollmentID],
           [CourseID],
           [StudentID],
           [Grade]
    FROM   [dbo].[StudentGrade]
    WHERE  CourseID = @CourseID
  • 在 T-sql 编辑器上单击鼠标右键按钮,然后选择 " 执行"Click the right mouse button on the T-SQL editor and select Execute
  • GetStudentGradesForCourse 函数将添加到 School 数据库The GetStudentGradesForCourse function is added to the School database

 

创建模型Create a Model

  1. 右键单击 "解决方案资源管理器中的项目名称,指向"添加",然后单击"新建项"Right-click the project name in Solution Explorer, point to Add, and then click New Item
  2. 从左侧菜单中选择 "数据",然后在 "模板" 窗格中选择 " ADO.NET 实体数据模型Select Data from the left menu and then select ADO.NET Entity Data Model in the Templates pane
  3. 输入TVFModel作为文件名,然后单击 "添加"Enter TVFModel.edmx for the file name, and then click Add
  4. 在 "选择模型内容" 对话框中,选择 " 从数据库生成",然后单击 " 下一步"In the Choose Model Contents dialog box, select Generate from database, and then click Next
  5. 单击 " 新建连接",在 "服务器名称" 文本框中** (Localdb) \ mssqllocaldb输入School**   作为数据库名称单击 "确定"Click New Connection Enter (localdb)\mssqllocaldb in the Server name text box Enter School for the database name Click OK
  6. 在 "选择数据库对象" 对话框中的 " " 节点下,   选择 " Person"、" StudentGrade" 和 " 课程"   表In the Choose Your Database Objects dialog box, under the Tables node, select the PersonStudentGrade, and Course tables
  7. 从 Visual Studio 2012 开始,选择 " 存储过程" 和 "函数" 节点下的 GetStudentGradesForCourse函数,该   Entity Designer 允许批处理导入存储过程和函数Select the GetStudentGradesForCourse function located under the Stored Procedures and Functions node Note, that starting with Visual Studio 2012, the Entity Designer allows you to batch import your Stored Procedures and Functions
  8. 单击 " 完成"Click Finish
  9. 此时会显示 Entity Designer,它提供了用于编辑模型的设计图面。The Entity Designer, which provides a design surface for editing your model, is displayed. 您在 " 选择数据库对象" 对话框中选择的所有对象   都将添加到模型中。All the objects that you selected in the Choose Your Database Objects dialog box are added to the model.
  10. 默认情况下,每个导入的存储过程或函数的结果形状将自动成为实体模型中的新复杂类型。By default, the result shape of each imported stored procedure or function will automatically become a new complex type in your entity model. 但我们想要将 GetStudentGradesForCourse 函数的结果映射到 StudentGrade 实体:右键单击设计图面并在模型浏览器中选择 " 模型浏览器",选择 " 函数导入",然后在 "编辑函数导入" 对话框中双击 GetStudentGradesForCourse函数,选择 " 实体",   然后选择 " StudentGrade "。But we want to map the results of the GetStudentGradesForCourse function to the StudentGrade entity: Right-click the design surface and select Model Browser In Model Browser, select Function Imports, and then double-click the GetStudentGradesForCourse function In the Edit Function Import dialog box, select Entities and choose StudentGrade

保留和检索数据Persist and Retrieve Data

打开定义 Main 方法的文件。Open the file where the Main method is defined. 将以下代码添加到 Main 函数中。Add the following code into the Main function.

下面的代码演示如何生成使用表值函数的查询。The following code demonstrates how to build a query that uses a Table-valued Function. 该查询将结果投影到一个匿名类型中,该类型包含相关课程标题和一个等级大于或等于3.5 的相关学生。The query projects the results into an anonymous type that contains the related Course title and related students with a grade greater or equal to 3.5.

using (var context = new SchoolEntities())
{
    var CourseID = 4022;
    var Grade = 3.5M;

    // Return all the best students in the Microeconomics class.
    var students = from s in context.GetStudentGradesForCourse(CourseID)
                            where s.Grade >= Grade
                            select new
                            {
                                s.Person,
                                s.Course.Title
                            };

    foreach (var result in students)
    {
        Console.WriteLine(
            "Couse: {0}, Student: {1} {2}",
            result.Title,  
            result.Person.FirstName,  
            result.Person.LastName);
    }
}

编译并运行该应用程序。Compile and run the application. 该程序生成以下输出:The program produces the following output:

Couse: Microeconomics, Student: Arturo Anand
Couse: Microeconomics, Student: Carson Bryant

总结Summary

在本演练中,我们介绍了如何使用 Entity Framework Designer 将表值函数映射 (Tvf) 。In this walkthrough we looked at how to map Table-valued Functions (TVFs) using the Entity Framework Designer. 它还演示了如何从 LINQ 查询调用 TVF。It also demonstrated how to call a TVF from a LINQ query.