Friday, March 13, 2009

Entity Framework Modeling: Select Stored Procedures

 

Entity Framework Modeling: Select Stored Procedures

In my last post, I illustrated how to map action stored procedures (insert, update, delete) to entities.  In this post, I will illustrate how you can import select stored procedures and map the results to entities. 

Watch the Screencast

You can watch the screencast here.

Index of Related Entity Framework Blog Posts and Screencasts

Here is an index of other blog posts and screencasts I have done on this subject.

Setting up the scenario

Before I import the stored procedure, I want to simply illustrate some EF functionality that doesn’t take advantage of select stored procedures.  We’ll add that functionality in later.  We are going to create a relatively simple Entity Data Model from the data model below. 

image

Step 1: Add the Entity Data Model

image

Step 2: Generate the Model From The Database

image

image

Step 3: Choose the source tables

image

Step 4: Name the Entities appropriately

Set appropriate Entity and Entity Set names for each of the entities.  Simply single-click each entity and set the values in the properties pane as seen below (I single-clicked the person entity):

image

The names I set were (Entity Name, Entity Set Name):

  • Person, People
  • PersonCategory, PersonCategories
  • StudentCourse, StudentCourses
  • Course, Courses

Step 5: Write some code to query

image

By default, the EF does not load relationships.  There is no way for the framework to know which relationships to load unless you tell it.  You will notice that I used lazy loading to load the relationships.  Alternatively, I could have used Include statements to do eager fetching.  If you are not familiar with these 2 options, let me do a brief overview. 

Eager Loading

You can direct the framework to eagerly load relationships by using an include statement (seen below).  With an eager load, the framework will construct a join query and the relationship data will be fetched along with the original entity data.

image

Sql Profiler Trace Result

image

Lazy Loading

With lazy loading, an additional query is constructed and run for each call to load to fetch the relationship information.  In the code below, an additional query will be run for each Person p.  In certain instances, eager loading is the way to go, while in others, lazy loading is a better approach.

image

Sql Profiler Trace Results

image

One of the additional queries run for the relationship

image

Query Results

Keeping in mind that we chose to go with the lazy loading approach (it will become why clear later), below are the results from our code.  Notice that only the people of type Student or BusinessStudent had classes.  Also notice that there was 1 Student and 2 BusinessStudents.  This will be important in our demo.

image

We now have the baseline code written and we can move on to mapping a select stored procedure.

Map in the Select Stored Procedure
Step 1: Write the Stored Procedure

image

Step 2: Update our model – Add the stored procedure to the model

image

image

Step 3: Create a function Import

image

image

Step 4: Update our code to call our function

image

The only change I made was to call our function.  Here are the results:

image

Our spGetBusinessStudents stored procedure was called when we started to iterate over the results.  At this point, it should be clear why I used lazy loading in the example.  Because stored procedures are not composable, we need to lazily load the relationships. 

Conclusion

The Entity Framework provides us the capability to import stored procedures as functions.  We can then map the results of the function back to our entity types.  We then have the ability to traverse relationships from those entities, as long as we use lazy loading.  In my opinion, this is great support for select stored procedures.

Published Friday, March 13, 2009 2:31 PM by rob.bagby

Demystifying The Code : Entity Framework Modeling: Select Stored Procedures

5 comments:

Tony said...

Thanks a lot! Just what I was looking for - clear and simple. The only thing I would like to reiterate - the return fields from the stored proc should match fields of the table that is behind the entity type you map your proc to.

Lentyai.

Vipin Kasarla said...
This comment has been removed by the author.
Vipin Kasarla said...

Do you know how to define the result entity, incase the stored proc returns the fields from two or more tables (via join)? like if SP is as follows...

select p.personId, p.FirstName, p.LastName, s.Grade
from Person p inner join StudentCources s on p.PersonID = s.PersonID

Jay said...

you rock man..thank you!

daspeac said...

I believe you have already heard about the fix dbf file eof

Blog Archive