Table-Valued Function in Entity Framework 6 DB-First Approach

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]
GO
/****** Object:  UserDefinedFunction [dbo].[GetCourseListByStudentID]  */  
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetCourseListByStudentID]
(    
    -- Add the parameters for the function here
    @studentID int
)
RETURNS TABLE 
AS
RETURN 
(
    -- 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.

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).

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:

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

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

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)
                     .ToList<Course>();
}