Entity Framework - Table-Valued Function

In this chapter, let us learn how to map Table-valued Functions (TVFs) using the Entity Framework Designer and how to call a TVF from a LINQ query.

TVFs are currently only supported in the Database First workflow.

It was first introduced in Entity Framework version 5.

To use the TVFs you must target .NET Framework 4.5 or above.

It is very similar to stored procedures but with one key difference, i.e., the result of a TVF is composable. This means the results from a TVF can be used in a LINQ query while the results of a stored procedure cannot.

Let’s take a look at the following example of creating a new project from File → New → Project.

Step 1 − Select the Console Apppcation from the middle pane and enter TableValuedFunctionDemo in the name field.

Step 2 − In Server explorer right-cpck on your database.

Step 3 − Select New Query and enter the following code in T-SQL editor to add a new table in your database.

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id =
   OBJECT_ID(N [dbo].[StudentGrade] ) AND type in (N U ))

BEGIN

   CREATE TABLE [dbo].[StudentGrade](

      [EnrollmentID] [int] IDENTITY(1,1) NOT NULL,
      [CourseID] [int] NOT NULL,
      [StudentID] [int] NOT NULL,
      [Grade] [decimal](3, 2) NULL,

      CONSTRAINT [PK_StudentGrade] PRIMARY KEY CLUSTERED ([EnrollmentID] ASC)

      WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

   ) ON [PRIMARY]

END
GO

Step 4 − Right-cpck on the editor and select Execute.

Step 5 − Right-cpck on your database and cpck refresh. You will see the newly added table in your database.

Step 6 − Now create a function that will return student grades for course. Enter the following code in T-SQL editor.

CREATE FUNCTION [dbo].[GetStudentGradesForCourse]

(@CourseID INT)

RETURNS TABLE

RETURN
   SELECT [EnrollmentID],
      [CourseID],
      [StudentID],
      [Grade]
   FROM   [dbo].[StudentGrade]
   WHERE  CourseID = @CourseID 

Step 7 − Right-cpck on the editor and select Execute.

Now you can see that the function is created.

Step 8 − Right cpck on the project name in Solution Explorer and select Add → New Item.

Step 9 − Then select ADO.NET Entity Data Model in the Templates pane.

Step 10 − Enter TVFModel as name, and then cpck Add.

Step 11 − In the Choose Model Contents dialog box, select EF designer from database, and then cpck Next.

Step 12 − Select your database and cpck Next.

Step 13 − In the Choose Your Database Objects dialog box select tables, views.

Step 14 − Select the GetStudentGradesForCourse function located under the Stored Procedures and Functions node and cpck Finish.

Step 15 − Select View → Other Windows → Entity Data Model Browser and right-cpck GetStudentGradesForCourse under Function Imports and select Edit.

You will see the following dialog.

Step 16 − Cpck on Entities radio button and select Enrollment from the combobox as return type of this Function and cpck Ok.

Let’s take a look at the following C# code in which all the students grade will be retrieved who are enrolled in Course ID = 4022 in database.

class Program {

   static void Main(string[] args) {

      using (var context = new UniContextEntities()) {

         var CourseID = 4022;

         // Return all the best students in the Microeconomics class.
         var students = context.GetStudentGradesForCourse(CourseID);

         foreach (var result in students) {
            Console.WriteLine("Student ID:  {0}, Grade: {1}",
               result.StudentID, result.Grade);
         }

         Console.ReadKey();
      }
   }
}

When the above code is compiled and executed you will receive the following output −

Student ID: 1, Grade: 2
Student ID: 4, Grade: 4
Student ID: 9, Grade: 3.5

We recommend that you execute the above example in a step-by-step manner for better understanding.