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 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:

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:
 

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:
 

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:

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 DefiningQuery and to map the new entity to it in order
to enable that functionality. Hopefully that in V2 it will be resolved.