Mapping Stored Procedures to ObjectContext Methods

Mapping Stored Procedures to ObjectContext Methods

Mapping Stored Procedures to ObjectContext Methods

In the session I had on Mapping Stored Procedures to ObjectContext Methods
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:

Model Browser with SP on SSDL 
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:

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 Object Context (method name) and the return type:

Add Function Import From

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 Object Context and you’ll be able to see it in the Model Browser like in the

next figure:

Model Browser with SP on CSDL

Using Mapped Stored Procedure

The following code shows how to use the mapped stored

procedure
through the custom Object Context  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:

Output 

Pay attention!

When you are using a stored procedure from the Object Context it

return an  ObjectResult 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 Object Context 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.

source:http://blogs.microsoft.co.il/gilf/2009/03/06/mapping-stored-procedures-to-objectcontext-methods/