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