Stored Procedure in Entity Framework:

Entity Framework has ability to automatically build native commands for the 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 either to get the data or to 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 as stored procedures in the EDM designer.

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


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

        -- 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
    END

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


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


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


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



You will see the function in the context class for GetCoursesByStudentId as shown below:


Now GetCoursesByStudentId can be called and the result shown below will be returned:

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

            foreach (Course cs in courses)
                Console.WriteLine(cs.CourseName);
        }

The code shown above will execute the following statement:

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

You will learn how to use stored procedure for CUD operation in the next chapter.