Over a million developers have joined DZone.

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

DZone's Guide to

Mapping Stored Procedure Results to a Custom Entity in Entity Framework

· Big Data Zone
Free Resource

Effortlessly power IoT, predictive analytics, and machine learning applications with an elastic, resilient data infrastructure. Learn how with Mesosphere DC/OS.

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
    SELECT CourseID, Credits
    FROM Course

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


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.

Learn to design and build better data-rich applications with this free eBook from O’Reilly. Brought to you by Mesosphere DC/OS.

dotnet ,visual studio ,stored procedures ,entity framework

Published at DZone with permission of Gil Fink, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.


Dev Resources & Solutions Straight to Your Inbox

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.


{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}