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

ASP .NET MVC 4 - Repository dependency injection with Ninject

Repository dependency injection with Ninject

Primary Goals

First question that comes in our minds is why bother with so many layers of code. Here are the major reasons I can think off :

Maintainability and extensibility through Separations of concerns

Repository pattern enables us to separate the implementation of accessing data from the data that repositories provide.
I really like this definition:

Repositories are the single point where we hand off and fetch objects. It is also the boundary where communication with the storage starts and ends.

from Patterns of Enterprise Application Architecture, page 332.

MVC Architectural Pattern - Model/View/Controller

Code quality through Testability

Mock Repository Pattern can be used for creating mock/fake repositories. Repository mocks are useful is various ways. They enable friendly Test Driven Development, allow testing only a part of the solution, DB disconnected mode in case we do not have a DB, working on Views and Controllers without data store, etc.

Independent Unit testing for Controllers and Repositories.

Productivity through Loose coupling

Being able to work interdependently in friendly TDD environment can boost the development speed
significantly. Loose coupling can be obtained with IoC and DI.  We can inject dependency manually or Dependency Injection Containers such as Ninject Dependency Injector.

There is a large list of dependency containers, other better known are Unity, StructureMap, Castle Windsor and Autofac.

List of .NET Dependency Injection Containers by Scott Hanselman

Solution structure

Solution is structured in five projects. One ASP .Net MVC4 project, one MS unit test project and three libraries. Repository contains all the "real" implementations to manipulate and expose data. MoqRepository contains fake methods together with fake/mock data. Model contains model classes. MVC project contains Controllers and Views.

ASP.Net MVC Project

• Controller - just routes the calls and has no business logic inside
• View - Generated with MVCScaffolding

Repository Project

• Repository classes - uses DB and exposes data. EF code first strategy has been used to generate data access layer.
• Repository interfaces - define the methods to be called from the Controller 

Mock Repository Project

• Mock repository classes - fake repository implementation. Exposes data hard-coded inside the class.

Model project

• Model classes - Model classes used to define DB table and view strong typed views.

Unit test project

• Test repositories - unit tests for "real" repositories
• Test controllers - unit tests for Controllers.

Ninject usage

I found the most easy way to get Ninject working is through Nuget. You can install it manually but it will take more steps to finish. There is no version for MVC 4 yet but the MVC 3 version works smoothly. 

Open solution

Open package management console

• Leave Package source to NuGet official package source.
• For Default project select ASP.Net project.
• Type in :

Install-Package Ninject.MVC3

• It will install all the dependencies and add needed assemblies. Result should look like this, ignore version part:

Successfully installed 'Ninject 3.0.1.10'.
Successfully installed 'WebActivator 1.5.2'.

Successfully installed 'Ninject.MVC3 3.0.0.6'.

Successfully added 'Ninject 3.0.1.10' to XXX.
Successfully added 'WebActivator 1.5.2' to XXX.
Successfully added 'Ninject.Web.Common 3.0.0.7' to XXX.
Successfully added 'Ninject.MVC3 3.0.0.6' to XXX.

In the App_Start project of the ASP.Net MVC project you will find a new file NinjectWebCommon.cs .  All you have to do in this class is to Register services which are going to be used in Controllers:


/// <summary>
/// Load your modules or register your services here!
/// Uncomment line 61 to use "real" implementation
/// </summary>
/// <param name="kernel">The kernel.</param>
private static void RegisterServices(IKernel kernel)
{
  kernel.Load(Assembly.GetExecutingAssembly());
  //kernel.Bind<IRepositoryMethods<Person>>().To<PersonRepository>();
  kernel.Bind<IRepositoryMethods<Person().To<MoqPersonRepository>();
}        

Loading Ninject bindings ....

and use it in the Controller, here is the example:

private readonly IRepositoryMethods<Person> _peopleRepository; 
public PeopleController(IRepositoryMethods<Person> peopleRepository)
{
    _peopleRepository = peopleRepository;
} 

GitHub Example:

ASP.Net MVC4 with Ninject

Must read:

IoC, DI, DI versions
Martin Fowler Articles - Injection

Repository pattern
Patrik LÖWENDAHL Blog
Martin Fowler on Repository pattern

Repository mocking
Mock a DB repository using Moq

ASP.Net MVC
Official ASP.Net MVC pages
Scott Hanselman Blog

Ninject
GitHub open source project
Ninject WIKI ToC
Ninject.MVC3 Documentation
Extensions