EF Basic 29:Stored Procedure

Stored Procedure in Entity Framework

Entity Framework has the 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. EDM won't have any entity for the 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 a 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, create a new ADO.Net Entity Data Model using EF Designer from database.

Select GetCoursesByStudentId. Make sure that the 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 a 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 procedures for CUD operation in the next chapter.

Get paid to share your links!