Using stored procedures with strong return types in Entity Framework 5.0.0

This article explains how to import stored procedures into Visual Studio Mvc4 application and use them with strongly typed parameters and return values. ADO.NET Entity Data Model will be used to represent the database.

Mvc4 uses Entity Framework to map the database to the entity model. The database mapping is done automatically by adding a new ADO.NET component to the project. The following picture shows scaffolded files holding POCO classes and the context. "TestModel.Context.cs" holds the database context derived from DbContext class. "TestModel.tt" holds the model files containing the POCO classes.

Image 1: Entity data model used for testing

Stored procedure does not have to be present when the ADO.NET model is added to the project. It can be created and imported later in the development process. In that case the database model has to be refreshed before the stored procedure becomes available for import.

To refresh the database, go to View -> Other Windows -> Entity Data Model Browser, find the desired model in the Model Browser, right-click and select "Update Model from Database". There you can edit the parts of the database the model encompasses. This can be used to add the newly created stored procedures to the model.

Image 2: Refreshing the model to include newly made stored procedure

After this is done, the time has come to import the stored procedure. Right click on the model diagram and select Add New -> Function Import button. The new "Add Function Import" window appears. Add a desired function name, select the stored procedure you want to add and the return type. The new stored procedure can be set to return any scalar or complex type, including entity data (for instance, the filtered subset of entities). If the return value is set incorrectly, the data retrieval will raise the invalid cast exception.

Image 4: Stored procedure import window

Now the stored procedure can then be called using the database context object and the result can be queried by using Linq or some other querying method.

Entities _context = new Entities();
var data = _context.MyStoredProcedure("Author name");

Scaffolded stored procedure call:

public virtual ObjectResult<Author> MyStoredProcedure(string authorName)
{
    var authorNameParameter = authorName != null ?
        new ObjectParameter("AuthorName", authorName) :
        new ObjectParameter("AuthorName", typeof(string));

    return ((IObjectContextAdapter)this)
        .ObjectContext
        .ExecuteFunction<Author>("MyStoredProcedure", authorNameParameter);
}

Sources and further reading:

1. Reflecting changes made to stored procedure in Entity Framework-generated complex type
2. ADO.Net Entity Framework: How to Retrieve data using Stored procedure step by step