Demystifying The Code

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.

Entity Framework Training

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.

Comments

10 Responses to “Entity Framework Modeling: Select Stored Procedures”
  1. Joseph Baggett says:

    I have a question I have not tried yet with the Entity Framework… how can you map multiple result sets from a stored procedure to different entities, subobjects/subcollections?

  2. R.Zarei says:

    hi
    i am were using objectdatasource with dataset.
    i could choose select,insert,update and delete command for objectdatasource from dataset’s tableadapter.
    now i move to entity model, i created function for my select stored procedures and choosed, this function for objectdatasource select command but when i run my web application , i see the following exception:

    The ObjectContext instance has been disposed and can no longer be used for operations that require a connection.

    and ideas?

  3. Stuart says:

    Nice article and I think Microsoft need to go further.
    For example, if I wish to return data for composite objects in my stored procedure then I should be able to tell the framework to load the data from these into objects.
    Lazy load is not an option in high performance scalable applications.
    LINQ is not an option in enterprise – data access is usually (for security, performance and maintenance reasons) mandated to be stored procedures.
    Usually I would be the one doing the mandating!

  4. Lochner says:

    Thanks! Help it a lot!

  5. sam says:

    I am not sure why you are calling lazy loading for stored procedures great support. To me, this is very limiting and Stored Procs are almost useless.

  6. RobBagby says:

    @sam. I disagree with you regarding stored procedures. They offer the ability to assign granular security, among other things.

  7. EF4.1 says:

    how can i make select function parametrized?
    for example passing where clause value.

    var people = from p in school.GetBusinessStudents(”name = xyz”)
    select p;

    and change select stored procedure accordingly.

  8. admin says:

    @Stuart,

    I agree that MS needs to keep going further. I further agree that there are a multitude of scenarios where you want to use eager loading. If, as you seem to be, in a situation where, for security purposes, you use Stored Procedures, right now, you are limited to lazy loading for those associations. It would be nice to be able to write the associations in the Stored Procedures and map the results to your graph.
    I tend to disagree with your assessment that LINQ is not an option in the enterprise. I have and continue to consult to several of the largest enterprises in the world and LINQ is used heavily. There are certain systems where access is heavily restricted, but, normally in our application domain, we have an ORM (EF Or NHibernate) create our queries.

    Rob

  9. admin says:

    At this time, you cannot. You have to use lazy loading for those associations. If you are not using select stored procedures and are allowing EF to generate your queries for you, you use an Include statement:
    .Include(”Addresses”)

    Rob

  10. admin says:

    It is possible that in your code, you are deferring execution of your query by returning an IQueryable. Try calling ToList().

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!

Demystifying The Code