Stored Procedure in Entity Framework

Back to: Entity Framework Tutorials For Begineers and Professionals

Stored Procedure in Entity Framework Database First Approach

In this article, I am going to discuss How to use Stored Procedures in Entity Framework Database First Approach to Perform Database CRUD Operations with Examples. Please read our previous article where we discussed how to perform Bulk DELETE in Entity Framework using the BulkDelete Extension Method with Examples. We are going to work with the same example that we created in our Introduction to Entity Framework Database First Approach article. Please read our introduction to Entity Framework Database First article before proceeding to this article.

What is a Stored Procedure?

A Stored Procedure is a database object which contains Pre-Compiled SQL queries (a group of T-SQL Statements). In other words, we can say that Stored Procedures are a block of code designed to perform a task whenever it is called. Please Click Here to learn more about SQL Server Stored Procedure.

Stored Procedure in Entity Framework Database First Approach

As we already discussed, the Entity Framework automatically builds the SQL Command for the database based on our queries. The Entity Framework also generates the INSERT, UPDATE, and DELETE SQL Command based on the Entity State to perform the INSERT, UPDATE, and DELETE Operations in the database.

Sometimes, you do not want to use the autogenerated SQL Commands instead you want to use your own defined Stored Procedures. Using Stored Procedure, you want to perform the Database CRUD Operations. That means using the Stored Procedure you will get the data as well as you will add, update and delete data from one or more database tables.

While Creating the Entity Data Model, we need to include the Stored Procedure, and Entity Framework will Create a Function instead of an Entity in the Entity Data Model for each stored procedure that you include. So, the Stored Procedure that you defined in the database will be created as a Function in your Entity Data Model and you can check those Functions within the Context Object of your EDMX File.

Example to Understand Stored Procedure in Entity Framework Database First Approach

If you remember while creating the database, we created the following four stored procedures in our database to perform INSERT, UPDATE, DELETE, and SELECT Operations.

spGetCoursesByStudentId Stored Procedure:

The following Stored Procedure is used to Retrieve the Course Details of a Student. The input parameter is StudentId and it is returning the Course Id, Course Name, and the Teacher Id who is teaching that particular course.

-- Get Courses by StudentId Procedure
CREATE PROCEDURE spGetCoursesByStudentId
 @StudentID INT
AS
BEGIN
 SELECT c.CourseId, c.CourseName, 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

spInsertStudent Stored Procedure:

The following Stored Procedure will take the Standard Id, First Name, and Last Name as input parameters and then INSERT the student data into the Student database table. Then it will return the StudentId by using the SCOPE_IDENTITY() function.

-- Insert Student Stored Procedure
CREATE PROCEDURE spInsertStudent
 @StandardId INT,
 @FirstName VARCHAR(100),
 @LastName VARCHAR(100)
AS
BEGIN
     INSERT INTO Student(FirstName ,LastName, StandardId)
     VALUES(@FirstName, @LastName, @StandardId);
     SELECT SCOPE_IDENTITY() AS StudentId
END

spUpdateStudent Stored Procedure:

The following Stored Procedure will take the StudentId, StandardId, FirstName, and LastName as input parameters and then update the StandardId, FirstName, and LastName data into the Student database table based on the StudentId.

-- Update Student Stored Procedure
CREATE PROCEDURE spUpdateStudent
 @StudentId INT,
 @StandardId INT,
 @FirstName VARCHAR(100),
 @LastName VARCHAR(100)
AS
BEGIN
    UPDATE Student
       SET FirstName = @FirstName,
           LastName = @LastName,
           StandardId = @StandardId
    WHERE StudentId = @StudentId;
END

spDeleteStudent Stored Procedure:

The following Stored Procedure is used to Delete a Student record from the Student Database Table based on the StudentId.

-- Delete Student Stored Procedure
CREATE PROCEDURE spDeleteStudent
 @StudentId int
AS
BEGIN
    DELETE FROM Student
 WHERE StudentId = @StudentId
END

Further, if you remember while creating the Entity Data Model, we have also included the above four stored procedures. If you can verify the same in the Entity Data Model. Click on the Update Model From Database option in the Entity Data Model and then click on the Refresh button and expand the Stored Procedure and Function tab as shown in the below image. Here, you can see the four stored procedure names as these are included in the Entity Data Model.

Now, these Stored Procedures will not be created as Entity in the Entity Data Model instead they will be created as Functions in the context class. Now, you verify the same in the context class and you will four methods are created with the same name as the stored procedure name as shown in the below image.

Now, let us proceed and see how to use the above four methods to call the four stored procedures that we defined in our database.

Calling spGetCoursesByStudentId Stored Procedure using Entity Framework:

For the spGetCoursesByStudentId Stored Procedure, the Entity Framework created the following spGetCoursesByStudentId method within the context class. This method takes the Student Id as an input parameter and sends that value as a parameter and calls the spGetCoursesByStudentId procedure and the result of the stored procedure is storing spGetCoursesByStudentId_Result type.

public virtual ObjectResult<spGetCoursesByStudentId_Result> spGetCoursesByStudentId(Nullable<int> studentID)
{
    var studentIDParameter = studentID.HasValue ?
        new ObjectParameter("StudentID", studentID) :
        new ObjectParameter("StudentID", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<spGetCoursesByStudentId_Result>("spGetCoursesByStudentId", studentIDParameter);
}

Whenever the Stored Procedure returns more than one column value, then based on the column name and type of values, it will create a complex type i.e. class, and store the result in that type. Now, modify the Main method as shown below. Here, using the context object we are calling the spGetCoursesByStudentId method by passing the student id 1.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                context.Database.Log = Console.Write;
                //Fetch the Course Detail of Student whose Id is 1
                var courses = context.spGetCoursesByStudentId(1);
                Console.WriteLine("Course Detail of Student ID: 1");
                foreach (spGetCoursesByStudentId_Result course in courses)
                {
                    Console.WriteLine($"\tCourse ID: {course.CourseId}, Course Name: {course.CourseName}, TeacherId: {course.TeacherId}");
                }
            }
            Console.Read();
        }
    }
}

Output:

As you can see in the above image, it is just calling the stored procedure by passing the value 1 for the StudentId input parameter.

Calling spInsertStudent Stored Procedure using Entity Framework:

For the spInsertStudent Stored Procedure, the Entity Framework created the following spInsertStudent method within the context class. This method takes the Standard Id, FirstName, and LastName as input parameters and sends these values for the StandardId, FirstName, and LastName input parameters of the Stored Procedure and then calls the spInsertStudent procedure. And the result of the stored procedure it is returning from the method which is nothing but the newly generated StudentId in the Student database table.

public virtual ObjectResult<Nullable<decimal>> spInsertStudent(Nullable<int> standardId, string firstName, string lastName)
{
    var standardIdParameter = standardId.HasValue ?
        new ObjectParameter("StandardId", standardId) :
        new ObjectParameter("StandardId", typeof(int));

    var firstNameParameter = firstName != null ?
        new ObjectParameter("FirstName", firstName) :
        new ObjectParameter("FirstName", typeof(string));

    var lastNameParameter = lastName != null ?
        new ObjectParameter("LastName", lastName) :
        new ObjectParameter("LastName", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Nullable<decimal>>("spInsertStudent", standardIdParameter, firstNameParameter, lastNameParameter);
}

Now, modify the Main method as shown below. Here, using the context object we are calling the spInsertStudent method by passing the Standard Id, First Name, and Last Name values, and then what the method returning we are printing in the Console window which is nothing but the newly generated Student Id.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                //To See the Generated SQL By Context Object
                context.Database.Log = Console.Write;
                
                var queryResult = context.spInsertStudent(1, "Pranaya", "Rout");
                foreach (var StudentId in queryResult)
                {
                    Console.WriteLine($"Newly Generated Student Id: {StudentId}");
                }
            }
            Console.Read();
        }
    }
}

Output:

Calling spUpdateStudent Stored Procedure using Entity Framework:

For the spUpdateStudent Stored Procedure, the Entity Framework created the following spUpdateStudent method within the context class. This method takes the StudentId, StandardId, FirstName, and LastName as input parameters and sends these values for the StudentId, StandardId, FirstName, and LastName input parameters of the Stored Procedure and then calls the spUpdateStudent procedure. The Stored Procedure then updates the first name, last name, and standard id based on the student Id.

public virtual int spUpdateStudent(Nullable<int> studentId, Nullable<int> standardId, string firstName, string lastName)
{
    var studentIdParameter = studentId.HasValue ?
        new ObjectParameter("StudentId", studentId) :
        new ObjectParameter("StudentId", typeof(int));

    var standardIdParameter = standardId.HasValue ?
        new ObjectParameter("StandardId", standardId) :
        new ObjectParameter("StandardId", typeof(int));

    var firstNameParameter = firstName != null ?
        new ObjectParameter("FirstName", firstName) :
        new ObjectParameter("FirstName", typeof(string));

    var lastNameParameter = lastName != null ?
        new ObjectParameter("LastName", lastName) :
        new ObjectParameter("LastName", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("spUpdateStudent", studentIdParameter, standardIdParameter, firstNameParameter, lastNameParameter);
}

Now, modify the Main method as shown below. Here, using the context object we are calling the spUpdateStudent method by passing the Student Id, Standard Id, First Name, and Last Name values.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                //To See the Generated SQL By Context Object
                context.Database.Log = Console.Write;
                
                int Result = context.spUpdateStudent(1, 1, "Pranaya", "Rout");
                Console.WriteLine("spUpdateStudent" + Result);
            }
            Console.Read();
        }
    }
}

Output:

Calling spDeleteStudent Stored Procedure using Entity Framework:

For the spDeleteStudent Stored Procedure, the Entity Framework created the following spDeleteStudent method within the context class. This method takes the StudentId as an input parameter and then calls the spDeleteStudent procedure. The Stored Procedure then deletes that student record from the database.

public virtual int spDeleteStudent(Nullable<int> studentId)
{
    var studentIdParameter = studentId.HasValue ?
        new ObjectParameter("StudentId", studentId) :
        new ObjectParameter("StudentId", typeof(int));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("spDeleteStudent", studentIdParameter);
}

Now, modify the Main method as shown below. Here, using the context object we are calling the spDeleteStudent method by passing the Student Id that we want to delete from the database.

using System;
namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                //To See the Generated SQL By Context Object
                context.Database.Log = Console.Write;
                
                int Result = context.spDeleteStudent(10125);
                Console.WriteLine("spDeleteStudent: " + Result);
            }
            Console.Read();
        }
    }
}

Output:

How to Use Stored Procedure with Output Parameter in Entity Framework?

So far, we have discussed the Stored Procedure with Input Parameters. It is also possible to create a stored procedure with an output parameter and call that stored procedure using Entity Framework. Let us understand this with an example.

First, execute the following SQL Script in our EF_Demo_DB database. The following stored procedure is used to insert the student data into the Student database table. It is taking Standard ID, First Name, and Last Name as input parameters and StudentId as the output parameter. Then as part of the Stored Procedure body, we are inserting the data into the Student table and then set the StudentId using the Scope_Identity function.

-- Stored Procedure with Input and Output Parameters
CREATE PROCEDURE spCreateStudent
 @StandardId INT,
 @FirstName VARCHAR(100),
 @LastName VARCHAR(100),
 @StudentId INT OUT 
AS
BEGIN
     INSERT INTO Student(FirstName ,LastName, StandardId)
     VALUES(@FirstName, @LastName, @StandardId);
     SELECT @StudentId = SCOPE_IDENTITY()
END
GO

Next, we need to update our Entity Data Model to include the newly created Stored Procedure spCreateStudent. So, open the Visual Designer of Entity Data Model and then right-click and select the Update Model from the Database option from the context menu as shown in the below image.

Once you click on the Update Model from Database option, it will open the below Update Wizard. Here, from the Add tab, expand the Stored Procedure and Function section and checked the check box spCreateStudent, and click on the Finish button as shown in the below image.

Save the changes. Now, it should add the above-Stored Procedure spCReateStudent as a Method inside the Context class as follows.

public virtual int spCreateStudent(Nullable<int> standardId, string firstName, string lastName, ObjectParameter studentId)
{
    var standardIdParameter = standardId.HasValue ?
        new ObjectParameter("StandardId", standardId) :
        new ObjectParameter("StandardId", typeof(int));

    var firstNameParameter = firstName != null ?
        new ObjectParameter("FirstName", firstName) :
        new ObjectParameter("FirstName", typeof(string));

    var lastNameParameter = lastName != null ?
        new ObjectParameter("LastName", lastName) :
        new ObjectParameter("LastName", typeof(string));

    return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("spCreateStudent", standardIdParameter, firstNameParameter, lastNameParameter, studentId);
}

Now, modify the Main method to use the above Method to create a student entity in the database. Remember, now the stored procedure uses an output parameter.

using System;
using System.Data.Entity.Core.Objects;

namespace DBFirstApproach
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var context = new EF_Demo_DBEntities())
            {
                //To See the Generated SQL By Context Object
                context.Database.Log = Console.Write;

                //Specify the Output Parameter name and type of the parameter
                ObjectParameter outputParameter = new ObjectParameter("StudentId", typeof(int));
                context.spCreateStudent(1, "Pranaya", "Rout", outputParameter);

                //Access the value from the ObjectParameter using the Value Property
                Console.WriteLine($"Student ID: {outputParameter.Value}");
            }
            Console.Read();
        }
    }
}

Output:

In the next article, I am going to discuss How to use Stored Functions in Entity Framework Database First Approach with Examples. Here, in this article, I try to explain How to use Stored Procedures in Entity Framework Database First Approach to Perform CRUD Operations with Examples. I hope you enjoy this Stored Procedures in Entity Framework to Perform CRUD Operations article.