Execute Stored Procedure using DBContext

Here, we will use stored procedure to get the Courses by Student. So we will create following "GetCoursesByStudentId" stored procedure:

        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.
            SET NOCOUNT ON;

            -- Insert statements for procedure here
        select c.courseid, c.coursename,c.Location
        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, you have to perform two steps to use this stored procedure in entity framework.

  1. Add the stored procedure in EDM
  2. Add function import.

Add stored procedure in EDM: As we added tables in the EDM, the same way you can add stored procedures in the EDM. If you want to add it in existing EDM the right click on designer and click on "Update model from database..". This will popup update wizard where you can select stored procedures and add it.

Entity Framework

When you click on "Finish", you won't find any changes in the designer that's because stored procedure is not being treated as entity. This step will only add stored procedure in storage model. You can see it in XML view of the designer.

Entity Framework

Wait a minute.. still you cannot use this stored procedure because Entity Framework doesn't allow a stored procedure to be queried until it's mapped in the EDM. So now we have to map this stored procedure to the conceptual model. To do that we have to perform second step, "Add function import".

Add function import: Now in this step, we will import a function for the stored procedure. To do that, right click on the designer surface and select "Model Browser". Here you can see your stored procedure by expanding "Stored Procedures" node of SchoolDBModel.Store. Now, right click on your stored procedure and select "Add function import..".

Entity Framework

This will popup "Add Function Import":

Entity Framework

Here, you can select four types of return values: None, Scalars, Complex and Entities. Let's see each of these:

None: it means stored procedure will not return any value.

Scalars: it means stored procedure will return single value of selected type like binary, Boolean, byte etc.

Complex: It means stored procedure will return complex type which is only on conceptual model but not in database table. You can create complex type here only by first clicking on ‘Get Column Information’ which will get the schema of stored procedure and then click on ‘Create New Complex Type’ which will generate complex type.

Entities: it means stored procedure will return collection of selected entities.

In our case, stored procedure will return collection of Course entity. Click ‘OK’. This will update your conceptual model and bring stored procedure in conceptual model under function import.

Entity Framework

Now, you can execute this stored procedure using DBContext. For example:

   using (var ctx = new SchoolDBEntities())
            //Execute stored procedure as a function
            var courseList = ctx.GetCoursesByStudentId(1).ToList<Course>();

            foreach (Course cs in courseList)
                Console.WriteLine("Course Name: {0}",cs.CourseName);

Above code will result in following command to the database:

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

You can also use stored procedure for add, update or delete operation when you call DBContext.SaveChanges method. So instead of creating SQL query, Entity Framework will use stored procedure for these operations.

Alternatively, you can also execute stored procedure using SqlQuery method. As we have learned about DBContext.Database.SqlQuery method in previous chapter, SqlQuery method is useful to execute raw SQL query to the database. We have to use same method to execute database Stored Procedure. For example:

    using (var ctx = new SchoolDBEntities())
            var idParam = new SqlParameter {
                     ParameterName = "StudentID",
                     Value = 1
            //Get student name of string type
            var courseList = ctx.Database.SqlQuery<Course>("exec GetCoursesByStudentId @StudentId ", idParam).ToList<Course>();
            //Or can call SP by following way
            //var courseList = ctx.Courses.SqlQuery("exec GetCoursesByStudentId @StudentId ", idParam).ToList<Course>();

            foreach (Course cs in courseList)
                Console.WriteLine("Course Name: {0}",cs.CourseName);

You can execute Stored Procedure by two way:

1. Using DBContext.Database.SqlQuery which returns any type of entity tracked by DBContext. Eg:

        var courseList = ctx.Database.SqlQuery<Course>(
            "exec GetCoursesByStudentId @StudentId ", idParam).ToList<Course>();

2. Using DBSet.SqlQuery which returns entity of same type as DBSet. Eg:

        var courseList = ctx.Courses.SqlQuery(
            "exec GetCoursesByStudentId @StudentId ", idParam).ToList<Course>();

You can use insert Stored Procedure same way.