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. 

Step 1: Add the Entity Data Model

Step 2: Generate the Model From The Database

Step 3: Choose the source tables

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

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

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.

Sql Profiler Trace Result

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.

Sql Profiler Trace Results

One of the additional queries run for the relationship

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.

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

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

Step 3: Create a function Import

Step 4: Update our code to call our function

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

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