Mapping Stored Procedure Results to a Custom Entity in Entity Framework

March 13, 2009

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


DotNetKicks Image

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

9 comments

  1. valMarch 16, 2009 ב 9:10

    What about SSDL rewriting when updating the model from database? Seems that this problem can’t be solved easily in EF v1.

    Reply
  2. Gil FinkMarch 16, 2009 ב 11:02

    @val,
    Indeed the Update Model From Database erases all the changes that you made manually and this is a painful thing in V1 of EF. I can only say that this is something that ADO.NET team is working on and probably will be corrected in V2 of EF.

    Reply
  3. AlonMarch 17, 2009 ב 14:40

    Toda Raba!

    Great place to get me started. Now … if I could just get it to work (and usable through ADO.net data services 😉

    Any how … it seems that the SQL generated (using query analyzer) includes the fake SQL where 1 = 2 clause. So it does not return anything. Obviously … I modified the edmx file’s XML incorrectly. I just can’t see what I blotched !?!? Focusing on the function import now? Or maybe the issue is that the SP has a parameter?

    Kol Tuv,
    Alon (Katamon/Jerusalem)

    Reply
  4. Gil FinkMarch 17, 2009 ב 15:27

    Hi Alon,
    Thanks for the comment. If you have a parameter within your SP you need to map it to the relevant property in the entity when you use the Add Function Import feature. Making changes to the Xml, as I wrote in the past, should be done very carefully. If you modified the edmx file’s XML incorrectly I suggest to check the changes you made first. Probably the error is there. About the solution in general, as I wrote, this is an hack to enable the mapping of SPs to custom entities because of that I used the fake DefiningQuery and the where 1 = 2 clause.

    Reply
  5. AlonMarch 17, 2009 ב 16:16

    Thanks! I actually coded the edmx file correctly but was accessing the service just plain wrong. Had to follow this article and voila …
    http://blogs.msdn.com/marcelolr/archive/2008/01/21/service-operations-in-ado-net-data-services.aspx

    Reply
  6. JZ.April 27, 2009 ב 7:04

    This is exactly what I am looking for. Thanks!
    But every I update model from database the code above in the edmx file got over written. Is there a way to avoid this?

    Thanks

    Reply
  7. Gil FinkApril 27, 2009 ב 7:43

    Thanks JZ,
    In V1 of EF the Update Model from Database will erase all the changes you are making manualy. There is no work around. You can save your changes in another file andafter the Update feature put the changes back to the model. In V2 (.Net 4.0) they solved this tweak from what I saw.

    Reply
  8. Sanjay PujariJune 4, 2009 ב 13:24

    How I can call this stored Procedure in Ajax using Datacontext

    Reply
  9. Gil FinkJune 4, 2009 ב 13:54

    @Sanjay Pujari,
    You can create a service operation that will call the created method. You can look at an example of service operation here: http://blogs.microsoft.co.il/blogs/gilf/archive/2008/11/14/service-operations-adding-business-logic-to-a-data-service.aspx

    Reply