Stored Procedure in Entity Framework 6 DB-First Approach

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, and 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 for one or multiple database tables.

EF API creates a function instead of an entity in EDM for each stored procedure and User-Defined Function (UDF) in the target database.

Let's use stored procedure to fetch the data from the database.

First, create the following stored procedures GetCoursesByStudentId in your SQL Server database. 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

Now, add a new Entity Data Model by right clicking on the project in the solution explorer in Visual Studio (2012/2015/2017) -> Add -> New Item. This will open an Add New Item popup. In the popup, select ADO.NET Entity Data Model and provide an appropriate name for the EDM and click the Add button. This will open Entity Data Model Wizard. Select EF Designer from database and click the Next button, as shown below.

Next, you need to create a connection with your existing database. If this is the first time you are creating an EDM for your database, then you need to create a new connection by clicking on the New Connection... button. Here, we already have a connection, so select the database from the dropdown and click the Next button.

In this step, select GetCoursesByStudentId under Stored Procedures and Functions. Make sure that the Import selected stored procedures and functions into the entity model checkbox is selected and then click Finish.

You will see the GetCoursesByStudentId stored procedure added in Stored Procedures/Functions and Function Imports shown below, with the new complex type GetCoursesByStudentId_Result in the Model Browser. Whenever you import a stored procedure or UDF into an EDM, it creates a new complex type with the name {sp name}_Result by default.

GetCoursesByStudentId returns the same fields defined in the Course entity. So, we don't need to add a new complex type for the GetCoursesByStudentId. You can change it by right clicking on GetCoursesByStudentId in Function Imports and selecting Edit. This will open Edit Function Import popup as shown below.

To set the Course entity as result type, select Entities and select Course from dropdown in the popup window and click OK, as shown below:

This will add the function named GetCoursesByStudentId in the context (derived from DbContext) class as shown below:

Now, you can use GetCoursesByStudentId as a function and get the data, as shown below:

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

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

The above example will execute the following statement in the database:

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

Learn how to use stored procedures for INSERT, UPDATE, and DELETE operations instead of SQL commands in the next chapter.