How to Map a Stored Procedure to an Entity in Entity Framework 4

The first thing you have to decide when mapping a stored procedure through Entity Framework (EF) is what type of object you're really after. You might think you want an Entity and design it all out on the EF design surface, but that may not be what you really need. The thing you need to take into consideration is how you are going to use the entity object. If all you're going to do is use this object to hold the data returned from the stored procedure then you don't actually want an entity; you want a complex type.

Entity objects  in EF are more than just objects. They contain state information that enables them to peform self-tracking. This is how EF tracks changes to objects in order to persist those changes when you call SaveChanges() on the data context. Because of this, entities require that they be mapped to an underlying data store or view. EF will not allow you to create an entity without an entity mapping. If you try to do this you will get the following error:

"Error 3027: No mapping specified for the following EntitySet/AssociationSet"

The way around this error is to use a complex type instead of an entity. Setting up a complex type is very simple. All you have to do is open up the EF designer, right-click empty design space, and select Add > Complex Type. This will add a new complex type object to the model browser. Right-click the new complex type and go to Add > Scalar Property. Add a scalar property for every property you want on your object (usually one property for every column returned by the stored procedure). If you created an entity already, delete it from the designer and rename your new complex type to the same name as the entity you just deleted.

EDIT: Turns out there is an easier way to create complex types for function imports. Read on and I will explain how to create the complex type on step five of creating the function import.

Note that if your entity is actually mapped to an underlying data store then you are free to let the stored procedure populate a collection of your entity. It's only when you want an entity that is not mapped to a data store that you need to use a complex type instead.

Now you are free to map your stored procedure. To do this you will need to create a function import. First ensure the stored procedure you want mapped is added to Entity Framework; if it is not then update your model from the database, selecting the desired stored procedure in the Add window. Now create the function import:

1. Right-click empty space in the EF designer and go to Add > Function Import.
2. A function import will create a method on your EF data context. The name of your function import will be the method name on the data context so name it appropriately.
3. Choose the desired stored procedure from the "Stored Procedure Name" dropdown list.
4. Next, choose what the function will return. If you have a mapped entity you would like to use then select entities and choose the desired entity. If you created a complex type, then choose "Complex".
5. Now create the complex type by clicking the "Get Column Information" button. This will load the box with all the fields returned by the stored procedure. Now simply click "Create New Complex Type". Change the name of the complex type next to the "Complex" radio button to the name you want.
6. Finally just click OK and you're done!