Stored Procedure in Entity Framework:

Entity Framework has ability to automatically build native commands for database based on your LINQ to Entities or Entity SQL queries, as well as build the commands for inserting, updating, or deleting data, you may want to override these steps and use your own predefined stored procedures. You can use stored procedures mainly for either to get the data or add/update/delete the records to one or multiple database tables.


Stored procedures and user-defined functions (UDFs) in the database are represented as functions in entity framework. So EDM won’t have any entity or other stuff for stored procedures in the EDM designer.


Here, we will add following stored procedure GetCoursesByStudentId into EDM that returns all the courses assigned to particular student:

    CREATE PROCEDURE [dbo].[GetCoursesByStudentId]
        -- Add the parameters for the stored procedure here
        @StudentId int = null
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.

        -- Insert statements for procedure 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

First of all, create new ADO.Net Entity Data Model using EF Designer from database.


Entity Framework stored procedure


Select GetCoursesByStudentId. Make sure Import selected stored procedures and functions into the entity model checkbox is selected and then click Finish.


Entity Framework stored procedure


You would see in GetCoursesByStudentId added in Function Imports with new complex type GetCoursesByStudentId_Result in Model Browser. Whenever you import stored procedure into model, it creates new complex type with the name {sp name}_Result by default.


Entity Framework stored procedure


GetCoursesByStudentId returns same fields defined in Course entity. So we don’t need to add new complex type for return data from GetCoursesByStudentId. You can change it by right click on GetCoursesByStudentId in function imports and select Edit. Check Entities and select Course from dropdown in popup window as below:


Entity Framework stored procedure


Entity Framework stored procedure


You would see function in context class for GetCoursesByStudentId as below:


Entity Framework stored procedure


So now you call GetCoursesByStudentId and get the result as below:

        using (var context = new SchoolDBEntities())
            var courses = context.GetCoursesByStudentId(1);

            foreach (Course cs in courses)

Above code will execute following statement:


exec [dbo].[GetCoursesByStudentId] @StudentId=1


Learn how to use stored procedure for CUD operation in the next chapter.