Entity Framework - Table-Valued Function

Advertisements

Previous Page
Next Page  

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.

Create Project

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

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

Explorer 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-click on the editor and select Execute.

Select Execute

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

Added Table

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-click on the editor and select Execute.

Editor Select

Now you can see that the function is created.

Function Created

Step 8 − Right click 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.

Entity Template Pane

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

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

Content Dialog Box

Step 12 − Select your database and click Next.

Select Database

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

Object Dialog Box

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

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

Select View

You will see the following dialog.

Dialog

Step 16 − Click on Entities radio button and select Enrollment from the combobox as return type of this Function and click 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.

Advertisements