Table-Valued Function in Entity Framework:

Table-valued function is similar to the stored procedure, except for one key difference: the result of TVF is composable which means that it can be used in the LINQ-to-Entity query.

The following is a sample TVF GetCourseListByStudentID in SQL Server database, which will return all the courses of a particular student.

USE [SchoolDB]
/****** Object:  UserDefinedFunction [dbo].[GetCourseListByStudentID]  */  
CREATE FUNCTION [dbo].[GetCourseListByStudentID]
    -- Add the parameters for the function here
    @studentID int
    -- Add the SELECT statement with parameter references here
    select c.courseid, c.coursename,c.Location, c.TeacherId
    from student s left outer join studentcourse sc on sc.studentid = s.studentid 
        left outer join course c on c.courseid = sc.courseid
        where s.studentid = @studentID

Now, update your EDM and add this TVF into your exiting EDM by right clicking on the Designer -> select Update Model from the Database.., as shown below.

Entity Framework 5.0 Tutorial

In the update wizard, expand the Stored Procedures and Functions -> Schema -> select GetCourseListByStudentID and click Finish. Make sure that the checkbox for Import selected procedures and functions into the entity model is checked (this will import the function automatically).

Entity Framework 5.0 Tutorial

This will add TVF as a function and create a new complex type GetCourseListByStudentID_Result for the result. Our TVF GetCourseListByStudentID returns columns from the Course table and so we can map the Course entity with the result instead of using a complex type. To do so, open Model Browser -> Function Imports -> right click on imported function 'GetCourseListByStudentID' -> click Edit:

Entity Framework 5.0 Tutorial

You can see that EDM has automatically created the complex type GetCourseListByStudentID_Result as a return collection type.

Entity Framework 5.0 Tutorial

Change Returns a Collection Of to Entities and select the Course entity, as shown below

Entity Framework 5.0 Tutorial

Now, you can execute a table-valued function as a function of DBContext, as shown below:

using (var ctx = new SchoolDBEntities())
    //Execute TVF and filter result
    var courses = ctx.GetCourseListByStudentID(1)

Test Your Entity Framework Knowledge: