Mapping Stored Procedure Results to a Custom Entity in Entity Framework

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

 

In the post I’m goingMapping Stored Procedure Results to a Custom Entity in Entity Framework
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 Defining Query 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 Defining Query 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
  Defining Query 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
created entity. In Entity Framework V1 this means that we need to
create a dummy Defining Query and to map the new entity to it in order
to enable that functionality. Hopefully that in V2 it will be resolved.

source: http://blogs.microsoft.co.il/gilf/2009/03/13/mapping-stored-procedure-results-to-a-custom-entity-in-entity-framework/