Mapping Stored Procedures to ObjectContext Methods

Mapping Stored Procedures to ObjectContext Methods

In the session I had on
Sunday I showed a simple
scenario of how to map stored
procedures to ObjectContext
methods in Entity Framework. In this
post I’ll show the same example that I used in the session.

The Stored Procedure

In the example I’m going to use a simple stored procedure which
returns all the courses from the database ordered by their title.
The stored procedure code:

CREATE PROCEDURE dbo.GetCoursesOrderByTitle

AS

BEGIN

    SET NOCOUNT ON 

    SELECT CourseID, Title, Days, [Time], Location, Credits

    FROM Course

    ORDER BY Title ASC

END

How to Map a Stored Procedure to a ObjectContext Method?

Step 1

The first thing to do is to choose the stored procedure in the
Entity Framework Wizard. You can do that whenever you create the
model or when you use the Update Model from Database feature of the
designer. After that the stored procedure will appear in the Model Browser
like in the following figure:
 
Step 2

When we have the stored procedure mapped in the SSDL (step 1)
we can use the designer Add -> Function Import in order to map
it to our conceptual model. The following figure shows the designer
Add –> Function Import:

Step 3

Pressing the Function Import button will open the Function Import
form. In that form you will choose the stored procedure name, the
name to import to the ObjectContext (method name) and the return type:

As you can see you can map the method to return entities from the
conceptual model. One drawback is that the Scalars mapping isn’t working
in V1 of Entity Framework. Pressing OK will create the method on the
ObjectContext and you’ll be able to see it in the Model Browser like in the
next figure:

Using Mapped Stored Procedure

The following code shows how to use the mapped stored
procedure through the custom ObjectContext that I use:

using (SchoolEntities context = new SchoolEntities())

{

    var courses = context.GetCoursesOrderByTitle();

    foreach (var course in courses)

    {

        Console.WriteLine(course.Title);

    }

    Console.ReadLine();

}

and the output:
 

Pay attention!
When you are using a stored procedure from the ObjectContext it
return an ObjectResult<T> which can be read only once (behind
the scenes a DataReader is returned). If you would like to use it more
then once you can transform the result to a list for example.

Summary

Lets sum up, I showed how to map a simple stored procedure to a method
on the ObjectContext of Entity Framework. Entity Framework support even
more sophisticated stored procedures then the one I showed. You can also map
stored procedures to replace the default CUD operations of entities which isn’t
shown in the post.