Table-Valued Functions (TVFs)

Article
10/14/2020

Note

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.

The video and step-by-step walkthrough shows how to map table-valued functions (TVFs) using the Entity Framework Designer. It also demonstrates how to call a TVF from a LINQ query.

TVFs are currently only supported in the Database First workflow.

TVF support was introduced in Entity Framework version 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 targets .NET 4.5 by default.

TVFs are very similar to stored procedures with one key difference: the result of a TVF is composable. 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

Presented By: Julia Kornich

WMV | MP4 | WMV (ZIP)

Pre-Requisites

To complete this walkthrough, you need to:

Install the School database.
Have a recent version of Visual Studio

Set up the Project

Open Visual Studio
On the File menu, point to New, and then click Project
In the left pane, click Visual C#, and then select the Console template
Enter TVF as the name of the project and click OK

Add a TVF to the Database

Select View -> SQL Server Object Explorer
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
Expand the LocalDB node
Under the Databases node, right-click the School database node and select New Query…
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

Click the right mouse button on the T-SQL editor and select Execute
The GetStudentGradesForCourse function is added to the School database

Create a Model

Right-click the project name in Solution Explorer, point to Add, and then click New Item
Select Data from the left menu and then select ADO.NET Entity Data Model in the Templates pane
Enter TVFModel.edmx for the file name, and then click Add
In the Choose Model Contents dialog box, select Generate from database, and then click Next
Click New Connection Enter (localdb)\mssqllocaldb in the Server name text box Enter School for the database name Click OK
In the Choose Your Database Objects dialog box, under the Tables node, select the Person, StudentGrade, and Course tables
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
Click Finish
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.
By default, the result shape of each imported stored procedure or function will automatically become a new complex type in your entity model. 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

Open the file where the Main method is defined. Add the following code into the Main function.

The following code demonstrates how to build a query that uses a Table-valued Function. 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

In this walkthrough we looked at how to map Table-valued Functions (TVFs) using the Entity Framework Designer. It also demonstrated how to call a TVF from a LINQ query.

Share via