Gil Fink, Microsoft MVP, is an expert in Web development and Microsoft data platform. He works as a senior architect at Sela Group. He is currently consulting for various enterprises and companies, where he architects and develops Web and RIA-based solutions. He conducts lectures and workshops for developers and enterprises who want to specialize in infrastructure and Web development. He is also a co-author of several Microsoft Official Courses and training kits. You can read his publications at his blog: http://blogs.microsoft.co.il/blogs/gilf. Gil is a DZone MVB and is not an employee of DZone and has posted 151 posts at DZone. You can read more from them at their website. View Full User Profile

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

03.16.2009
| 57115 views |
  • submit to reddit

In the post I’m going to explain how to map results of a stored procedure to a custom created entity which we have created with the Entity Framework designer.

Map Stored Procedures to Custom Entity

Sometimes we have stored procedures in our database which don’t map to any table or view of our database. The problem with that is that if we want to use those stored procedures and map them to a custom entity that we have created we will get an error while compiling the project. Entity Framework’s entities cannot be left without a mapping to a table or a view. The workaround for such situations is to use a dummy DefiningQuery element and to map the entity to that element. Hopefully that in the next release of Entity Framework we will get the ability to map stored procedures to custom entities instead of using the hack I’m going to show.

How to do the hack?

In the following example I’ll use the following stored procedure:

CREATE PROCEDURE dbo.GetCourseIDAndCredits
AS
BEGIN
    SET NOCOUNT ON 
    SELECT CourseID, Credits
    FROM Course
END

Pay attention that this stored procedure is simple and it’s only to show how to perform the mapping to a custom entity. The stored procedure returns the ID of a course with its credits.

Step 1
Import the stored procedure to the store model using Entity Framework Wizard.

Step 2
Create CourseCredits entity that matches the columns which the stored procedure returns. Make CourseID property as the entity primary key. The following figure shows the result of step 2:
CourseCredits Designer Diagram

Step 3
Use the designer’s Function Import feature and on Add Function Import dialog, set the return type of the stored procedure to our new CourseCredits entity (if you are not familiar with Function Import you can read my previous post on this subject). The following figure shows the dialog:
Add Function Import Dialog

Step 4
Create an entity type on the SSDL which will be the definition of the entity type that we are going to map to the CourseCredits entity. Open the model in Xml editor and define CourseCredits entity type like:

<EntityType Name="CourseCredits"> 
<Key>
<PropertyRef Name="CourseID" />
</Key>
<Property Name="CourseID" Type="int" Nullable="false"/>
<Property Name="Credits" Type="int"/>
</EntityType>

Step 5
Since Entity Framework restrict us to map every entity to a table or view, we need to use an hack and create a DefiningQuery on SSDL. Define the CourseCredits entity set as follow in the SSDL:

<EntitySet Name="CourseCreditsSet" EntityType="SchoolModel.Store.CourseCredits">
<DefiningQuery>
SELECT cast(0 as int) CourseID, cast(0 as int) Credits
WHERE 1 = 2
</DefiningQuery>
</EntitySet>

Pay attention that the DefiningQuery returns nothing. The where clause will never happen. This is for the sake of not enabling using the custom entity in other situations.

Step 6
Map the CourseCredits entity in the designer to the created dummy DefiningQuery in the Mapping Details View:
Mapping Details View 

Step 7
Test the solution. The following test code will print to the output the mapped objects:

using (SchoolEntities context = new SchoolEntities())
{
var courses = context.GetCourseIDAndCredits();
foreach (var course in courses)
{
Console.WriteLine("{0} {1}", course.CourseID, course.Credits);
}
Console.ReadLine();
}

And the result of running the code:
Console Output

Summary

Lets sum up, I showed how to map stored procedure to a custom reated entity. In Entity Framework V1 this means that we need to reate a dummy DefiningQuery and to map the new entity to it in order to enable that functionality. Hopefully that in V2 it will be resolved.

References
Published at DZone with permission of Gil Fink, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Sergio Reys replied on Wed, 2010/03/17 - 11:46am

Hi Gil, Nice article!

Just keep in mind that the SSDL is often regenerated when updating DB schemas.

So have to be cautious to avoid  lost your changes ; )

Good job!

Gil Fink replied on Tue, 2010/03/23 - 7:48am in response to: Sergio Reys

Thanks Sergio Reys. In EF4 the Update Model from Database was improved so it doesn't erase SSDL changes. I agree that in EF1 you should be cautious with that operation.

Ajay Pal replied on Sat, 2011/11/26 - 3:56am

Can you please tell how can I create controller for my store procedure in MVC , My SP contain select data statement from multiple tables . 1. I am creating an entity for my SP containg all the coloum my SP return. But its not working ,Some Mapping error is giving . Please help .........

Liezel Jandayan replied on Wed, 2013/01/23 - 6:43am

 This is not even similar to the "feeling" of Java. Not that I think that Scala is a bad idea, but Java must not try to copy Scala and Scala is much to complicated to ever become a mainstream language.-Ariella Kapelner

Stephanie Kaye Lopez replied on Wed, 2013/01/23 - 7:34am

 Stored procedures may return result sets, i.e. the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. -The Balancing Act Lifetime 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.