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
Index of Related Entity Framework Blog Posts and Screencasts
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.
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
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
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.
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.