How to Execute Stored Procedures in Entity Framework

By: Prasanth Pokuri

Senior .NET Developer/Lead @ Key2 Consulting

 

This article will show you the different ways you can execute a stored procedure in Entity Framework that returns a single result set and multiple result sets.

Here are the 3 ways:

  1. Add a Stored Proc in EDM using EF designer and execute using DbContext
  2. Execute a Stored proc using DbContext and SqlQuery
  3. Execute a Stored Proc that returns multiple result sets in Entity Framework

Let’s get started.

Add a Stored Proc in EDM using EF designer and execute using DbContext

You add stored procedures the same way you add database tables in EDM. To add to an existing EDM, right click on the EF designer and click on “Update model from database..” This will popup the update wizard, where you can select stored procedures as shown below.

The Entity Framework has the capability of importing a Stored Procedure as a function. We can also map the result of the function back to any entity type or complex type (custom model).

The following is the procedure to import and use a Stored Procedure in Entity Framework.

As shown below, select the GetCoursesByStudentId stored procedure and make sure that the Import selected stored procedures and functions into the entity model checkbox is selected. Then click Finish.

GetCoursesByStudentId will be 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 Stored Proc returns the same fields defined in Course entity. So we don’t need to add a new complex type (to create a custom complex type, right click on GetCoursesByStudentId in function imports and select Edit).

Here, we see four types of return values: None, Scalars, Complex, and Entities.

None: Stored Procedure will not return any value.

Scalars: Stored Procedure will return single value of selected type like binary, Boolean, byte, etc.

Complex: Stored Procedure will return complex type (Custom type), which is only in the conceptual model but not in the database table. You can create a complex type by clicking on ‘Get Column Information’, which will get the schema of stored procedure. Then click on ‘Create New Complex Type’ and your complex type will be generated.

Entities: Stored Procedure will return collection of selected entity.

In our example, we are returning Courses data so check Entities and select Course table from the dropdown in the popup window as shown below:

The function in the context class for GetCoursesByStudentId is as shown below:

Now, GetCoursesByStudentId stored procedure can be called as shown in the code below.

The code shown above will execute the following statement in sql server database:

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

Execute a Stored proc using DbContext and SqlQuery

Alternatively, you can execute a stored procedure using SqlQuery method. SqlQuery method is useful to execute raw SQL query in the database. This is the easiest way of executing the Stored Procedure:

If the returned type is pre-defined, use the following (where “Course” is a pre-defined type),

Using DBSet.SqlQuery which returns entity of same type as DBSet(database table)

Execute a Stored Proc that returns multiple result sets in Entity Framework

Sometimes when using stored procedures you will need to return more than one result set. This method is commonly used to reduce the number of database round trips required to compose a single screen. Prior to EF5, Entity Framework would allow the stored procedure to be called, but it would only return the first result set to the calling code.

The examples below use a basic Blog and Posts model where a blog has many posts and a post belongs to a single blog. We will use a stored procedure in the database that returns all blogs and posts. Something like this:

We can execute code to issue a raw SQL command to execute our stored procedure. The advantage of this approach is that it works with both Code first and the EF Designer.

In order to get multiple result sets working, we need to drop to the ObjectContext API by using the IObjectContextAdapter interface.

Once we have an ObjectContext, we can then use the Translate method to translate the results of our stored procedure into entities that can be tracked and used in EF as normal. The following code sample demonstrates this in action.

The Translate method accepts the reader that we received when we executed the procedure, an EntitySet name, and a MergeOption. The EntitySet name will be the same as the DbSet property on your derived context. The MergeOption enum controls how results are handled if the same entity already exists in memory.

Here we iterate through the collection of blogs before we call NextResult. It is important to understand the above code because the first result set must be consumed before moving on to the next result set.

Once the two translate methods are called, the Blog and Post entities are tracked by EF the same way as any other entity – and so it can be modified or deleted and saved as normal.

Note: EF does not take any mapping into account when it creates entities using the Translate method. It will simply match column names in the result set with property names on your classes.

Note: If you have lazy loading enabled while accessing the posts property on one of the blog entities, EF will connect to the database to lazily load all posts, even though we have already loaded them all. This is because EF can’t know whether or not you have loaded all posts or if there are more in the database. If you’d like to avoid this, you’ll need to disable lazy loading.

 

 

Leave a Reply

Your email address will not be published.